Read an Excerpt
Excel for Marketing Managers
By Ivana Taylor, Bill Jelen, Linda DeLonais
Holy Macro! BooksCopyright © 2006 Ivana Taylor and Bill Jelen
All rights reserved.
How to Use Excel to Become a Marketing Genius
This might be a slight exaggeration, but then again, it may not be. I've heard it said that genius is the ability to spot the obvious. And that is exactly what Excel helps you to do. The problem most of us have when looking at data is seeing the information that's hidden within.
Successful marketing people know what information is important and how to pull that information together in order to get what they want from all of their constituencies: their customers, their CEO's, their service providers and the members of their teams. Don't be fooled, these people aren't any bigger, better or brighter than you are, they're just very resourceful and they know and understand the tools that are at their disposal.
One such tool is Microsoft Excel. Excel is an amazingly powerful tool and it isn't just for accounting or financial professionals. Marketing people can use it too.
As marketers, our primary job is to get and keep customers for our organizations. To do this, we follow a fairly simple process that looks something like this:
Excel can help you at each stage of the marketing process. But can it help you do your job?
Excel Makes Your Job Easy
Excel can absolutely make your job easier. Here is a short list I've compiled of marketing positions and the responsibilities and tasks that Excel can help you to do.
[??] Marketing Management
* Marketing Audit – Map a process
* Competitive Analysis
* Marketing Plan
* Pricing Analysis and Strategy
* Find New Customers
* Identify new markets
* Monitor trends
* Provide Justification for Strategies and Plans
* Customer Satisfaction tracking and analysis
* Opportunity Analysis
[??] Product Management
* Pricing and Margins
* Sales Estimates
* Launch a product
* Progress against Plan Report
* Track and monitor product introductions
* Sales Reports
[??] Marketing Communications
* Lists (customer, prospect, purchased)
* Campaign Analysis
* Lead Tracking
* Trade Shows
* Media Choice and Management
* Supplier choice and management (printers, designers, research firms)
Depending on which area of marketing you're in, you may have any or all of these on your job descriptions on your plate. Isn't it nice to know that you have a little extra help in doing them?
Get Wise Before You Analyze: Cool Things Excel Can Do So You Won't Have to
I don't know about you, but I'm one of those marketing people that want to see results yesterday. That means that I'll often jump right into doing some crazy analysis the long and hard way simply because I don't want to read an Excel book. Then sometime after I've literally wasted hours on a project, I'll learn about a function or tool that would have saved me a day of work and frustration.
Here are a few things that Excel can do that you won't have to:
[??] Forecast and calculate average growth rates with Goal Seek
[??] Calculate and format days, dates, and times Excel can do a launch countdown for product launch or days until a trade show.
[??] Copy your formula to the end of the database You'll love this if you've been scrolling down and end up over-scrolling hundreds of rows beyond your data.
[??] Quickly sort your whole database by clicking just clicking on one column
[??] Sort data in YOUR order, not just alphabetically
[??] See the sum by just highlighting an area
[??] Find a segment of your list with specific characteristics Examples: Find all companies in Texas; find all records with "Goodyear" in the field
[??] Calculate Sales over Plan
[??] Manage and customize mailing lists
[??] Segment, target, and position with Pivot Tables
[??] Calculate compound growth rates
[??] Join text and a date to create a "customized" messageCHAPTER 2
If you use an Excel spreadsheet at least a couple of times a month, these are the basics.
Here's the Situation
Out of the box, Excel has some annoying features. First, Excel hides many powerful menu items and toolbar icons in order to make Excel seem less intimidating. This might be fine for beginners, but it is not fine for you.
Here's What to Do
1. From the menu, select Tools — Customize. If the Customize option is not initially visible, choose the double-down arrow at the bottom of the menu to completely expand the menu.
2. The Customize dialog contains three tabs. Choose the third tab, which is the Options tab.
3. Choose the checkbox for "Show Standard and Formatting toolbars on two rows".
4. Also, choose the checkbox for "Always show full menus".
Adding More Buttons to Your Toolbars
Now that the toolbars are displayed on two rows, you can drag more icons to the toolbars. Go back to Tools – Customize, but go to the center tab – the Command tab. When the Customize dialog is displayed, you can drag icons off of toolbars, rearrange icons, or drag new icons to the toolbars. There are hundreds of useful icons in seventeen categories. Any of these can be dragged to a visible toolbar.
1. For example, choose the File category. The fifth icon is the Close icon.
2. Drag the Close icon from the dialog box and drop it on the Standard toolbar, near the Save icon. You will now be able to close a workbook at the click of a button.
Tweaks in the Options Dialog
1. From the menu, select Tools – Options.
The Options dialog is one of Excel's busiest, with 13 different tabs. On the General tab, there is a spin button next to Recently Used File List. This is initially set to 4.
2. Adjust the Spin button up to the maximum of 9.
Below the line on the same tab, there is a setting that causes every new workbook to have three worksheets. One old version of Excel started with this setting at 16. However, you can always add new sheets with Insert – Worksheet.
3. Dial this value down to 1.
Changing Tab Color
In Excel 2002 or newer, you can change the tab color of an individual worksheet.
1. Right-click the tab and choose tab color.
Ironically, the tab color shows up really well on the non-active worksheets, but once you select a worksheet with a tab color, the color is relegated to a tiny band at the bottom of the sheet tab.
Keeping Headings Visible
1. To always see the headings in row 1, place the cell pointer in A2 and choose Window – Freeze Panes. Everything visible above and to the left of the active cell is frozen.
2. To freeze column A on the left side of your spreadsheet, place the cell pointer in B2 before using Window – Freeze Panes.
Note that the previous technique will not cause the headings to print at the top of each printed page.
1. To print headings, use File – Page Setup. On the Sheet tab, enter 1:1 in the "Rows to repeat at top" textbox.
Printing Only a Certain Range
Say that you want to print the pivot table in V1:Z23, but not the data in A1:T9801.
1. Select the range to be printed.
2. From the menu, select File – Print Area – Set Print Area.
It is important to select the print range before invoking the menu command.
Printing Headers and Footers
Headers and Footers come in two varieties. You may already have titles and headings at the top of your worksheet.
1. In this case, use the fourth tab of the Page Setup dialog. Indicate that rows 1:5 should repeat at the top of the report.
The other flavor of headers includes page numbers and other information that would print outside the margins of your worksheet. These types of headers and footers are entered on the Header/Footer tab of the Page Setup dialog.
2. Use the dropdowns to select a standard text or use the Custom button to enter your own text.
3. In the Custom Header or Footer area, you can enter any text plus a variety of standard fields to generate date, time, page number, file name, and so forth.
Copying a Worksheet
You may think you know how to copy a worksheet – but I'll bet that you don't. I guarantee this one will save you anywhere from five to 30 minutes, depending on the size of your worksheet (and how compulsive you are about your settings).
Here's the Situation
A worksheet contains more than data and formulas. There is page setup date; there are column widths, etc. When you copy cells from one worksheet to a new worksheet, you will copy the cell contents, but not the meta data (configuration information) about the sheet. There are several variations on the Move or Copy Sheet command.
Here's What to Do
1. If you need to make a copy of a worksheet and place it in the same workbook, simply hold down the Ctrl key while you drag the sheet to a new location.
2. The new worksheet is given a strange name such as Jan(2), Jan(3), etc. Double-click the sheet tab to rename the sheet.
3. To access the full range of options for the Move or Copy command, right -click the worksheet tab and choose "Move or Copy".
In the default settings for the Move or Copy dialog, Excel will move the worksheet to a new location in the current workbook. This is somewhat silly, since you can easily move a worksheet by simply dragging the tab to a new location.
Click and drag a worksheet tab to move the worksheet to a new area in the same workbook.
4. Choose the box for "Create a copy" in order to have Excel make an exact copy of the worksheet.
5. Finally, use the dropdown at the top. If you want to make a copy of a worksheet in a new workbook or copy it to another open workbook, you can choose this in the "To book" dropdown.
Using this command offers many advantages over copy and pasting cells. Excel will copy:
[??] Column widths and row heights
[??] Cell formatting
[??] Print Setup information such as headers and footers
[??] View Manager and Scenario Manager settings
Showing Numbers in Thousands
Here's the Situation
If you are analyzing sales for a $100 Million company, it does not make sense to show sales to the penny or dollar.
How can you display results in thousands or millions?
Here's What to Do
There are custom number formats to display results in thousands, but they are not found on the Formatting toolbar.
1. Select the range of cells containing the numeric data. Press Ctrl+1 (that is, Ctrl plus the number 1) to display the Format Cells dialog.
When you want to set up a custom format, it is best to choose a standard numeric format to get close. Use either the numeric or currency category and change the decimal places to zero.
2. Choose Custom from the bottom of the Category list. Notice that you are starting with a custom number format of "$#,##0". This is the format that Excel built for you to display currency with zero decimal places.
3. Add a comma at the end of the custom number format.
If you place a comma at the end of this custom number format, Excel will keep the original number, but divide it by one thousand when it displays it in the cell. In the Sample box below, you will see that $869,454 is now displayed as $869.
To make it clear that the numbers are in thousands, you could note this in the title of your report. Or, you can add an abbreviation to the custom number format. If your company uses "K" as the abbreviation for one thousand, then a custom number format of $#,##0,K is valid. However, to use "M" for the thousands abbreviation, you will have to put the M in quotes: $#,##0,"M"
Every comma at the end of the custom number format will cause the displayed number to be divided by another thousand. Thus, to display numbers in millions, put two commas at the end of the format. To display numbers rounded to the nearest hundred thousand, it is valid to combine a decimal place followed by two commas. The following table shows the effect of various custom formats on the same number.CHAPTER 3
Quickly Seeing Sum or Average
Here's the Situation
Your boss calls you on the phone and starts asking you questions. He asks questions like "How much did Shell Canada buy last year?", "What was the largest order from Nortel?", and "What was the average order from Kroger?". (I always start to sweat at this point.) You think to yourself, "Does he really think that I should know how much Shell Canada bought last year off the top of my head?!".
Here's What to Do
You can answer these questions quickly without creating any formulas in Excel. In fact, if you can click the mouse quietly, your boss might actually believe that you do know this stuff off the top of your head!
1. Select a single cell in the Customer column and click the AZ button in the Standard toolbar to sort the data by Customer.
Be sure to select just a single cell. Selecting more than one will sort just the selected cells, which can really mess up your data. If this happens, press the Undo button or Ctrl+Z before you do anything else!
2. Scroll until you find the section of records for Shell Canada. Highlight the cells that contain revenue for those records.
Some installations of Excel have the Status Bar turned off. You might have to use View – Status Bar to turn the Status Bar back on.
3. Now – look in the Status Bar at the bottom of the screen.
The Status Bar generally says "Ready" on the left side. In the right half of the status bar, you will see the words "Sum=71651". This represents the sum of the selected cells. So – the answer to the boss' first question is that Shell Canada purchased $71,651 from the company last year.
4. Scroll up and highlight the revenue for Nortel. In this case, there are 28 cells in the selection. The status bar reveals that Nortel purchased $406K last year, but your boss wants to know the largest order from Nortel.
5. Right click on the Sum field and choose Max.
The status bar reveals that the largest order from Nortel was $25,350.
6. Next the boss wants to know the average order from Kroger. Highlight the cells for Kroger revenue. Right-click the status bar figure and choose Average.
If one of the cells in your selection contains an error such as #VALUE! or #N/A, the QuickSum feature will turn off.
The operation of the QuickSum functions for Count and CountNums are different than the functions in Excel. Usually, the COUNT function counts only numeric cells. In the QuickSum, Count will count all non-blank cells. This is equivalent to using =COUNTA() in the spreadsheet.
Here's the Situation
You have sales data for three dozen of your best customers. You would like to see totals by customer. You might be tempted to insert blank rows between each customer and use the AutoSum button to add totals. This might work for a few customers, but it will take too long for a large number of customers.
Here's What to Do
Instead, use automatic subtotals.
1. Select a single cell in the customer column. Press AZ in the Standard toolbar to ensure that your data is sorted by Customer. From the Data menu, select Subtotals.
The Subtotals command is excellent, but it has some quirky defaults. Excel always assumes that you want to subtotal by the left-most column in your dataset. It also assumes that you want to apply the subtotals to the right-most column in your dataset. If that column contains text, then the Subtotals dialog will change the Function selection from Sum to Count.
In the current case, you want to subtotal each Customer.
2. Change the top dropdown from Region to Customer. In this case, the function of Sum is correct. In the Subtotal list, keep Profit checked, and check COGS and Revenue.
If you have more than three columns to subtotal, you would need to scroll up to select other fields. If you need each customer to print on a separate page, select the "Page break between groups" option.
3. When you click OK, Excel will automatically insert a new row between each customer. The Subtotals command will insert a special function called Subtotal. The Subtotal function will sum rows in the range, but it is smart enough to ignore other subtotals in the range.
Notice that Excel has added three small buttons (1, 2, and 3) to the left of the row numbers. If you press the 2 button, Excel will show you only the subtotal rows.
Pressing button 2 is a great way to print a summary report. Pressing button 1 shows you only the grand totals. Pressing button 3 shows all of the rows.
Since the report is showing only the subtotals, it would be cool if you could copy just those subtotals to a new workbook. However, if you select the cells, copy and paste, you will see that all of the hidden detail rows were copied as well. Instead, use this method.
Excerpted from Excel for Marketing Managers by Ivana Taylor, Bill Jelen, Linda DeLonais. Copyright © 2006 Ivana Taylor and Bill Jelen. Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.