Pinterest Stumbleupon Whatsapp

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 How to Integrate Excel Data Into a Word Document How to Integrate Excel Data Into a Word Document During your work week, there are probably lots of times that you find yourself copying and pasting information from Excel into Word, or the other way around. This is how people often produce written reports... Read More or sending out emails straight from Excel How to Send Emails From an Excel Spreadsheet Using VBA Scripts How to Send Emails From an Excel Spreadsheet Using VBA Scripts We'll show you how to set up automated emails from within Excel using Collaboration Data Objects, (CDO) and VBA scripts. Our code templates make this a lot easier than it sounds! Read More . 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 Top 3 Websites To Download Useful Free Excel Programs Top 3 Websites To Download Useful Free Excel Programs Read More .

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.


Monitoring Your Websites

I’ve tried a lot of different tools to ping the different websites that I manage, like the ICMP Ping Manager Monitor Network Devices And Websites With ICMP Ping Manager Monitor Network Devices And Websites With ICMP Ping Manager Both at work and at home, I often find that I have a need to check the status of my PCs, printers, servers or other network devices. These days, with so many devices making use... Read More .  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

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"
            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

    Sheet1.Cells(intRow, 2) = sPing

    intRow = intRow + 1


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

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


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 Make a Personal Budget on Excel in 4 Easy Steps Make a Personal Budget on Excel in 4 Easy Steps Do you have so much debt that it will take decades to pay off? It's time to make a budget and apply a few Excel tricks to help you pay off your debt sooner. Read More .

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 *

  1. Bethany Swaney
    March 14, 2017 at 2:04 am

    I came to this site looking for answers to my computer class questions. I actually learned a lot, and even think i found a few things that i can actually use in my day to day life or even in the future. Im a Business Administration major in college right now. I didn't realize before how much excel could actually help me in business alone.

  2. Michael
    March 6, 2015 at 7:30 am

    Great article.. definately goin to use this!! :)

  3. HLJonnalagadda
    September 23, 2012 at 9:20 am

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

  4. GrrGrrr
    September 20, 2012 at 7:51 pm


  5. AriesWarlock
    September 20, 2012 at 3:54 pm

    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
      September 20, 2012 at 10:45 pm

      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().

  6. GayashanNA
    September 20, 2012 at 7:48 am

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

  7. Ahmed Khalil
    September 20, 2012 at 6:10 am

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

  8. Philip Treacy
    September 20, 2012 at 4:19 am

    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