Displaying a specific tab when opening a worksheet

I am a big fan of including an instructions worksheet tab on my dashboards and complex reports. However, most people that read my reports completely ignore this tab.  One day I was thinking about this problem and decided to find a solution to have a workbook open to the instructions tab.  Now by default, Excel will display the worksheet displayed when the workbook was last saved. So when I save my workbook, I always have the Instructions worksheet selected.


However, some people (yes Tom and Nancy I am referring to you) will open my workbook then save it when it’s on a different worksheet, thus negating my effort to have the workbook open to the instructions worksheet tab.


Well I decided to take a different approach when it was necessary for the workbook to open to the instructions tab. I can use the following 3 lines of code in a macro to force the workbook to open to my instructions tab.


Private Sub Workbook_Open()
Worksheets("Instructions").Activate
End Sub


This 3 line macro will always open my workbook to the Instructions tab. Now of course you can modify the worksheet name to be any worksheet tab in a workbook. Just be sure to exactly type the name of the worksheet you want to open.


Okay so how do we add this code??? Follow these steps:


Open the workbook where you want to add this macro.


Press Alt+F11 to display the Visual Basic Editor.


In the Project Explorer window you will see a list of the open workbooks and templates. If you do not see the Project Explorer window, Press CTRL+R or select Project Explorer from the View menu.

Locate your workbook in the Project Explorer. It will be named something like VBAProject (Book1) (if the worksheet has not yet been saved) or the name of your workbook.


You want to expand the workbook to view the individual tabs. You can do that by clicking on the plus sign to the left of the current workbook in the Project Explorer. In the above example I have three worksheet tabs, Instructions, Data and Results. If you only see a list of folders, click on their plus signs to see the worksheet tabs.


Double click on the This Workbook object located at the bottom of the list of worksheets to open the code window.


In the code window, type or paste the 3 lines of blue code.


Private Sub Workbook_Open()
Worksheets("Instructions").Activate
End Sub


Next close the Visual Basic Editor window by clicking on the X at the top right of the window.


Save your workbook.


Now, whenever you open your workbook, the specified worksheet will always be displayed first (regardless of which worksheet was displayed when the workbook was saved)

View Calculation Formulas in Excel

When I inherit a spreadsheet from someone I always want to take a look at it for accuracy. Reviewing the spreadsheet also allows me to understand the creator’s logic. And let’s face it, few people if any document all the formulas on a separate tab in the worksheet.

So I often want to look at the formulas in the worksheet. Now there are a few ways to view calculation formulas in worksheets (ranging from simple to more complex).

The most basic way is to examine a single calculation by selecting the cell with the formula and viewing the calculation in the formula bar.

In this example I have placed my cursor in cell D2 and the calculation formula shows as =B2*C2

There are also times where you may want to display all the calculations in your worksheet. There are three ways to do this.

The most difficult is to click on the windows button at the top left of Excel then choose Excel Options. This will bring up the Excel Options dialog box. Choose Advanced and scroll down until you get to the Display options for this worksheet.  Check Show formulas in cells instead of their calculated results. When done, click on the OK button.

Excel will now display all your calculation formulas.

To redisplay the calculation, go back in and uncheck this box.

Now this way works but is not fast. You can more easily accomplish the same thing by selecting Formulas from the Ribbon.

Ribbon > Formulas > Show Formulas

Finally the fastest way to display your calculation formulas is to use a keyboard shortcut.

Press and hold the CTRL key then press the ` key (CTRL + `) to show calculation formulas in Excel (` is found on the keyboard just below the ESC key).

So now you know how to view formulas. What you do with that knowledge is a topic for another post.

HLookup - The overlooked cousin

Today I want to look at =Vlookup’s often overlooked cousin =HLookup (Vertical lookup to Horizontal Lookup).

They both accomplish a similar task. =Vlookup allows you to search for a value in a column and return a value in a column to the right of where your search value is stored.

=Hlookup allows you to search for a value in a top row of a table and return a value in a row below of where your search value is stored. 

You would use =Hlookup when your value is located in a row across the top of a table of data, and you want to look down a specified number of rows. You would use =VLOOKUP when your value is located in a column to the left of the data you want to find.

Confused? Don’t be.

Both have a similar formula structure.

Hlookup(Lookup Value, Table array, Row index num, Range Lookup)

Vlookup(Lookup Value, Table array, Col index num, Range Lookup)

Lookup Value - The value you want to find. If its text place a " around the text.

Table Array - The columns and rows where you want to search for your value and return value.

Col  / Row Index Num - The column or row of the value you want to return.

Range lookup - True or False, not required but specifies whether you want your lookup to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

Here is some sample data.

	A	B	C	D	E	F	G	H
1	AA	BB	CC	DD	EE	FF	GG	HH
2	1	2	3	4	5	6	7	8
3	9	10	11	12	13	14	15	16

Here I have columns A through H with data in rows 2 and 3. My header row has duplicated letters (AA, BB, CC. etc.)

Let’s use =HLookup to bring back the value of 5 in column E.

=HLOOKUP("EE",1:2,2,FALSE)

So what does this formula do???

It asks Excel to find the value EE in the designated row array (rows 1 & 2), then return the value in row 2.

If I wanted to return the value in row 3 (13) in column E, my formula would be…

=HLOOKUP("EE",1:3,3,FALSE)

Now it is important to point out that the Array 1:3 indicates the rows I select for my formula. If my header row starts in row 5 and my data is in rows 6-7, then my formula would change.

=HLOOKUP("EE",1:2,3,FALSE) to =HLOOKUP("EE",5:7,3,FALSE)

Of course I can reference a cell instead of a hard coded value such as “EE”. In this example I want to look for the value in cell G18 and return the value un row 3 of my array.

=HLOOKUP(G18,1:3,3,FALSE) to =HLOOKUP("EE",5:7,3,FALSE)

Wildcards

If range_lookup is FALSE and lookup_value is text, you can use the wildcard characters - (?) and asterisk (*).

A question mark matches any single character (as shown in cell F8 in my example).

 If you have 6 question marks, then you are trying to return a field with 6 characters in length.

An asterisk matches any sequence of characters.

 If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character

Now I almost never use =HLookup in my daily Excel use, but do rely heavily on =VLookup. How do you use =HLookup in your daily work?

KPI Sales Dashboard with Fuel Gauge Part 3 of 3

Continuing with the last two posts on creating this sales dashboard…. Wait you didn’t the last two posts? Well get caught up by checking them out.

I want to focus today on creating the gauge chart. I add a tab labeled Gauge Chart and add the following information.

	A	B	C
1	Column B Formulas	Needle	Value
2		180	83.59
3	=((B2/100)*C2)-1	149.4575
4		2
5	=360-SUM(B2:B4)	28.54245
6	=SUM(B2:B6)	360

Now in cell C2 I have a hard coded value so that I can setup my gauge chart. I will eventually point this cell to the calculations tab for Current Year Total Sales Compared to Last Year Total Sales. But for now I want it hard coded so that I can change my number (allowing me to position my needle on the gauge chart).

The total value of my needle column B is 360 which matches the degrees of a circle. The 2 represents the pie wedge that will be the needle of my gauge. If I want the needle to be thicker, I can increase this value to be 3, 4 or 5.

This creates the framework for my gauge chart.

To create my gauge I start by highlighting cells 3B through B6. I then select Insert > Pie > 2-D Pie.

Next I want to rotate the pie chart 90 degrees clockwise placing the blue portion on the bottom. To do that, right mouse click on the blue area of the pie chart and select Format Data. Under Series Options, select Angle of first slice and set it to 90. When you close the window you will see the chart has rotated.

Now we have the foundation for the chart. You won’t need the legend on the right so you can remove it by left mouse clicking on it then pressing the delete key.

Next I want to make the bottom ring of the circle transparent. I left double click on the bottom section to select the bottom portion of the circle to select it and then right mouse click on it. I select Format Data Point to bring up the Format Data Point Dialog Box. I click the Fill option and select No Fill. If the boarder is visible, I also change the boarder color to no fill.

I repeat this process removing the red and purple portions of the pie chart (leaving only the green “needle”.

I make the whole pie chart background transparent by clicking on the pie chart and then selecting from the format tab > Shape Styles >Shape Fill > no fill. I also change the needle color to red by double clicking on the green area and changing the fill color to red.

When done I have my needle.

I can cut the pie chart than past it onto my dashboard tab. I little bit of adjusting and I can align the needle just the way it needs to be.

Now you are probably noticing the numbers in the gauge. I create each number as a separate text box (Insert tab > Text > Text Box) and then move them into the correct position. 

The 83.59% of Sales Goal is created with a text box also. I point the text box to cell A22 which I have changed the color of the cell text from black to white so you can see it. The formula is listed in cell A24 for your reference.

The tweet boards are created the same way, pointing a text box to the appropriate cell on the calculation tab. For example the formula for Fashion Sales is =Calculations!I4. HVAC Sales is =Calculations!I5.

Same with the oval tweets, these are simple text boxes pointing to the calculations tab.

To finish I add a few column charts for the side gauges and I have a completed dashboard.

Well there are many steps to create this dashboard, but most of them are repetitive in nature and once you create one, you can easily replicate the step for the other components.