Guerrilla Data Analysis Using Microsoft Excel

Holy Macro! Books

Contents

Reviewing the Basics

This book assumes that you have some basic knowledge of Excel. However, even seasoned Excel veterans miss some of the basics, so let's start with some fundamentals that will serve you well when you're working with data and things get hot.

Overview of Excel Formulas and Functions

There is a difference between a formula and a function. Formulas start with = and do not always use a function.

=3+2 is a formula without a function.

=B3+E3 is a formula without a function, and it adds the values in cells B3 and E3.

SUM is a function.

=SUM(3,2,11) is a formula with the SUM function, and would add 3+2+11.

=B1*MAX(A3:C20) is a formula that uses MAX to find the maximum value in the range A3:C20 and multiplies that result by the value in cell B1.

Therefore, functions are those features in Excel that are named like MAX, COUNTA, SUMIFS, NOW, TAN, KURT, CHAR, etc, and they are programmed to perform specific tasks.

There are hundreds of Excel functions, grouped in 11 categories. No one has reason to memorize every function. Instead, getting the most from Excel requires using resources like online forums, tutorials, books, and just asking people if they can help answer questions.

The following tables show some of the ways that Excel communicates with its users via formula notation and error messages.

Formula Notations

Here is a list of some of the notation that you'll see in formulas and what they mean.

Excel Error Notations

Here is a list of some of the errors that you'll see in Excel and what they mean.

Changing Formulas to Values

Here's a must-know: You should always get rid of formulas if they've done their job and are no longer needed.

Say that you've received a report that has the first and last names in separate cells and you need the full names in one cell.

To get the names put together, you can use the & symbol to concatenate them:

[ILLUSTRATION OMITTED]

In this figure, notice the formula =A2&" "&B2. You have to include the space between the first and last names. (To see for yourself, set up a similar spreadsheet and use the formula =A2&B2 instead.)

Once you have the names in one cell, you're done with columns A and B. But can you just delete them? Nope! If you delete the columns, you get a #REF! error because the formulas are looking for data that no longer exists:

[ILLUSTRATION OMITTED]

You need to undo the deletion with Ctrl+Z and get your data back.

To do this right, you have to get rid of the formulas in column C before you can delete the data in columns A and B. There are several ways to do this. Here's one:

1. Highlight the range that contains underlying formulas and right-click.

2. In the context menu that pops up, select Copy and then select Paste As Values, which is designated by the clipboard icon with the 123, as shown in the image below.

Now the formulas are gone. You have actual data in the Full Name column, and you're free to delete the First Name and Last Name columns.

If your keyboard has an Application key (which looks like a mouse cursor pointing to a dropdown menu and is often between the right Alt and Ctrl keys), you can convert to values by pressing Ctrl+C and then pressing and releasing the Application key and then pressing the V key.

You don't have an Application key?

Here's yet another way to paste as values after you've copied the original data:

1. On the Home tab, select the arrow at the bottom of the Clipboard group.

2. In the menu that appears, select Paste Special to open the Paste Special dialog. Notice in the figure below that Values is selected in the Paste section.

3. Click OK to get rid of the formulas in the Full Name column, leaving only the data.

Using Paste Special in Other Ways

The Paste Special options in Excel are worth getting to know. You've seen how to use Paste Special to change formulas to values. The following sections cover two more features: Transpose and, Multiply.

Transposing Columns and Rows

For one thing, you can use Paste Special to transpose columns and rows. Say that you have city names as column headers, as shown in the next figure, but you'd rather work with them as row headers.

Here's what you do:

1. Highlight the range and press Ctrl+C (or right-click and select Copy) to copy it.

2. Select the cell where you want the vertical data to start. The following figure shows the cursor in cell A4.

3. On the Home tab, select the arrow at the bottom of the Paste icon. From the menu that appears, select Paste Special to open the Paste Special dialog.

4. Select the Transpose check box, as shown in the following figure. Click OK.

Excel makes the cities into row headers instead of column headers — and it transposes the corresponding data into the right place, too.

Performing a Calculation on Every Cell in a Range

Say that you have a range of 48 cells that represent sales data, and you need to calculate a revenue share amount of 14% on each of the 48 cells. You can use Paste Special for this, too, and here's how you do it:

1. Enter 0.14 in an empty cell (G2 in the image below).

2. Press Ctrl+C (or right-click the cell and select Copy) to copy it.

3. Highlight the range of 48 cells of sales data, right-click, and select Paste Special.

4. In the Paste Special dialog, select the Multiply radio button and then click OK.

5. Excel did the multiplication for you but it's also changed the formatting to General.

With the cells still highlighted, Select Home | Accounting | click the dollar-sign to convert the General format to Accounting.

Using Helper Columns

Using helper columns isn't actually an Excel feature, but it's a really great strategy for dealing with complex formulas. When you need multiple calculations in a formula, you can break the actions into several steps or multiple formulas rather than try to write a massive formula all at once.

Say that you have a list of employees, along with the pay rate for each, the number of hours each employee has worked, and the number of overtime hours worked. The rule is that employees get time-and-a-half pay for those overtime hours (anything over 40 hours in a week). You need to be able to determine how much each employee should be paid — regular pay plus overtime.

To tackle this problem, you could calculate everyone's total pay all at once with a really sweet formula. Or you could simplify the process by using helper columns to do the calculation in several steps. The following figure shows what the helper columns solution might look like.

Using helper columns (and rows) gives you a lot of advantages:

• If you know a calculation is wrong, helper columns help you more easily spot where it went wrong. It's much harder to troubleshoot a monster formula with a labyrinth of parentheses.

• If you're planning to eventually create a single formula, you can use helper columns to think through all the necessary components, build them one at a time, and ensure that they work. When all the pieces do what they need to do, you can assemble the single formula and then delete the helper columns.

• If you have to modify the final calculation, it's easier to add, remove, or alter helper column components than to navigate an obstacle course of parentheses.

• If your data needs to keep a certain order that Excel won't recognize, you can use a helper column and have Excel sort by the helper column content. The figure below shows column E being used as a helper column to keep the timeline straight. You can sort by conference and by contact person and still get the rows sorted by year and by season.

Using helper columns also has a downside: To troubleshoot a complex task, you need to go to multiple locations. If the helper columns are scattered hither and yon in hidden cells or hidden sheets, troubleshooting can be really frustrating.

Let's get back to the earlier example of using helper columns to calculate total pay (regular pay plus overtime) for the employees of a company. The next figure shows the formulas used for calculating Ron's pay:

[ILLUSTRATION OMITTED]

You could combine these formulas into one single formula, like this:

=IF(C2<40,C2*B2,(40*B2)+((C2-40)*1.5*B2))

When you're sure your formula yields correct calculations, you can hide the helper column and save space in your work area. To do that, you simply highlight the columns you want to hide, right-click, and select Hide from the context menu, as shown below left. The result is on the right.

Using Relative, Absolute, and Mixed References

Which would you rather do?

• Spend a few seconds writing a formula once and dragging it over thousands of cells.

• Spend hours manually manipulating data or rewriting minor variations of the same formula.

If you have any hobbies or tend to avoid pain, then you probably chose the first option. The key to this option is understanding relative and absolute cell references. This understanding is critical for making your Excel life easier.

By default, when you build formulas, the cell references are relative. For example, this figure shows the formula =B2*C2 in cell D2:

[ILLUSTRATION OMITTED]

When the formula is copied down the column, Excel automatically modifies the formula to match each row:

[ILLUSTRATION OMITTED]

To show the absolute cell reference, you can calculate a discount that's keyed into cell G2. A dollar sign indicates an absolute reference that forces G2 to always be referenced when the formula is sent down the column:

[ILLUSTRATION OMITTED]

Notice that the formula in column F does what you wanted it to do — multiplies D2 by G2, D3 by G2, and so on:

[ILLUSTRATION OMITTED]

You can go a step further and generate a discount grid so that you can see the discounted amounts at various discount levels:

[ILLUSTRATION OMITTED]

To get this result, you write the following formula just one time. In cell B3 type:

=B$2*$A3

Next, you need to drag the formula down and right, so that this happens:

• B$2 changes to C$2, D$2, E$2, and F$2

• $A3 changes to $A3, $A4, $A5, $A6, and $A7

The following figure shows the result.

Linking Worksheets and Workbooks

Linking Between Worksheets

A lot of Excel veterans ask, "How do you link one sheet to another sheet?" If you don't know how to do this, put on your glasses, and let's go through it in several ways.

In the image below, your job is to fill in the range B2:F4 on the Overview sheet with the appropriate data.

Workbook details:

• Each warehouse has a worksheet.

• There is a company worksheet where you can find the managers' names.

• IS = In Stock; B = Back Ordered

Following are snapshots of each worksheet:

[ILLUSTRATION OMITTED]

Linking One Cell to Another on Different Worksheets

The first link you can make is a link between the warehouse types. To do so, follow these steps:

1. On the Overview sheet, in cell F2, type = so that Excel is ready to accept a formula.

2. Click on Hazel's worksheet sheet to move to it.

3. Select cell B1. In the formula bar, you can see that the formula is being built: =Hazel!B1.

4. Press Enter.

The ! tells you that the formula in F2 is linked to another worksheet — in this case, the worksheet named Hazel. Cells F3 and F4 will have the same format, as shown here:

[ILLUSTRATION OMITTED]

Those are basic links. No work is being done other than retrieval of whatever content is in another cell. The next section will show to have a cell do some work on another sheet and bring back the result.

Using a VLOOKUP with References to Another Worksheet

In cells E2:E4 you need each manager's name, and you can use VLOOKUP to retrieve the names. To begin, select cell E2.

You build the VLOOKUP as you will see in "Matching Reps and Rep IDs Using VLOOKUP" on page 64, except that here you go to the Company sheet and highlight the range A2:B14. This is the formula in E2:

=VLOOKUP(A2,Company!$A$2:$B$14,2,FALSE)

Again, the ! tells you that the range A2:B14 is not on this worksheet; it's on the Company worksheet.

The image below shows the formulas used to retrieve the Manager names in column E.

Inserting Table References Between Worksheets

Tables are discussed in detail at "Excel Tables: The Glue in Dynamic Spreadsheet Development" on page 99." Right now, let's do a preview of how tables link together.

The data sets for Hazel, Summer, and Cape are all in tables. You can see the names of the tables in the dropdown list to the left of the formula bar:

[ILLUSTRATION OMITTED]

As described in the Tables chapter, the formulas that are used with tables are different from traditional formulas.

To retrieve the number of back ordered items you're going to use COUNTIF

=COUNTIF(Hazel[Status],"B")

You don't see the ! this time because the formula in not referring to a worksheet; rather, it's referring to the Status data on the Hazel table — which the square brackets indicate.

Here is the completed grid:

[ILLUSTRATION OMITTED]

The next figure shows the underlying formulas.

Linking Workbooks

It is possible to link completely separate Excel files which are called workbooks. To do it, you use a formula like this one:

='C:\Users\Oz du Soleil\Examples\[Traffic Data.xlsx]Source Data'!$C$2

Here are the parts of the formula:

• Cell: C2

• Worksheet: Source Data

• Workbook: Traffic Data.xlsx

• Folder: Examples

• Path to folder: C:\Users\Oz du Soleil\

Linking data between cells, worksheets, and workbooks enables you to make data dynamic. If you change a value in one cell, it will update everything and everywhere that it has an impact. Linking can also help you keep your data better organized. If you don't link between worksheets, you must keep everything on one massive, cumbersome worksheet or use manual effort to bridge separate worksheets.

Developing Dynamic Spreadsheets

What is dynamic, as in dynamic formula or dynamic spreadsheet? Dynamic means that as your data changes, the changes are automatically reflected all throughout your spreadsheet. You don't have to manually make changes in multiple places if, for example, a single value impacts five calculations.

Dynamic spreadsheet development has at least three benefits:

• It automatically flexes with changes in the data.

• It maintains the integrity of calculations.

• Minimizing manual changes, also minimizes potential for error.

Let's look at an example. In the next figure, the formula =SUM(B2:B6) in D2 isn't dynamic. It gives the right answer when the entries are only Barcelona through San Diego. However, because the formula is static, it is limited to the range B2:B6. However, it doesn't work when someone comes along later and adds data for Toronto , Tokyo, Moscow, and Buenos Aires in B7:B10.

This problem with static formulas shows up as a concern, especially with Pivot Tables and graphs because it's harder to change the underlying data after you've built a Pivot Table or graph. So, get into the habit of asking yourself, "How dynamic does my spreadsheet need to be?"

You face very different tasks if your data changes a lot than if it rarely changes or if it is a one-time data set that will never change. If you'll make lots of additions, deletions, and updates, making a spreadsheet dynamic will really save you a lot of hassle. The next figure shows two versions of the same spreadsheet, one static and the other dynamic. If you know you'll need to add offices and employee counts with this data set, you can see that making the spreadsheet dynamic will save you some errors and problems later on. (See "Excel Tables: The Glue in Dynamic Spreadsheet Development" on page 99.)

Throughout this book, you'll learn about a number of tools and features that help you create dynamic spreadsheets, including:

• Pivot Charts

• Slicers

• The OFFSET function

• The INDEX function

• Data Models

• Tables

Conditional Formatting

Being able to quickly see interesting features of your data helps you plan, make decisions, and develop strategies. Conditional formatting makes this possible by allowing you to create rules and alerts in your spreadsheets.

The first way that I ever used conditional formatting was to set alerts that warned me about upcoming deadlines.

I would often worry that I'd forget about a deadline until it was too late. With conditional formatting I found it easy to plan the upcoming week based on the alerts that I would see each Monday when I opened the document. I used red cells to warn of deadlines that were due in the next 3 days, and yellow cells to warn of deadlines within the next 4 to 10 days. (This figure shows this, but if you're reading a printed version of this book, you see only black and white. Sorry about that!)

Using conditional formatting this way provided a straightforward visual cue: a lot of red meant a busy week. No need to count, sort or filter. It was time to get to work.

Next I'll show you how to implement this alert system for yourself