(by 2 people)
Work Smarter Look Smarter
I work in the 9-5 corporate world much like millions of others who graduated college with a business degree. I sit in a cubicle surrounded by other cube dwellers who have similar jobs to mine.
What sets me apart from my co-workers is my ability to get things done and the ability to analyze large sets of data which helps manage our business. Over the years I have watched my organization shrink from 200 employees to just 30. I have been told by my boss I will be the last employee in the group becuase of my knowledge and ability to get at information that eludes others.
So does that mean I am any smarter than the folks around me. Not at all. I just work smarter. While they can use the same data inputs as I do, their output quality is lacking. I try to help them out by teaching them my tricks and tips, but they usually lack the desire to learn them.
If you are reading this , I hope you are in the position of wanting to improve your productivity, improve your skills, and seeking to become a valuable resource to your company and your co-workers. None of these tips are earth shattering or require any programming knowledge. They are simple tasks that speed up your ability to work with data or allow you to present better information to your boss and executives.
Over the next few weeks I will be adding articles about Excel's many useful features to help you become more productive and work smarter.
EXCEL 2007 Formulas
This is a great book. There are good examples of Pivot tables, charting, and an introduction to VBA.
Chapter 14 on Arrays is one of the best time savers I have ever seen. If you have purchased Office 2007 be sure to get this book.
Chapter 14 on Arrays is one of the best time savers I have ever seen. If you have purchased Office 2007 be sure to get this book.
Excel 2007 Formulas (Mr. Spreadsheet's Bookshelf)
Amazon Price: $29.69 (as of 11/23/2008) 
Excel Pivot Tables Part I
The best analysis tool to improve your understanding of data
Lets assume for a moment that you have access to a large volume of data on a regular basis. Some of this data may already be summarized but still contain several columns of information which you sort and resummarize to learn about your business. Pivot tables allow you to reshape your data and to summarize it various ways.
The example data I will use is Store Sales. Imagine a store with broad categories and unique products which are sold across the country. Common reports would be category trends by day, product performance, top selling locations, and many more reports that would guide a buyer, inventory manager, executive, or store manager.
Our mythical report will have the following fields:
date, category, item#, sales_region, store, qty_sold, amount, qty_returned, return_amt.
As you can imagine this report could be several thousand lines long and sorting would take a while.
Lets assume you need Category Sales by Date and by Reqion for your director over the buyers.
In your spreadsheet select "Data" from the top menu then "Pivot Table". A wizard should appear prompting you for the data type. You should be using an "excel list or database" and your output should be a "pivot table". Click Next.
Input the range of your data (a1:I25987). Click Next.
Where do you wish to output your data? I usually select "new worksheet". Click Layout.
(continued in part II)
The example data I will use is Store Sales. Imagine a store with broad categories and unique products which are sold across the country. Common reports would be category trends by day, product performance, top selling locations, and many more reports that would guide a buyer, inventory manager, executive, or store manager.
Our mythical report will have the following fields:
date, category, item#, sales_region, store, qty_sold, amount, qty_returned, return_amt.
As you can imagine this report could be several thousand lines long and sorting would take a while.
Lets assume you need Category Sales by Date and by Reqion for your director over the buyers.
In your spreadsheet select "Data" from the top menu then "Pivot Table". A wizard should appear prompting you for the data type. You should be using an "excel list or database" and your output should be a "pivot table". Click Next.
Input the range of your data (a1:I25987). Click Next.
Where do you wish to output your data? I usually select "new worksheet". Click Layout.
(continued in part II)
Book Recommendations for Excel Pivot Tables
Pivot Table Data Crunching (Business Solutions)
Amazon Price: $23.09 (as of 11/23/2008)
List Price: $34.99
Usually ships in 24 hours
Excel Pivot Tables Recipe Book: A Problem-Solution Approach
Amazon Price: $31.49 (as of 11/23/2008)
List Price: $34.99
Usually ships in 24 hours
Excel Pivot Tables Part II
This is where the pivot table fun begins. The wizard will show you a graphic with sections titled Page, Column, Row, and Data. Each of these allows you to summarize the data elements by the items you specify in Row Column or page.
The list of fields will be to the right of the graphic. Drag the field "Date" into the "Column" section. This will display the date values across the top of the report. Next drag the category field handler into the "Row" section. Then drag the sales_ region into the "Page" section. And finally drag qty_sold into the "Data" section. The data section is the items to be summarized but you can drag other non-numerical fields there and the pivot table will count them rather than sum them. Click "OK" then "Finish".
You should now have a new page with a pivot table. You will notice that the Sales Reqion section is set to "ALL". You can pick a sales region to display using the dropdown. Making it easy to analyze regional sales. You can also limit the values shown in Column and Row sections to further refine your report.
By using the various views of your data you can easily analyze the large volumes of information. You can even drag the "PAGE" section regional_sales down to the "ROW" section and put the sales region in front of or behind the category values. You can then filter all but 2 sales regions to display for comparison.
I hope you can see the value of reshaping your data into a pivot table. There are many ways to look at the same info and some views will help you manage your business better.
Who cares if the stores in the North-East sell 10% more products than stores in the South-East? The Buyer and Inventory managers do, as well as executives and store managers. It may indicate problem stock levels in the southeast vs northeast, or personell issues, or even variances of product placement within the stores.
Pivot tables arm you with the ability to dig into your data and answer questions that few dare to ask.
Homework: Repeat this excercise dragging multiple variables into the Row and Column sections to see how data can be manipulated when controlling relationships of the summary fields.
The list of fields will be to the right of the graphic. Drag the field "Date" into the "Column" section. This will display the date values across the top of the report. Next drag the category field handler into the "Row" section. Then drag the sales_ region into the "Page" section. And finally drag qty_sold into the "Data" section. The data section is the items to be summarized but you can drag other non-numerical fields there and the pivot table will count them rather than sum them. Click "OK" then "Finish".
You should now have a new page with a pivot table. You will notice that the Sales Reqion section is set to "ALL". You can pick a sales region to display using the dropdown. Making it easy to analyze regional sales. You can also limit the values shown in Column and Row sections to further refine your report.
By using the various views of your data you can easily analyze the large volumes of information. You can even drag the "PAGE" section regional_sales down to the "ROW" section and put the sales region in front of or behind the category values. You can then filter all but 2 sales regions to display for comparison.
I hope you can see the value of reshaping your data into a pivot table. There are many ways to look at the same info and some views will help you manage your business better.
Who cares if the stores in the North-East sell 10% more products than stores in the South-East? The Buyer and Inventory managers do, as well as executives and store managers. It may indicate problem stock levels in the southeast vs northeast, or personell issues, or even variances of product placement within the stores.
Pivot tables arm you with the ability to dig into your data and answer questions that few dare to ask.
Homework: Repeat this excercise dragging multiple variables into the Row and Column sections to see how data can be manipulated when controlling relationships of the summary fields.
Was this lens on Excel Tips helpful to you?
Loading poll. Please Wait...
Things to come....
My planned topics over the next few days. You can use F1 to search for these in Excel Help if you cant wait.
My planned topics over the next few days. You can use F1 to search for these in Excel Help if you cant wait.
- Pivot Tables
- Data Subtotal
- Summary Flags
- VLOOKUP
- Charts
- Data Form
Enhancing Data by Creating your own Data Summary Flags
Combine this technique with Subtotals or Pivot Tables for more data analysis power.
In our Pivot table Example we used an existing data resource to analyze the Regional Sales for a given time period by broad categories. Now lets suppose the executive over the central, midwest, and west buyers wants to look at his regions only.
We could show him all regions on the report and let him filter what he doesn't want to see, OR we could create the report for him manually by selecting the regions in the dropdown, OR we can create a new flag field to Identify his regions.
Assuming other Executives will also want to see only their regions we can create a field to support them all. After the last column of your dataset add a column called "regional_exec".
Sort your data by region.
In the new regional_exec column we will place the names of the executive for each region. Doing this the old fashioned hardway we can type in the name of the first executive with the "Central" region (i.e. Bob Smith) and copy it for all the Central region lines of the database. Repeat for each region. IN a future article I will show you how to create a short list of regions and executives and populate this field using VLOOKUP.
For the purposes of this illustration the manual method works fine. When complete you should now have a full column of info with the executives names. You can now use this field in a pivot table in the PAGE or ROW sections to summarize the regions by Executive, with or without regional value details.
Other ways of creating flags is to use true/false test. Example we can ID all stores with sales over $100,000 by creating the flag field and inserting a formula.
=IF (G2-I2>100,000,">100K","<100K")
*G2 is the "sales_amount" field and I2 is the returns_amount field. Their net must be greater than 100K to return the true value of ">100K".*
Select the column and Copy then Edit/PasteSpecial as Values to fill the columns with the flag rather than the formula.
This flag can now be used in the pivot table to identify stores selling more or less than $100K per day.
Suppose the executive wants top stores in each region by national rank. We sort our data based on sales_amount, Create a new field called Rank, and fill the field with #s 1, 2, 3, etc. Then in our pivot table we will use the storeRank field pulling the Min rank values for any level of summary. In the pivot table we add storeRank to the DATA section and change it from Sum to MIN by right clicking on the field name.
There are many other uses for flag fields limited only by yourimagination.
We could show him all regions on the report and let him filter what he doesn't want to see, OR we could create the report for him manually by selecting the regions in the dropdown, OR we can create a new flag field to Identify his regions.
Assuming other Executives will also want to see only their regions we can create a field to support them all. After the last column of your dataset add a column called "regional_exec".
Sort your data by region.
In the new regional_exec column we will place the names of the executive for each region. Doing this the old fashioned hardway we can type in the name of the first executive with the "Central" region (i.e. Bob Smith) and copy it for all the Central region lines of the database. Repeat for each region. IN a future article I will show you how to create a short list of regions and executives and populate this field using VLOOKUP.
For the purposes of this illustration the manual method works fine. When complete you should now have a full column of info with the executives names. You can now use this field in a pivot table in the PAGE or ROW sections to summarize the regions by Executive, with or without regional value details.
Other ways of creating flags is to use true/false test. Example we can ID all stores with sales over $100,000 by creating the flag field and inserting a formula.
=IF (G2-I2>100,000,">100K","<100K")
*G2 is the "sales_amount" field and I2 is the returns_amount field. Their net must be greater than 100K to return the true value of ">100K".*
Select the column and Copy then Edit/PasteSpecial as Values to fill the columns with the flag rather than the formula.
This flag can now be used in the pivot table to identify stores selling more or less than $100K per day.
Suppose the executive wants top stores in each region by national rank. We sort our data based on sales_amount, Create a new field called Rank, and fill the field with #s 1, 2, 3, etc. Then in our pivot table we will use the storeRank field pulling the Min rank values for any level of summary. In the pivot table we add storeRank to the DATA section and change it from Sum to MIN by right clicking on the field name.
There are many other uses for flag fields limited only by yourimagination.
VLOOKUP Function
merging data sources with common fields
One of the most common problems we run into in data analysis is merging two lists. Getting values from one list into another based on a common field is usually achieved by sorting and manual manipulation.
But I have found Excel's VLOOKUP function to be a faster more efficient method.
Assumptions: for the purposes of this excercise we will assume we have a store level sales summary and a store Address list to merge. The common field is store number. We'll append the sales to the store address list.
Rules: The store numbers in both datasets should be formatted the same way. IF they are truly numberic, format them as numbers and get rid of leading zeros. The store sales info should have store number in the first column and be sorted by the store number.
The FORMULA:
=VLOOKUP (a2, sales!a2:e567, 3, false)
=VLOOKUP is the excel function and the items in parenthesis are the criteria.
a2 represents the first Store Number cell in our Store Address List.
sales!a2:e567 is the range of data to lookup in our Store Sales Summary
3 - we will pull data from the third column (ie sales amount) of the Store Sales Summary. Any value between 1 and 5 works for the a-e range of our data cells.
False - If this parameter is set to "false" the function returns "NA#" if it does not find a match. If set to True, it would find the next closest value for store number and report its sales amount (not what we desire).
Sample Data
Store List
Store | Address | City | State | Sales
26001 | 123 easy street | Lula | GA | Formula
26002 | 345 lucky street | Atlanta | GA | Formula
26003 | 678 main road | NY |NY | Formula
Sales Data
Store | zone | sales amt | Returns | writeoffs
26001 | South | 1000 | 0 | 0
26003 | North | 5000 | 200 | 0
Assuming our Store Header is in position A1 in both data sets Column A is our common store numbers. If we enter the vlookup formula in the Sales column of the Store Address we would expect store 26001 to be populated with 1000.
Store 26002 would be NA# because there is no corresponding sales data. The store might be closed or not selling the items in the sales report. Store 26003 would be populated with
5000.
We would copy the formula all the way down our report.
We could also add a column to grab returns and write offs to add to the Store Address List.
We can then use this list in a Pivot Table and get Sales/Returns/Writeoffs by State.
Format your common data and you can merge any two data sets for further analysis.
But I have found Excel's VLOOKUP function to be a faster more efficient method.
Assumptions: for the purposes of this excercise we will assume we have a store level sales summary and a store Address list to merge. The common field is store number. We'll append the sales to the store address list.
Rules: The store numbers in both datasets should be formatted the same way. IF they are truly numberic, format them as numbers and get rid of leading zeros. The store sales info should have store number in the first column and be sorted by the store number.
The FORMULA:
=VLOOKUP (a2, sales!a2:e567, 3, false)
=VLOOKUP is the excel function and the items in parenthesis are the criteria.
a2 represents the first Store Number cell in our Store Address List.
sales!a2:e567 is the range of data to lookup in our Store Sales Summary
3 - we will pull data from the third column (ie sales amount) of the Store Sales Summary. Any value between 1 and 5 works for the a-e range of our data cells.
False - If this parameter is set to "false" the function returns "NA#" if it does not find a match. If set to True, it would find the next closest value for store number and report its sales amount (not what we desire).
Sample Data
Store List
Store | Address | City | State | Sales
26001 | 123 easy street | Lula | GA | Formula
26002 | 345 lucky street | Atlanta | GA | Formula
26003 | 678 main road | NY |NY | Formula
Sales Data
Store | zone | sales amt | Returns | writeoffs
26001 | South | 1000 | 0 | 0
26003 | North | 5000 | 200 | 0
Assuming our Store Header is in position A1 in both data sets Column A is our common store numbers. If we enter the vlookup formula in the Sales column of the Store Address we would expect store 26001 to be populated with 1000.
Store 26002 would be NA# because there is no corresponding sales data. The store might be closed or not selling the items in the sales report. Store 26003 would be populated with
5000.
We would copy the formula all the way down our report.
We could also add a column to grab returns and write offs to add to the Store Address List.
We can then use this list in a Pivot Table and get Sales/Returns/Writeoffs by State.
Format your common data and you can merge any two data sets for further analysis.
Date Formats - Day of week text format
There are times in data analysis you might wish to know the day of the week. For use in a pivot table or charted info the day of week must be in a text format.
There are 2 ways to get day of week displayed in your data. If you merely need to see the day you can format the date field using "Format Cells" select "Custom" then enter "ddd" or "dddd" for the cell to change from 8/1/2006 to "Tuesday".
If however you need the date and the day next to it as text there is a simple formula to create the text formatted day of the week. Assume the date is in cell "c5" and we wish the day of week to display in cell "c6".
In cell "C6" type " =text(c5, "ddd") ".
Copy Cell c6 to other rows if necessary and as a final step to trully create the field as text, Copy all cells where the day of week is displayed and use "Edit" select "Paste-Special" then "formulas as values".
Using "ddd" gives a three character day of week like "Tue" vs "dddd" gives the whole day name "Tuesday".
There are 2 ways to get day of week displayed in your data. If you merely need to see the day you can format the date field using "Format Cells" select "Custom" then enter "ddd" or "dddd" for the cell to change from 8/1/2006 to "Tuesday".
If however you need the date and the day next to it as text there is a simple formula to create the text formatted day of the week. Assume the date is in cell "c5" and we wish the day of week to display in cell "c6".
In cell "C6" type " =text(c5, "ddd") ".
Copy Cell c6 to other rows if necessary and as a final step to trully create the field as text, Copy all cells where the day of week is displayed and use "Edit" select "Paste-Special" then "formulas as values".
Using "ddd" gives a three character day of week like "Tue" vs "dddd" gives the whole day name "Tuesday".
