How To Use Microsoft Excel To Manage Your Life

uses for excelIt’s no secret that I’m a total Excel fanboy. Much of that comes from the fact that I enjoy writing VBA code, and Excel combined with VBA scripts open up a whole world of possibilities.

In the past, here at MUO, I’ve shared a few of the things I’ve done with Excel and VBA, like exporting Excel data into Word or sending out emails straight from Excel. Of course if you aren’t much of a coder, you can always get Excel applications someone else has written, like those listed by Simon.

However, if you’re serious about using Excel to manage more of your life, then you should take the time to learn how VBA coding works. To help with that, I’ve decided to share a “Automation” spreadsheet that I’ve created to manage different areas of my life. This spreadsheet has 4 tabs and covers everything from grouping links of URLs that I want to launch all at once, to managing my debt and paying it off faster.

If you want to enhance your use of Excel, I invite you to follow along as I share a few of these designs – and simple scripts – that I used to accomplish these tasks.

Managing Your Life With Excel

Microsoft Excel is not just a data spreadsheet. It is actually a design platform for applications. If you look at it that way, you may realize just how much you can accomplish with the application.

Look at a sheet as a design board where you can place Visual Basic form objects like command buttons, dropdown boxes, textboxes and anything else at all. Not only can you place them anywhere on the sheet, but you can use those objects to interactively (or automatically) add, remove, or manipulate information on the sheet.

Ads by Google

Monitoring Your Websites

I’ve tried a lot of different tools to ping the different websites that I manage, like the ICMP Ping Manager.  But, not long ago I discovered a way to ping websites from right inside of an Excel VBA script. That meant, I could add a sheet to my “Automation” workbook that would ping all of the websites that I manage, and put the results into a cell next to the website name.

This is how I laid out the sheet.

uses for excel

The number “4” in cell B1 is used to display the count of websites that I’ve installed on the sheet. This will allow the script to only count through the number of cells that actually have sites listed, started at A3.

The code to accomplish this looks like this:

Dim intSiteCount As Integer  Dim intCount As Integer  Dim oPing As Object, oRetStatus As Object  Dim sHost As String  Dim sPing As String  Dim intCol As Integer  Dim intRow As Integer    intSiteCount = CInt(Sheet1.Cells(1, 2).Value)    intRow = 3    For intCount = 1 To intSiteCount      sPing = ""      Sheet1.Cells(intRow, 2) = sPing      intRow = intRow + 1  Next    intRow = 3    For intCount = 1 To intSiteCount        sHost = Sheet1.Cells(intRow, 1)        Set oPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery _        ("select * from Win32_PingStatus where address = '" & sHost & "'")        For Each oRetStatus In oPing          If IsNull(oRetStatus.StatusCode) Or oRetStatus.StatusCode <> 0 Then              sPing = "Ping Failed"          Else              sPing = sHost & " Ping Success on " & Now() & Chr(10)              sPing = sPing & "Time (ms) = " & vbTab & oRetStatus.ResponseTime & Chr(10)              sPing = sPing & "TTL (s) = " & vbTab & vbTab & oRetStatus.ResponseTimeToLive          End If      Next        Sheet1.Cells(intRow, 2) = sPing        intRow = intRow + 1    Next

The first “For” look at the top just goes through the results cells and clears the results from the last time I ran a check. The second FOR loop counts through the rows listing the websites, starting at the third row (intRow-3), performs the Ping command (the Set oPing line), and then returns the results into column B (Sheet1.Cells(intRow,2) = sPing).

Here’s how those results look after the script runs.

how to use excel for managing money

The results column shows whether the ping was successful, and the Time/TTL details.

If you aren’t familiar with adding command buttons to an Excel sheet, you can add the button from the “Developer” menu, and clicking on the “Insert” button and choosing the button from the list. Draw the button onto the sheet, right click on it, and then select “Assign Macro”.

how to use excel for managing money

Type the name of the Macro for that button, and click on “New”.

how to use excel for managing money

This will open up the code screen where you can insert the code from above.

Maintaining a Library of Link Groups

On another tab, I also started to organize the group of links that I use to perform certain tasks. For example, when I write for MUO, I like to open up the MUO WordPress editor, google, and our Google Docs page for topics. When I research for Top Secret Writers topics, I like to open a few standard media pages.

use excel for project management

When I click the “Launch Group” button, it will launch the default web browser and open up all pages in that group. Here’s how the script for each button looks:

Dim intSiteCount As Integer  Dim intCount As Integer  Dim intCol As Integer  Dim intRow As Integer    intSiteCount = CInt(Sheet2.Cells(4, 3).Value)  intRow = 5    For intCount = 1 To intSiteCount      ActiveWorkbook.FollowHyperlink (Sheet2.Cells(intRow, 2))      intRow = intRow + 1  Next

This script is simple but effective. The secret to this one is the “FollowHyperlink” function. This code will check the number of links defined in the cell just to the right of the group title, and knows to run through that many links before the title. For each button, the location of the link count, and the column being used needs to be manually typed into the code, but the rest of the code is identical for each button.

Previewing your Picture Gallery

On the next tab of my automation worksheet is where I go when I want to quickly run through all of the images in my pictures folder. I do this because I place more than just images in that folder, and want to see all of the files that I have there.

Here’s what it looks like after clicking the “Preview Pics” button.

use excel for project management

Right now I manually update this sheet by deleting all pictures from the B column, and then clicking on the “Preview Pics” button.

The button runs the following script:

Dim myPict As StdPicture  Dim strFilePath As String  Dim intRow As Integer  Dim myPictName As Variant  Dim myCell As Range  Dim sPicture As String  Dim strTest As String  Dim myRng As Range  Dim intSkip As Integer    intRow = 2    strFilePath = Sheet3.Cells(1, 3).Value    Set myObject = New Scripting.FileSystemObject  Set mySource = myObject.GetFolder(strFilePath)    On Error Resume Next    With Sheet3      Set myRng = Sheet3.Range("B2", .Cells(.Rows.Count, "B").End(xlUp))  End With    For Each myfile In mySource.Files    'If picture is a file      If Right(myfile, 4) = ".gif" Or Right(myfile, 4) = ".jpg" Or Right(myfile, 4) = ".bmp" Or Right(myfile, 4) = ".tif" Or Right(myfile, 4) = ".png" Then          Sheet3.Cells(intRow, 1).Value = ""          Sheet3.Cells(intRow, 1).Value = myfile.Name            intSkip = 0            For Each myCell In myRng.Cells              If intSkip = 1 Then                  With myCell.Offset((intRow - 3) + 1, 0)                      Sheet3.Shapes.AddPicture myfile.Path, msoCTrue, msoCTrue, .Left, .Top, 125, 125                  End With              End If              intSkip = intSkip + 1          Next myCell        End If      intRow = intRow + 1    Next

The secret of this script is using the StdPicture object, which lets you sort of overlay pictures at the location of certain cells, by defining the left and top properties of the picture to match that of the cell. Just make sure to size the cells slightly larger than what you define in the code. In my situation I used 125 height and width for the pictures, so my cells are set slightly larger than that ahead of time.

Managing Your Debt

The final tab that I want to share is actually one that I wrote a while back about using Excel to create a personal budget.

The most important concept that I wrote about in that article, and one that belongs in any article about using Excel to manage your life, is using Excel to calculate how the “snowball effect” can help you pay down your debt.

The concept is pretty simple. List all of your credit card debt side by side in a sheet, with two columns per debt – total balance and payment. The calculation for each subsequent payment cell is “PrevBalance + (PrevBalance * 0.10/12) – last payment”

use excel for project management

Then you can drag all of the values down the sheet and they’ll recalculate, showing how quickly your balance will drop as you make those payments. As you can see, making one payment per debt until each debt is paid will eventually pay off each individual debt.

But thanks to the quick calculating power of Excel, you can determine when balances will be paid off, and at that point take the minimum balance for that card and move it over to another card not yet paid off. As the spreadsheet shows, each subsequent balance gets paid off much faster.

uses for excel

Excel allows you to calculate and visualize quickly how your payments will affect future payoff dates, and it also gives you a schedule to look back at while you’re trying to make sure that you’re right on track in paying off those debts.

As you can see, Excel is a very powerful tool when it comes to managing all aspects of your life – whether it’s your work, managing files, or your budget.

Do you have any unique uses for Excel to manage your own life? Share some of your own tips and advice in the comments section below.

Image Credit: magnifying glass via Shutterstock

Leave a Reply

Your email address will not be published. Required fields are marked *