TIP # 1: Keep Your Dashboard Header Visible Using Freeze Panes

Step # 1: Selecting the Row Below Where You Want to Freeze

The first step in freezing your header to make it visible is selecting the row just below the area you would like to freeze. Select the row number on the left of the worksheet and this will highlight your entire row.

Step # 2: Accessing Freeze Panes from the Quick Access Ribbon

Once your row is highlighted, you can head over to the view tab on the quick access ribbon and select freeze panes. This will bring up 3 different options but you’ll want to select freeze panes again.

Step # 3: Using the Scroll Bar to Watch the Magic – Freeze Panes!

Use the scroll bar in your worksheet to move up and down. Notice that anything below the rows that you have frozen will move but your dashboard/tracker header will remain visible. Freeze panes has to be one of my favorite options for this reason.

TIP # 2: Create Buttons in Your Header to Navigate Between Worksheets

Step # 1: Create the Shape

The first step in creating buttons in Excel is to create, select and add a shape to your worksheet.  To add a shape to your worksheet, in the Quick Access Ribbon go to the insert tab and then select shapes under the illustrations tab. This will bring up a drop down menu with multiple shapes.  From here you’ll select the shape you’d like to use as your Excel button.  This button will be used to navigate between sheets.

Step # 2: Edit the Text

Now that the shape is created, you’ll want to select the shape by right clicking and then you’ll want to select edit text. This will of course allow you to change the text.  Change the text to display the tab name that you’d like to link to in your workbook.

Step # 3: Create the Hyperlink to Link the Button to Another Tab

Now that you’ve changed the button text, you can create a hyperlink with the button so that when the user clicks on it they are brought to another worksheet (such as “Expense Entries”).  To do this, if your shape is still selected type CTRL + K or go to the insert tab and select hyperlink.  Once you are in the hyperlink window, go to place in this document.  Under the cell reference section, select the tab/worksheet within your workbook that you want to link to. NOTE: You can also link to name references, which means if you wanted to you could link to a specific cell anywhere within your workbook.

TIP # 3: Use a Nested SUM and IF Formula to Summarize Your Detailed Expenses

Step # 1: Select the Cell On Your Budget & Expense Tracker Where Total Spend Will Be Calculated

The first step is definitely an easy one.  The only thing you need to do in this step is select the top cell on your budget and Expense tracker and get prepared to create the nested SUM & IF formula.  For the purpose of teaching you this exercise, I will suggest that the expense entries and data that is being referred to is put into an Excel Table so that you can use structured references in the array formula that you will be creating.

Step # 2: Creating and Writing the Nested Array SUM & IF Formula

Now that you’ve selected the cell and setup your expense entries table, you can create your nested array SUM & IF Formula (click here for more on array formulas).  The SUM & IF Formula has one simple job, it is to return the sum of all expense entries that match the associated expense type from column B.  This means that if we are looking at cell D5 as shown below, we want to SUM the value of all expense entries when/(IF) our expense type on our expense entries table equals the expense type in D5.

As I have mentioned in step #1,  you can use structured references to build an array formula that will handle this situation. Here’s what our formula will look like:

The Formula Explained:

• SUM is used to find to calculate the total sum of all expense entries

 =SUM(

• IF will be used to SUM only the values in our value of purchase column (on the expense entries table) where the associated expense type in our Budget & Expense Tracker equals the same expense type (Select Purchase Type Column) on our detailed expense entries table/sheet. So if B5 equals the purchase type on our expense entries table, our true statement in the IF formula will add all of our entries when true or will add 0 for the lines that are false.

• Once you’ve entered your formula and closed the last bracket, type CTRL + SHIFT + ENTER.  This will make your formula an array.  The reason why you want to do this is because you want the formula to not only find the first record where the expense type equals the select purchase type on your expense entry table but you want to sum an array of numbers (multiple entries) where this statement/formula is true.

{=SUM(IF(B5=ExpenseEntries[Select Purchase Type),ExpenseEntries[Value of Purchase ($)],0))}

Step # 3: Watch Your Budget & Expense Tracker Update as you Add Expense Entries

Now that you’ve created the formula, you can copy that formula down across all expense types on your budget and expense tracker.  After that’s completed, you can head over to your expense entries table and add records.  You’ll notice that your budget and expense tracker automatically updates to show total spend to date as you add new detailed expense records to your table.