Best Microsoft Excel Bloggers

Tuesday, March 2, 2010

Pivot Tables- Show Differences

PIVOT TABLES - SHOW DIFFERENCES
Everyone knows how absolutely cool Pivot Tables are but not everyone is aware of some 'hidden"pivot table features. Today I wanted to talk about comparing values in a pivot table.  Frequently, you need to compare against a base budget number or you may want to compare last year against this year. The good news is that this is very easy to do with a pivot table.


Create your pivot table as you normally would
  • Right-click on the pivot table
  • Select Value Field Setting
  • Click on the Show Value As tab
  • Click the drop-down arrow and make your selection (% difference from, difference from,% of total)

 In the example, below, I changed Normal to Difference from and then selected

Year from the Base Field and selected as 2007 as the Base item.
The field you want to base the difference on must be included in the pivot table.Click OK
The resulting pivot table shows how 2008 and 2009 compare against 2007. Since 2007 is the base no numbers are displayed in the 2007 column.



While numbers show a lot, sometimes percentages are even more useful.
 Below is the same pivot table only I selected % Difference from 2007.
 

Friday, February 26, 2010

An Accounting Game!

For all you CPAs on the East Coast who are suffering through the blizzard and looking for something to do - check this out.

Free accounting game.  Yes- an accounting game!
So, if you are sitting at home looking out the window at the piles and piles of snow ( or sitting in an airport!) , give this a try. Something to do.  Actually it has been around for awhile and I have a link on my site to it but it's late on Friday and this is all I could come up with.

Check out http://www.revenuerecognitionchallenge.com/
a free online accounting game. Yes- that's right... free accounting game . If you report different revenue types correctly as unexpected scenarios occur and pass the "Schmarbanes-Schmoxley"audit, your stock price goes up and fireworks go off!


It's fun in a nerdy way... what I can say!
Have a good weekend and stay warm.

Thursday, February 25, 2010

Cumulative Totals

Sometimes it is the easy stuff that escapes us.
Below is a screenshot showing how to create a cumulative total.

In the example below, I want to create a Running Total and track my T-Shirt Returns.
  • Click in cell D3 and type =SUM($B$3.B3)
  • Press Enter
  • Copy the formula down.



















Why this works:                                                                            
The $B$3 creates an absolute cell reference so that as the formula is copied down, it always goes back and refers to B3.
If you click in cell D4, after you create the formula, you would see that the formula has now changed to
=SUM($B$3:B4). The first cell reference is an absolute and anchors the cell reference in place but the second cell reference moves down a row.
The formula at D15 would of course read as =SUM($B$3:B15).

Wednesday, February 24, 2010

View Multiple Worksheets in the Same Workbook

SEE MULTIPLE WORKSHEETS IN THE SAME WORKBOOK AT ONCE

Frequently people like to look at more than one worksheet in their workbook and flipping back and forth can be bothersome.

So, if you want to view 2 or more worksheets at the same time on your computer screen all you have to do is open a second window.

Select the View Tab and then in the Window group, click New Window.
It doesn’t look like anything happens when you do this however if you look up at the title bar, you will now see a :2 after the file name to show that a second copy (window) has been made.
You can then use each window to display and edit different parts of the same workbook.

The files are mirror images of each other and any change is actually appearing in both at the same time.


Tip: Close out the :2 version first and then you will see the :1 disappear from the original. If you close out the :1 first, it is not a big deal, however you will always have that :2 after the name and then you are always wondering whatever happened to Version 1. :)









Tuesday, February 23, 2010

Smart Tables in Excel 2007

Excel 2007 offers a wonderful feature that not many people are aware of.  It is called a Smart Table and, in my mind, is basically an advanced version of Excel 2003’s List feature.

Excel considers a list or table to consist of data that is adjacent and does not contain a totally empty column or a totally empty row.
With the Table feature, you can have new data formatted as you enter it, shade alternate rows, instantly sum up the right column and quickly name the table.




The most exciting feature however is that tables are dynamic. As you add or delete data, Excel automatically updates and formats the table. So, what does this really mean for you? It means that if you base a chart, a pivot table or a formula on a table, as data changes in the table, it will automatically update the chart or whatever it is based on. This ensures that you are working with the most current data.


CREATE A TABLE
To create an Excel table


  • Type your data into the worksheet as you normally would
    • Make sure that you include column headings
    • Do not leave any empty rows or columns
  • Click in the data and press CTRL+T.
The Create Table dialog box will pop up so that you can double-check the data range and to ensure there is a checkmark in My Table has headers.








  • Click OK.

The table feature automatically bands and shades every second row and it applies filter arrows to the column headings. A definite timesaver.
 
 
 
 
 
 
When the table is created, a contextual toolbar also appears.
It allows you, among other things, to quickly sum a column, name the table, change formatting styles as well as remove duplicates.
 
 
Adding data into the spreadsheet is not any different than what you have done before – you simply select a cell in the row immediately below the last row and type the value in.

-If you enter a formula in the adjacent column, Excel will automatically copy it down the entire table and format it.
-If you enter data in the adjacent row, Excel will automatically format it so that it matches the table.


To me, the exciting part of the table is that when you add the total row, Excel automatically calculates the values in the rightmost column. You can change that summary operation or add a total to another column easily.



To Create a Total Row
-Make sure your cursor is in the table
-Click on the Design tab under the new Table Tools tab
-Click Total Row










The last row of your total now displays the sum of the right most column.



Ms. Excel- Resident Excel Geek