Change Default Font and Number of tabs

By default Excel will display 3 sheet tabs when you open a workbook. You can then add or delete tabs as needed. Some users never use all three tabs while others use more then 3.

Well you can modify how many tabs open by default.

In 2007 From the Office Button select Excel Options

Select the Popular tab and change the “include this many sheets” number.

You can also change the default font and font size from the popular tab.

SumIf vs Pivot Table

Earlier this week I received an email from Raj. He was new to Excel and had a problem he could not figure out. Raj had a list of accounts and was trying to get the total quantity of purchase orders and the total value of the purchase orders for each account. The problem he was having was that there were multiple lines for each account (based on the fiscal month). Here is a sample the data he had.

	A	B	C	D
1	Account Number	Fiscal Month	Number Of Purchase Orders	Total PO Value
2	1000002	Jan	3	1864
3	1000004	Jan	5	3232
4	1000009	Jan	4	4469
5	1000004	Feb	1	3368
6	1000007	Jan	6	3675
7	1000009	Feb	2	2953
8	1000005	Jan	4	5994
9	1000009	Mar	1	4812
10	1000001	Jan	3	5221
11	1000008	Jan	4	5126
12	1000007	Feb	2	5812
13	1000002	Feb	5	3334
14	1000005	Feb	4	4398
15	1000008	Feb	4	2948
16	1000005	Mar	1	2184
17	1000002	Mar	6	3670

I have highlighted the accounts above to help show the multiple rows.

Well there are a few ways to solve this problem.  To start I want to have a column with just a list unique accounts (no duplicates).

F
Account Number
1000001
1000002
1000004
1000005
1000007
1000008
1000009

I then can use the SUMIF formula to solve Raj’s problem.

F	G	H	I	J
Account Number	PO Order Total	formula	PO Total Value	formula
1000001	3	=SUMIF(A:A,F7,C:C)	5,221.00	=SUMIF(A:A,F7,D:D)
1000002	14	=SUMIF(A:A,F2,C:C)	8,868.00	=SUMIF(A:A,F2,D:D)
1000004	6	=SUMIF(A:A,F3,C:C)	6,600.00	=SUMIF(A:A,F3,D:D)
1000005	9	=SUMIF(A:A,F6,C:C)	12,576.00	=SUMIF(A:A,F6,D:D)
1000007	8	=SUMIF(A:A,F5,C:C)	9,487.00	=SUMIF(A:A,F5,D:D)
1000008	8	=SUMIF(A:A,F8,C:C)	8,074.00	=SUMIF(A:A,F8,D:D)
1000009	7	=SUMIF(A:A,F4,C:C)	12,234.00	=SUMIF(A:A,F4,D:D)
	55		63,060.00

So what is this formula doing? For the PO Total column the sumif formula takes the account number in column F and then looking in column A for the match. It then sums all the values in column C that have the match.

 Now I could also create a simple pivot table to solve Raj’s problem.

Both provide the answer Raj is looking for. Want to see more? Download the example here.

Camera Tool

Last week I demonstrated how to create a box chart. Didn't see the post? click here

Someone wrote to me asking why they would ever want to use a box chart when they could use a simple column chart to represent the same data. Well it’s a good question so let’s look a bit closer at the two techniques.

Here’s a simple dashboard comparing three different types of leaf blowers. It also shows the box charts compared to column charts.

Notice how I matched the leaf blower color in my box charts as well as my column charts. This helps provide continuity to the dashboard. Both the box chart and column charts show the same information, but the way it the data is represented is different.

And that’s the point. Excel allows you to represent data in different ways. Most people will settle for the built in charts Excel provides and that’s fine. However there are alternatives and the box chart is a good example of this. It’s a creative use of conditional formatting that provides a user the flexibility to display their data differently.

The person who emailed me also raised a valid point regarding the columns and how I needed to size them to make the box chart. He pointed out that it would be difficult to properly display data under these boxes due to the column width. I agree. It would not be ideal to place data under the boxes. However there is an easy solution.

Notice the columns D – M, O – X and Z – AK in the below example. These are the columns that the reader was referencing. They are very small in width.

To get around this problem, you can use the camera tool to place the image of the chart onto your dashboard. The actual chart itself can be on a different tab. And of course since you are using the camera tool, the image is not static (it will change as your data changes).

In the attached example I have my box charts on the data tab and the Dashboard “links to the box charts” by the use of the camera tool.

Now if you are not familiar with the camera tool, you should become acquainted.
The camera tool is a hidden feature in Excel that allows you to copy a rectangular area in your workbook and then creates a mirror image of the area as a drawing object. You can then move and resize the object. If the contents of original rectangular area changes then the mirror image also changes.

To use the camera tool, you must add the tool to a tool bar in excel menu area.

From the menu Click the drop down arrow on the tool bar and select More Commands

1. From the menu Click the drop down arrow on the tool bar and select More Commands
2. In the Customize tab and select All Commands.
3. Scroll down in the commands area until you see a little camera icon.
4. Click the Add Button then the OK button to add this to your tool bar.

To use the camera tool on our box chart first highlight the chart. When I select my cells they are outlined with a black boarder.  Next click on the camera icon that we added to our tool bar.

Now click anywhere in your worksheet and Excel will places a snapshot of the range you selected.

 Now you can move and resize this as needed.

Notice how my column widths are normal size on the dashboard tab.

So using the camera tool allows you to get around the problem of column widths.

The camera tool is a great feature in Excel but there are some issues that you need to be aware of. As shown here if you resize an image too small you will loose resolution. Also if you change the aspect ration of the image, you will get distortion. 

Need to see an example? Download the worksheet by clicking on this link.