There are 3 Excel must knows that will get you through almost every business analysis problem - these are the pivot tables, the vlookups, and the sumifs. In addition, to make ones life a bit easier there are a number of short cuts and basic features that are imperative to know in order to make your life more convenient.
I'll give a brief overview below, but intend to add more detailed training notes in future blogs :
Pivot Tables
Without a doubt the pivot table is a fundamental feature of Excel that bridges part of the gap between a what is a flat file and a database application.
Pivot table will summarise your data in a format that is easy to read, and is also easy to change should you wish to view your data slighly differently. Things like summarising sales by region or by product, or summarising deliveries by state or by date, are typical applications of the pivot table.
I tend to use pivot table also when I want to condense a list to its components as its the fastest way I have found of doing this. For example if I have a list of every single customer order over the month, and I just want a list of products that the customer has purchased, or I just want to know the list of purchase order numbers (without all the detail) I would use pivot table to quickly return that information.
Another way of doing the same thing would be to use the subtotal function, but I am not in favour of using subtotals and rarely use them for the sole reason that they mess up what was originally a perfectly good data file by inserting rows and data which more or less renders the data file useless for any future pivot tables.
The pivot table wizard is accessed through Data menu. I am using a Mac in this instance, but on a PC it is pretty much the same.
You will need a data file to practice the pivot table function.
The Pivot Table wizard is fairly self explanatory and with a bit of practice you can work out pivot tables without needing to go into detailed training notes. Just remember to click on the 'Layout' button to get into the meat of the process. The Pivot Table wizard will default to creating the Pivot Table on a new data sheet. I would always recommend this option as data sheets are free, and if you insert the Pivot Table in an existing data sheet it will overwrite any pre-existing cells.
One tip is always to double click the row attributes to get rid of subtotals as they tend to clutter up your pivot table with fairly meaningless aggregations of data. Get rid of the subtotals by double clicking the row attribute within the layout function of the wizard and changing the radio button from 'automatic' to 'none' for each of the row attributes. You can always add them in again later if you decide that you do want them by right clicking the pivot table and selecting the wizard.
90% of the time also you are using Pivot Table to sum data. The wizard (for some reason known only to Microsoft) will default to a count of data option. In the layout function of the wizard you need to double click on the Count Of .. button in the data section and change it to Sum Of .. in order to correct this.
One great feature of the pivot table is that you can easily drill down on any calculated cell to take a closer look at the data. Just try it. Double click on the data, and Excel will create a new sheet with a filtered list from the original data file containing the relevant data. This is a very fast alternative to Filtering the data. To avoid cluttering up your file always remember to delete the sheet afterwards (unless obviously you want to keep it).
One thing to remember about Pivot Tables is that they take a snapshot of the data as it existed when the Pivot Table was originally created. If you change any of the data in the original data file you will have to right click the Pivot Table and choose 'Refresh Data' for the Pivot Table to update.
Vlookups
If Pivot Tables are the bread then Vlookups are the butter.
Vlookups enable you to cross populate 2 or more different data files. For example if you have one data file containing sales by customer, but you have another data file containing customer by state, and you want to create a file containing sales by state, then you are looking at using Vlookups.
The critical thing to remember about Vlookups is that they are only good for 1 to 1 relationships. In the above example, if a customer has more than 1 state, then Vlookups will not work and you need to be looking at Sumifs (next section).
To practice Vlookups you will need 2 different data files with one thing in common, and it has to be a 1 to 1 relationship. Type in '=vlookup(' and hit enter for the wizard to pop up.
I am not going to go into the detail here but there is another critical thing to remember about Vlookups... The 4th attribute is a false/true, or 0/1 attribute. In my 15 years of excel use I have never used a '1' as the 4th attribute. Unfortunately, in its wisdom, Microsoft defaults the 4th attribute to a '1'.
What this means is as follows. If the 4th attribute is a '1' and the vlookup cannot find an exact match, then it will find the next closest match (which defeats the purpose of using a Vlookup). It is imperative that you make sure the 4th attribute is a '0' so that if the Vlookup cannot find an exact match it will return a 'NA#' value. This will tell you that there is something wrong with your formula, or that there is some data missing from one of your files.
There will be times when you want your formula to return a zero instead of 'NA#'. For example if you are looking up a sales volume from another data file, and if there is no sales volume for the product you are looking up, the Vlookup will return a 'NA#' Whilst this is a logical outcome, you might have another sum formula adding up all the sales volumes and the 'NA#' will prevent the sum formula in the secondary process from adding up the values.
In this instance you need to use an =if(iserror(), , ) or =if(isna(), , ) to convert the 'NA#' value to a zero to facilitate the secondary sum process.
Sumifs
If Pivot Tables are the bread, and Vlookups are the butter, then Sumifs are the mayonnaise that you might need when the Vlookups just might not cut the mustard.
As we know Vlookups are good for 1 to 1 relationships, but what about when there is a 1 to many relationship ?
For example you might have a file containing every customer sales order, and you have a list of specific customers, but you want to return a list of total sales for your specific list of customers.
A Pivot Table is not the best option because you are not interested in every customer, and a Vlookup will not work because there are multiple sales per customer.
One solution is to do a Pivot Table on the sales by customer, and then do a Vlookup on the total sales for each customer on your list of specific customers. A Vlookup on a Pivot Table will certainly work, but the limitation of a Pivot Table is that it takes a snapshot of the data, and it does not return live information. The Vlookup on a Pivot Table solution is therefore very clunky and there is a much more elegant solution.
The elegant solution is the Sumif formula.
The beautiful thing about Sumif is that it only relies on 3 attributes; the data field you are looking up, the reference field that you are looking up on, and the data field for which you want to return the summed information. Unlike the Vlookup it will never return a 'NA#' value. If it cant find anything it will just return a zero.
For example if I want to add up the total sales by customer or by material on a list of customer purchase orders; and I have a list of the specific customers or materials that I am interested in, the Sumif formula is the perfect solution.
Honestly I spent years in the wilderness doing Vlookups on Pivot Tables until someone showed me the Sumif solution and I have never done a Vlookup on a Pivot Table since then.
Short Cuts and Basic Features
Copying Down by Double Clicking the bottom right hand corner of a cell
Text to Columns to convert text fields into numeric fields
Find and Replace "." with "/" to convert SAP dates into Excel dates
Window - Freeze Panes to lock column and row headings in for analysis
=trim() to remove superfluous blank spaces
'Page Setup - Rows to Repeat at Top' to format large printouts
Tools - Options - Gridlines to remove excel gridlines for printing/presentation
No comments:
Post a Comment