Excel Must Knows

I have been using Excel myself for near on 15 years and would consider myself a power user. Despite this I can only do the most basic of macros. To be honest I never even use macros. In my view macros are the most overrated feature of Excel I have ever come across.

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

Dead Stocks

We are probably all familiar with aged stocks. Those items that appear way down the right hand side of the aged inventory register are generally there for a reason .. slow moving, obsolete, or simply forgotten about in the pace of day to day business.

So what then is a dead stock ? It is best explained with a diagram.

In this simple example assuming constant demand and periodic purchasing, the dead stock component is that part of stock that is never consumed. It represents waste.

I only have metrics from my own experience but in my view it would not be unusual for 1/3 of inventory to be classed as 'dead stock' over a typical 3 month period.

Dead Stock vs Aged Stock

Most if not all aged stock is dead stock, however, not all dead stock is aged. This distinction is important. In the above diagram for example it is clear that the stock is not aged, in fact it turns over approximately once per month. Therefore it will never show up on an aged stock report. The fact that it is turning over quickly means that it can be fixed relatively quickly.

Dead stock management is therefore a very effective way to reduce inventory in the short term.

Identifying Dead Stock

Aged stocks by their nature are relatively easy to identify (using an aged stock report or aged inventory trial balance), but typically can be challenging to fix. If the material is slow moving, then it will take some time to run it out. If the material is obsolete, then the activity of reworking, writing off, or otherwise disposing of it can take some time and generate cost.

Dead stocks on the other hand can be difficult to identify, but the fast moving dead stocks by their nature can be fixed relatively quickly.

For SAP users there is a transaction called MC50 "Dead Stock Report" that quickly identifies dead stocks. Run at a plant level, or even for a list of materials, the report will rank dead stock balances over the specified time period. Over longer time periods expect the dead stock balance to be smaller than over short time periods because over a longer time period there is a greater probability that the stock will have run lower than over a short period.

Ive included a snapshot of the SAP MC50 transaction below. A tip for optimising the performance of the report is to run it from values 1 to 99999999, rather than unbounded.

Reducing Dead Stock Balances

Reducing the dead stock balance comes down to planning and process control. If your purchasing and or planning processes are highly automated, the remedies tend to revolve around forecasts, safety stocks, lead times, re-order points, and re-order quantities which can be easily maintained.

The MC50 Dead Stock report will automatically rank your dead stocks from highest to lowest. You can export the data into an excel spreadsheet to create a Pareto Chart similar to the one below.

In this example, there are a total of 61 materials in inventory for this product group. In fact, when I compared the dead stock balance to the inventory balance, 62% of the total inventory was dead over the last 3 months !

Looking at the above chart, 50% of total dead stocks are made up of just 10 materials. What this means is that I can reduce total inventory for the product group by 1/3 if I just fix the dead stock balances on 10 items. When the problem is broken down like that it doesnt seem so difficult any more.

I can break the problem down into 10 material level strategies, and solve them all one by one or simultaneously.

Viewing the inventory history

SAP has a neat functionality built into the Dead Stock report whereby you can view a graphical summary of the dead stocks over time.

Run the dead stock report, and place the cursor on one of the material numbers. From the top menu buttons, click on 'Detailed Display'. You will see a pop up window with a number of options, and you can jump from these options to a number of screens. Click on 'Stock Level' and hit the green tick.

This is a great tool for getting a quick feel for a material inventory. In the above example we can see that back in 2007 we had an overzealous purchasing officer who brought in way too much of this material, and in the years since then we have fine tuned our purchasing and forecasting in order to manage it a bit better. Over most of 2010, however, most of the stock has fallen dead yet we have still brought more in. In this instance I would be looking at the forecast accuracy on this material, with a view toward reducing the safety stock. As you can see though, the material turns fairly regularly, and therefore is unlikely to appear on our ageing reports.