How To Use Microsoft Excel To Manage Your Life

Ads by Google

spreadsheet   How To Use Microsoft Excel To Manage Your LifeIt’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.

Ads by Google

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.

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.

automation1a   How To Use Microsoft Excel To Manage Your Life

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.

automation2   How To Use Microsoft Excel To Manage Your Life

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”.

automation3   How To Use Microsoft Excel To Manage Your Life

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

automation4   How To Use Microsoft Excel To Manage Your Life

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.

automation5   How To Use Microsoft Excel To Manage Your Life

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.

automation6   How To Use Microsoft Excel To Manage Your Life

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”

automation7   How To Use Microsoft Excel To Manage Your Life

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.

automation8   How To Use Microsoft Excel To Manage Your Life

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

Ads by Google

7 Comments - Write a Comment

Reply

Philip Treacy

LOL using Excel to ping sites. I could use something like this to monitor client backups and create a report in Excel for them, then publish that to the web

Reply

Ahmed Khalil

Very very very good thanks alot, but can i ask if these features can be gained in Numbers09 of iworks on MAC

Reply

GayashanNA

This must be the most useful article about Excel! Awesome! and Thanks a lot!

Reply

AriesWarlock

Maybe someone can tell me why I can’t round a number in a cell… in that same cell? For some reason the rounded number has to appear in a different cell, but that’s not useful!

elhaj

you can just use the ROUND() function. you can also use cell formating to “SHOW” the number as rounded.
if you use cell formating to round numbers then used them (numbers) in any kind of operation like SUM() Excel will use the real long version of the numbers not the short rounded version, which may lead to math errors, to be on the safe side use ROUND().

Reply

GrrGrrr

wow..interesting.

Reply

HLJonnalagadda

Incredible article! Did not know any of these features. Thanks a lot for the advise!

Your comment