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