EXCEL: Lookups, Charts, Statistics, and Pivot Tables

Having reviewed basic functions, cell references, and date and time functions, we now dive into some of the more advanced features of Microsoft Excel. We present methods to solve classic problems in finance, sales reports, shipping costs, and statistics.

These functions are important to business, students, and those who are just want to learn more.

VLOOKUP and HLOOKUP

Here is an example to illustrate vertical lookup (VLOOKUP) and horizontal lookup (HLOOKUP) functions. These functions are used to translate a number or other value into something which is understandable. For example, you can use VLOOKUP to take a part number and return the item description.

To investigate this, let’s go back to our “Decision Maker” spreadsheet in Part 4, where Jane is trying to decide what to wear to school. She is no longer interested in what she wears, since she has landed a new boyfriend, so she will now wear random outfits and shoes.

In Jane’s spreadsheet, she lists outfits in vertical columns and shoes, horizontal columns.

She opens the spreadsheet and the function RANDBETWEEN(1,3) generates a number between or equal to 1 and 3 corresponding to the 3 types of outfits she can wear.

She uses the function RANDBETWEEN(1,5) to pick among five types of shoes.

Since Jane cannot wear a number we needs to convert this to a name, so we use lookup functions.

We use the VLOOKUP function to translate the outfit number to outfit name. HLOOKUP translates from shoe number to the various types of shoes in the row.

The spreadsheet works like this for outfits:

Excel picks a random number from 1 to 3, since she has three outfit options.

Next the formula translates the number to text using =VLOOKUP(B11,A2:B4,2) which uses random number the value from B11 to look in the range A2:B4. It then gives the result (C11) from the data listed in the second column.

We use the same technique to pick shoes, except this time we use VOOKUP instead of HLOOKUP.

Example: Basic Statistics

Almost everyone knows one formula from statistics – average – but there is another statistic that is important for business: standard deviation.

For example, many a person who has gone to college has agonized over their SAT score. They might want to know how they rank compared to other students. Universities want to know this too because many universities, especially prestigious ones, turn down students with low SAT scores.

So how would we, or a university measure and interpret SAT scores? Below are SAT scores for 5 students ranging from 1,870 to 2,230.

The important numbers to understand are:

Average – Average is also referred to as the “mean”.

Standard Deviation (STD or σ) – This number shows how widely dispersed a set of numbers are. If the standard deviation is large, then the numbers are far apart and if it is zero, all the numbers are the same. You could say that the standard deviation is the average difference between the average value and the observed value, i.e. 1,998 and each SAT score. Please note, it is common to abbreviate standard deviation using the Greek symbol sigma “σ”.

Percentile Rank – When a student receives a high score, they can brag that they are in the top 99% percentile or something like that. “Percentile rank” means the percentage of scores are lower than one particular score.

Standard deviation and probability are closely-linked. You can say that for each standard deviation, the probability or likelihood that that number is inside that number of standard deviations is:

STD	Percentage of scores	Range of SAT scores
1	68%	1,854-2,142
2	95%	1,711-2,285
3	99.73%	1,567-2,429
4	99.994%	1,424-2,572

As you can see, the chance that any SAT score is outside 3 STDs is practically zero, because 99.73% of the scores are within 3 STDs.

Now let’s look at the spreadsheet again and explain how it works.

Now we explain the formulas:

=AVERAGE(B2:B6)

The average of all the scores over the range B2:B6. Specifically, the sum of all the scores divided by the number of people who took the test.

=STDEV.P(B2:B6)

The standard deviation over the range B2:B6. The “.P” means STDEV.P is used over all the scores, i.e., the entire population and not just a subset.

=PERCENTRANK.EXC($B$2:$B$6,B2)

This calculates the cumulative percentage over the range B2:B6 based upon the SAT score, in this case B2. For example, 83% of the scores are below Walker’s score.

Graphing the Results

Putting the results in a graph makes it easier to understand the results, plus you can show it in a presentation to make your point more clearly.

Students are on the horizontal axis and their SAT scores are shown as a blue bar graph on a scale (vertical axis) from 1,600 to 2,300.

The percentile ranking is the right-hand vertical axis from 0% to 90%, and is represented by the grey line.

How to Create a Chart

Creating a chart is a topic unto itself, however we will explain briefly how the above chart was created.

First, select the range of cells to be in the chart. In this case A2 to C6 because we want the numbers as well as the student’s names.

From the “Insert” menu select “Charts” -> “Recommended Charts”:

The computer recommends a “Clustered-Column, Secondary Axis” chart. The “Secondary Axis” part means it draws two vertical axes. In this case, this chart is the one we want. We don’t have to do anything else.

You can use move the chart around and resize it until you have it as the size and in the position you want. Once you’re satisfied you can save the chart in the spreadsheet.

If you right-click the chart, then “Select Data”, it shows you what data is selected for the range.

The “Recommended Charts” feature usually bails you out from having to deal with such complicated details as determining what data to include, how to assign labels, and how to assign the left and right vertical axes.

In the “Select Data Source” dialog, click “score” under “Legend Entries (Series)” and press “Edit”, and change it to say “Score”.

Then change series 2 (“percentile”) to “Percentile”.

Return to your chart and click on the “Chart Title” and change it to “SAT Scores”. Now we have a complete chart. It has two horizontal axes: one for SAT score (blue) and one for cumulative percentage (orange).

Example: The Transportation Problem

The transportation problem is a classic example of a type of mathematics called “linear programming”. This lets you maximize or minimize a value subject to certain constraints. It has many applications to wide array of business problems, so it is useful to learn how it works.

Before we get started with this example we have to enable the “Excel Solver”.

Enable Solver Add-In

Select “File” -> “Options” -> “Add-ins”. At the bottom of the add-ins options, click the “Go” button next to “Manage: Excel Add-ins”.

On the resulting menu, click the check box to enable “Solver Add-in” and click “OK”.

Example: Calculate the Lowest iPad Shipping Costs

Suppose we are shipping iPads and we are trying to fill our distribution centers using the lowest transportation costs possible. We have an agreement with a trucking and airline company to ship iPads from Shanghai, Beijing, and Hong Kong to the distributions centers shown below.

The price to ship each iPad is the distance from the factory to the distribution center to the plant divided by 20,000 kilometers. For example, it is 8,024 km from Shanghai to Melbourne which is 8,024/20,000 or $.40 per iPad.

The question is how do we ship all these iPads from these three plants to these four destinations at the lowest possible cost?

As you can imagine, figuring out this could be very difficult without some formula and tool. In this case we have to ship 462,000 (F12) total iPads. The plants have a limited capacity of 500,250 (G12) units.

In the spreadsheet, so that you can see how it works, we have typed 1 into cell B10 meaning we want to ship 1 iPad from Shanghai to Melbourne. Since transportation costs along that route are $0.40 per iPad the total cost (B17) is $0.40.

The number was calculated using the function =SUMPRODUCT(costs,shipped) “costs” are the ranges B3:E5.

And “shipped” are the range B9:E11:

SUMPRODUCT multiplies “costs” times the range “shipped” (B14). That is called “matrix multiplication”.

In order for SUMPRODUCT to work properly, the two matrices – costs and shipped – have to be the same size. You can get around this limitation by making extra costs and shipping columns and rows with zero value so that the arrays are the same size and there is no impact on the total costs.

Using the Solver

If all we had to do was multiply the matrices “costs” times “shipped” that would not be too complicated, but we have to deal with constraints there as well.

We have to ship what each distribution center requires. We put that constant into the solver like this: $B$12:$E$12 >= $B$13:$E$13. This mean the sum of what is shipped, i.e., the totals in cells $B$12:$E$12, must be greater than or equal to what each distribution center requires ($B$13:$E$13).

We cannot ship more than we produce. We write that constraints like this: $F$9:$F$11 <= $G$9:$G$11. Put another way, what we ship from each plant $F$9:$F$11 cannot exceed (must be less than or equal to) the capacity of each plant: $G$9:$G$11.

Now go to the “Data” menu and press the “Solver” button. If the “Solver” button is not there, you need to enable the Solver add-in.

Type in the two constraints detailed earlier and select the “Shipments” range, which is the range of numbers that we want Excel to calculate. Also pick the default algorithm “Simplex LP” and indicate that we want to “minimize” the cell B15 (“total shipping costs”), where it says “Set Objective”.

Press “Solve” and Excel saves the results into the spreadsheet, which is what we want. You can also save this so you can play around with other scenarios.

If the computer says it cannot find a solution, then you have done something that is not logical, for example, you may have requested more iPads than the plants can produce.

Here Excel is saying that it found a solution. Press “OK” to keep the solution and return to the spreadsheet.

Example: Net Present Value

How does a company decide whether to invest in a new project? If the “net present value” (NPV) is positive, they will invest in it. This is a standard approach taken by most financial analysts.

For example, suppose the Codelco mining company wants to expand the Andinas copper mine. The standard approach to determine whether to move ahead with a project is to calculate the net present value. If the NPV is bigger than zero, then the project will be profitable given two inputs (1) time and (2) cost of capital.

In plain English, cost of capital means how much would that money would earn if they just left it in the bank. You use the cost of capital to discount cash values to present value, in other words $100 in 5 years might be $80 today.

In the first year, $45 million is set aside as capital to finance the project. The accountants have determine that their cost of capital is 6%.

As they start mining, the cash starts coming in as the company finds and sells the copper they produce. Obviously, the more they mine, the more money they make and their forecast shows their cash flow increasing until it reaches $9 million per year.

After 13 years, the NPV is $3,945,074 USD, so the project will be profitable. According to Financial analysts the “pay-back period” is 13 years.

Creating a Pivot Table

A “pivot table” is basically a report. We call them pivot tables because you can easily switch them one type of report to another without having to make an entire new report. So theypivot in place. Let’s show a basic example that teaches the basic concepts.

Example: Sales Reports

Sales people are very competitive (that’s part of being a salesman) so they naturally want to know how they fare against one another at the end of the quarter and end of the year, plus how much their commissions will be.

Suppose we have three sales people – Carlos, Fred, and Julie – all selling petroleum. Their sales in dollars per fiscal quarter for the year 2014 is shown in the spreadsheet below.

To generate these reports, we create a pivot table:

Select “Insert ->Pivot Table, it is on the left-hand side of the toolbar:

Select the all the rows and columns (including the salesman name) as shown below:

The pivot table dialog box appears to the right-hand side of the spreadsheet.

If we click all four fields in the pivot table dialog box (Quarter, Year, Sales, and Salesperson) Excel adds a report to the spreadsheet that makes no sense, but why?

As you can see, we have selected all four fields to add to the report. Excel’s default behavior is to group rows by text fields and then sum all the rest of the rows.

Here it gives us the sum of the year 2014 + 2014 + 2014 + 2014 = 24,168, which is nonsense. Also it gave is the sum of the quarters 1 + 2 + 3 + 4 = 10 * 3 =3 0. We don’t need this information so we deselect these fields to remove them from our pivot table.

“Sum of Sales” (total sales) is pertinent however, so we’ll fix that.

Example: Sales by Salesman

You can edit “Sum of Sales” that to say “Total Sales”, which is clearer. Also, you can format the cells as currency just like you would format any other cells. First click on “Sum of Sales” and select “Value Field Settings”.

On the resulting dialog, we change the name to “Total Sales” then click “Number Format” and change it to “Currency”.

You can then see your handiwork in the pivot table:

Example: Sales by Salesman and Quarter

Now let’s add subtotals for each quarter. To add subtotals just left-click on the “Quarter” field and hold and drag it to the “rows” section. You can see the result on the screenshot below:

While we’re at it, let’s remove the “Sum of Quarter” values. Simply click on the arrow and click “Remove Field”. In the screenshot, you can now see we’ve added the “Quarter” rows, which breaks down each salesperson’s sales by quarter.

With these skills fresh in mind, you can now create pivot tables from your own data!

Conclusion

Wrapping up, we have shown you some of the features of Microsoft Excel’s formulas and functions that you can apply Microsoft Excel to your business, academic, or other needs.

As you have seen, Microsoft Excel is an enormous product with so many features that most people, even advanced users, do not know all of them. Some people might say that makes it complicated; we feel it’s more comprehensive.

Hopefully, by presenting you lots of real-life examples, we have demonstrated not only the functions available in Microsoft Excel but have taught you something about statistics, linear programming, creating charts, using random numbers, and other ideas that you can now adopt and use in your school or where you work.

Remember, if you want to go back and take the class again, you can start fresh with Lesson 1!

Why Do You Need Formulas and Functions?

This How-To Geek School class is intended for people who use Excel, or try to use Excel but are intimidated by the concept of formulas and functions. Its objective is to make you an Excel pro or at least, a competent amateur, in just a few lessons.

Note: for the uninitiated, this is the very first lesson in the first series for How-To Geek School. We will be running a new lesson every single weekday, Monday through Friday, from now until we’ve covered every subject. So stay tuned for more, and we’ll be making a formal announcement about how it works next week.

What is Excel and Why Do We Need It?

If you are an office administrator, you probably use Excel. Ditto, if you are a salesman. If you are a financial analyst, of course, you need Excel. For these and many other professions, working with spreadsheets is an essential tool in the daily routine.

People who are math wizards will probably immediately take to Excel and use it like a natural extension of their computational processes. Most of the rest of us … probably need some help in that department.

If you did not pay attention in high school algebra class, don’t fret! All you need to do is learn some basic knowledge of formulas and functions from this How-To Geek School series and apply them to your work.

If you don’t know whether or not you need Microsoft Excel, then you probably have not been shown what it can do. Once you do know, then you can find ways to use it in your work to make complicated and tedious tasks easier.

Microsoft Excel is more than just a program to enter names and numbers. For example, if you simply want to list the names of students in your class then you can just make a Word document. Word is fine for writing essays and making lists but to do calculations, you need Excel’s formulas and functions.

A Brief Tour of the Excel Interface

Before we dive in, let’s just briefly review the Excel interface. You may already know all this stuff, but it never hurts to review.

When you open a blank Excel spreadsheet, it is known as a “workbook” or “book” and each spreadsheet in a book is a known as a “worksheet” or “sheet”.

The top of the app is dominated by the “ribbon” (grey), which sub-divided into “tabs” (red), which are then further sub-divided into “sections” (blue).

Below the ribbon is the “name box”, which lets you rename “cells” and then to the right of that is an area that allows you to create your all-important “formulas”. If you hover over each icon, you can see the purpose of each button, but what we’re most concerned with is the wide area where our formulas and functions will be displayed.

Finally, the worksheet is arranged in “columns” (denoted by letters) and “rows” (indicated by numbers). Each location is a “cell” and a group of cells is known as a “range”. If you have multiple sheets in a book, you can quickly flip through them using the tabs seen below the sheet. Below that, in the grey status bar, you can adjust your page views and zoom level.

That’s it, pretty simple, and just a short overview of some of the terminology you’ll encounter in this HTG School series.

What is a Function?

A function is a calculation or operation that returns a result. The inputs in a function are called “arguments”.

All functions begin with an equals sign [=]. That way Excel knows not to treat the arguments as text. For example, =AVERAGE(2,4) is a function but AVERAGE(2,4) is just a string of text. Without an equals sign, Excel will not calculate a result. The arguments in this function are 2 and 4.

Note, Excel uses upper-case letters to list functions, but you can use lower or upper-case letters when you write them.

In Excel, the “Function Library” can be found on the “Formulas” tab.

There are 13 categories of functions, some of which are:

• Mathematical: AVERAGE() – calculates the average of a series of numbers.
• Date and time: DATEVALUE() – converts a string of text like “30 November 2013” to a number so that you can use this number in other date and time functions. You cannot do math with dates unless you convert them to numbers first. We explain this in detail in Lesson 4.
• Text: LEN() – returns the length of a string. For example =LEN(“Excel”) is 5.
• Logical: IF() – the IF() function is written like =IF(<test>, then A, else B). So, if “test” is true, then the result is A; if “test” is not true, then B.
• Lookup and Reference: These are needed to lookup values elsewhere in the spreadsheet. For example, VLOOKUP looks in a table of values to find one cell.

How could you use this last one? Well, to get the day of the week in text from a date function. You can use VLOOKUP to scan a table to turn this number into something easier to understand, like “Wednesday”.

There are also special functions for financial, engineering, and statistics which are listed separately on the “More Functions” menu.

What is a Formula?

A formula is combination of “operators”, “operands”, and “functions”.

For example, the function =SUM adds a list of numbers (it is so commonly used, that is listed on the first menu in Excel, abbreviated by the Greek letter Sigma (Σ), which is the notation that mathematicians use to sum a series).

You use a formula like doing a calculation by hand. For example, you could put your family budget into a formula like this:

Remaining cash = (4 * weekly salary) – mortgage – food – utilities

The operators are multiply [*] and subtract [-]. The operands are the values “weekly salary”, “mortgage”, “food”, and “utilities”. The result is “remaining cash”.

Names and Addresses

The values for “food” and the other operands are names that you define in Excel. Without a “name”, you would have to use the “address”.

The address of a cell is written using row-column notation. The rows are given numbers and the columns, letters. The first cell in the spreadsheet is A1. When you have reached the end of the alphabet, the rows are numbered AA, AB, BA, BB, and so forth.

Formulas can be more complicated than the family-budget example. In high school you learned that that the area of a circle is the radius times pi squared or πr2.

In Excel, you can write this using the formula =PI() * radius ^ 2.

Here, PI() is the function that returns the number 3.14 and “radius” is a “name” we have given to a cell that contains the radius; the “operators” are the exponent (^) and multiplier (*).

Order and Precedence

Parentheses are used to indicate the order and precedence in calculations.

The area of a circle can also we written πrr but not (πr)2, so you need to understand order and precedence to get right answer. Exponents are evaluated before multiplication, so parentheses are not needed in this case. The function squares the radius first then multiplies that by Pi.

If you wanted to eliminate any possible doubt you could be explicit and write =Pi * (radius ^2).

We will explore order and precedence further in Lesson 2.

Coming up Next …

For the remainder of this series, we will concentrate on a four key areas:

Lesson 2: Defining and Creating Formulas

To get started, the first item to explore is how to create formulas and understand the basic rules:

• How to create a formula
• Calculation operators
• Order and precedence
• Demonstrate how a function is used within a formula

Lesson 3: Cell Reference and Formatting

In this lesson we discuss the three types of cell reference (relative, absolute, and mixed), formatting, how to move or copy and formula, and circular references.

Suppose you have a formula in cell B1 that says =A1*2. If you copy that formula to cell B2 (one cell to the right), then the cell reference changes too.

The new formula says B1*2. You can use the dollar sign ($) to anchor the cell reference so that copying it does not change where it points (don’t worry, we cover this in a bit).

Lesson 4: Useful Functions You Should Get to Know

This lesson explains some of most commonly-used functions and how we can apply them in useful calculations and how to “nest” functions.

To nest a function means put one function inside another. For example, if you want to know what day today is, write =WEEKDAY(NOW()).

NOW() gives today’s date and WEEKDAY() turns it into a number from 1 to 7. NOW() is “nested” inside WEEKDAY().

Lesson 5: Advanced Functions: If, Nested-If, and Lookups

If you want to create complex models or algorithms, you will need to know how to combine logic and lookup functions including IF and nested IF statements and lookups including VLOOKUP and HLOOKUP.

An example of lookup function would be =COLUMN(). This gives the column number of a cell reference, e.g., A=COLUMN(A1).

We’ll also briefly cover pivot tables. A pivot table is used to extract meaning from data. For example you could take a list of sales and group them by region or sales person to give you the totals for each.

Steganography and Arachniography

Steganography refers to hiding encrypted data or information inside of a common or completely innocent looking file such as an image, audio, or video file.

For all intents and purposes, most people will never be aware that the file is more than it appears to be, thus providing a nice hiding place in plain sight.

If someone does become aware of the ‘extra data’ in a file, it can be identified as encrypted data or information, but will be undecipherable without the decryption key.

An Arachniography is the web-based equivalent of a bibliography. Taking things one step further, an annotated Arachniography is the web-based version of an annotated bibliography.

Andrew J. Butrica of NASA was the person responsible for coining the term during his work on the history of NASA’s X-33 project.

4 Hidden Window Management Tricks on the Windows Desktop

Windows has quite a few features for autoamtically arranging windows, placing them side by side or tiling them on your screen. These features are a bit hidden, so you may not have noticed them.

We used Windows 7 here, but all of these tricks also work on Windows 8 except the ones that require the Task Manager. Many of the tricks also work with earlier versions of Windows.

Aero Snap for Side-by-Side Windows

RELATED ARTICLE

Aero Isn’t Gone in Windows 8: 6 Aero Features You Can Still Use
Many people think Aero is completely gone in Windows 8, but this isn’t true. Microsoft hasn’t helped matters by saying... [Read Article]

Aero Snap is extremely useful. It was introduced with Windows 7, but it’s also available on Windows 8. Microsoft says they got rid of Aero in Windows 8, but Snap is one of the Aero features still available on Windows 8.

The Snap feature makes a window take up half of your screen, making it easy to arrange two windows side by side without manually resizing and moving them around. To use Aero Snap, hold the Windows key and press the left or right arrow keys. The current window will be resized and placed at the left or right side of the screen.

You can also click a window title bar, hold down the mouse button, and drag the window’s title bar to the left or right edge of the screen. You’ll see a preview of the shape the window will become. Drop the window on the edge of the screen and it will be automatically resized to take up the appropriate side of the screen.

Maximizing, Restoring, and Minimizing Windows

You can maximize a window by dragging and dropping its title bar, too. Just drag and drop it to the top edge of the screen. . You’ll see a preview of the shape the window will become. Release your mouse button and the window will take up the entire screen. When you grab the title bar with your mouse and drag it away from the top of the screen, the window will be restored to its previous size.

With keyboard shortcuts, you can press Windows Key + Up arrow to maximize a window or press Windows Key + down arrow to restore a maximized window. Press Windows Key + down arrow again to minimize a window.

Cascade, Stack, or Tile Windows From the Taskbar

Right-click the taskbar and you’ll see three window management options — Cascade windows, Show windows stacked, and Show windows side by side. You’ll also see an “Undo” option if you right-click the taskbar after clicking one of these options.

The Cascade windows option will arrange your open windows in a “cascade,” allowing you to see all their title bars at once. This option isn’t the most practical.

The Show windows stacked option is a bit more interesting, as it allows you to arrange your windows stacked vertically on top of each other. This probably isn’t ideal for typical wide-screen displays, but it could be useful in some situations.

The Show windows side by side option is even more interesting, as it allows you to have Windows automatically arrange your open windows side-by-side with each other. It’s like Aero Snap, but it allows you to have three or more windows automatically arranged so they’re side by side — useful for multitasking on large, wide screen monitors.

Arrange Windows From the Task Manager

RELATED ARTICLE

8 Things You Didn’t Know You Could Do In Windows 7′s Task Manager
The Windows Task Manager is often used for troubleshooting – perhaps closing an application that isn’t working properly or monitoring... [Read Article]

You can also open the Task Manager from the taskbar’s right-click menu or press Ctrl+Shift+Escape to open it with a keyboard shortcut. The Task Manager has some integrated window management options, among its many other hidden features.

Note: Microsoft introduced a new Task Manager in Windows 8, and it doesn’t appear to have the window list pane or any window management features anymore. You can’t use the below tricks on Windows 8 — Microsoft probably removed them because they were used so rarely.

Click the Windows menu in the Task Manager and you can select Tile Horizontally or Tile Vertically. Interestingly, these options seem to arrange windows in a different way than the task bar options do, tiling them both horizontally and vertically in a way that allows as many windows as possible to appear on your screen at a time.

The Applications tab displays a list of all your open application windows, and it allows you to perform some more advanced tricks. Selecting multiple windows here allows you to arrange only specific windows automatically.

For example, let’s say we wanted to make three specific windows appear side-by-side. First, we’d select three windows by holding the Ctrl key and clicking each window’s name. Next, we’d right-click a selected window and select the Tile Vertically option. Windows will automatically arrange the three windows side-by-side.

---

Some of these features are more useful than others. The Snap feature is crucial for multitasking with multiple desktop application at a time. The Tile features aren’t used as frequently, but they could be very useful if you have a large monitor and need to arrange many windows on the screen so that they’re all visible at the same time.