Excel is an excellent tool for generating different kinds of tables and charts. In terms of tables, Excel offers three different kinds which are Excel tables, Pivot tables and Data tables. We take a closer look at these in sections 3.2–3.4. In section 3.1 we give a presentation of some of the different charts and graphs Excel offers.
Excel includes a module for drawing charts. We can choose between different graphs and charts in the group Charts under the tab Insert as illustrated in figure 3.1. The most commonly used are Column, Line and Scatter.
A column chart can for instance be used to display the rain for months through a year. Then the height of the columns will indicate the amount of rain in different months. In the cells A3:C9 in figure 3.1 we find (fictitious) amounts of rain in Oslo and Glasgow for the months January–June. If we want a chart for the rain in Oslo, we can mark the cells A3:B9 and choose the tab Insert and Column under the group Charts. If we choose Clustered Column (the first one under 2-D Column) in the menu that appears, we get the chart in figure 3.1. If we alternatively mark the cells A3:B9 and press F11, we get the same chart drawn in a separate spreadsheet.
Figure 3.1 Column chart.
If we click on an element in the chart, a menu appears where we can choose Format. If we double-click on an element, the dialogue box for formatting appears automatically. In figure 3.2 we have modified the chart from figure 3.1 by adding data labels to the columns, deleting the grid lines, and changing the background and column colours.
Figure 3.2 Column chart.
If we click somewhere in a chart, the tab Chart Tools, and the context tabs Design, Layout and Format appear on the Ribbon. Under these tabs we can choose different layouts, switch row and column in the data set, add trend lines, etc.
If we want a chart showing the rain in both Oslo and Glasgow, we can mark the cells A3:C9 and repeat the procedure. This will result in the diagram in figure 3.3, which contains two series.
Figure 3.3 Column chart with two series.
If we want to present data for a time series, such as energy prices during a year, a line chart can be a good alternative.
A common problem is when we want to show how one variable varies with another. In such cases a scatter chart is the best alternative.
Figure 3.4 shows variable unit cost (vuc) for a product at different production volumes (in units) in the cells B7:B13. In the cells C7:C13 total unit costs (tuc) have been calculated as vuc + fixed costs/units. To draw curves for vuc and tuc (as functions of units), we can start by marking the cells A6:C13 and then choose the tab Insert, and Scatter under the group Charts. Then a menu appears where we can choose for instance Scatter with Smooth Lines and Markers. This will result in a chart similar to the one in figure 3.4. (The chart in figure 3.4 has been modified.)
Figure 3.4 Unit costs diagram.
If we just choose Insert, Scatter, etc. without marking the cells with the data, a blank chart area appears together with some new tabs on the ribbon. If we choose the context tab Design and Select Data in the group Data, a dialogue box appear where we click Add. (We get the same dialogue box if we click with the right mouse button in the chart area, and choose Select Data in the menu that appears.) Then the dialogue box in figure 3.5 appears. Here we state the cells A7:A13 (units) as Series X values and the cells B7:B13 (vuc) as Series Y values.
Figure 3.5 The Edit Series dialogue box.
Clicking OK brings us back to the previous dialogue box. Then we can repeat the procedure and include the curve for tuc in the chart.
Sparklines
Excel 2010 offers sparkline graphics. A sparkline is a small chart that fills one cell in a spreadsheet. Such small charts can be very useful if for instance we want to illustrate a trend in a set of data.
Suppose we have registered prices for the stocks A and B in the cells B5:G6 as illustrated in figure 3.7. If we want simplified line charts for these data, we choose the tab Insert and Line in the group Sparklines so that the dialogue box in figure 3.6 appears. As Data Range we choose the cells B5:G6, and as Location Range the cells H5:H6. When we click OK we get the line charts in the cells H5 and H6.
Figure 3.6 Dialogue box for Sparklines.
If we click in a cell with a sparkline, we can edit the chart by choosing the tabs Sparkline tools/Design that appear on the ribbon. In figure 3.7 we have ticked High Point and Low Point in the group Show, so that the maximum and minimum values are marked in the chart.
Figure 3.7 Sparklines.
The sparklines in figure 3.7 are backgrounds in the cells. It is still possible to write text, values, formulas, etc. in these cells. If we click on one of these cells and press delete, the text or values disappear, but the sparkline remains. To remove a sparkline, we need to click with the right mouse button in the actual cell, and choose Sparklines and Clear Selected Sparklines in the appearing menus.
If we just want to do some simple calculations on a data set, such as calculating sums and averages, the Excel table tool can be a good alternative.
Assume we have a list of customers with sales for different months as shown in the cells A3:C15 to the left in figure 3.8. If we click on one of these cells or mark this area, we can generate an Excel table from these data by choosing the tab Insert and then Table in the group Tables. First the dialogue box to the left in figure 3.8 appears where we click OK. Then we get the table to the right (in the same figure). The same table can also be generated by choosing the tab Home and Format as Table in the group Styles. Then we get a menu with different table styles to choose among. By clicking on one of the arrows in the table headers (cells A3:C3), we get a menu which offers sorting and filtering of the data in the table. If we click somewhere in the table, we can also add a total row to it, by choosing the tabs TableTools/Design and then ticking Total Row in the group Table Style Options. This operation can also be done by adding an appropriate function (such as SUM) at the bottom of the table.
If we click on one of the cells in the Total row, an arrow appears to the right of the cell. By clicking on this arrow we get a menu that offers different calculations on the column above the cell, such as sum, average, and maximum.
Figure 3.8 Excel table.
If we want to add data to an Excel table (as a row or a column), we can write the data in an adjacent row or column, and press enter. The data will then be added to the table. We can also expand a table by clicking and dragging on the right corner at the bottom of the table.
If we want our data back on the original form, we click somewhere in the table and choose the tabs TableTools/Design and then Convert to Range in the group Tools. In the dialogue box that pops up, we click OK. Then the data appears in its original form.
3.3 Pivot tables
Under the tab Insert in the group Tables, we find the choice Pivot Table which can be extremely useful if we want to extract and present information from a large amount of data. The term Pivot indicates that we can rotate a large amount of complex data around an imaginary axis, and look at the data from the angle we desire, i.e. extract the information we want.
We will demonstrate pivot tables by looking at the sales figures from a kiosk illustrated in figure 3.9 (the rows 7–44 are hidden). Each line in the spreadsheet contains information about a sale with customer, product, number of units, total sales, and sales assistant.
To generate a pivot table we start by marking the cells with the data and the headings (A4:E62). Then we choose the tab Insert and Pivot Table in the group Tables so that the dialogue box in figure 3.9 appears with A4:E62 as Table/Range.
Figure 3.9 Dialogue box for Pivot tables.
If we choose a cell such as G46 as Location and click OK, a Pivot Table Field List appears in the right part of the spreadsheet as shown in figure 3.10. We can use this field list and define a pivot table by clicking and dragging headings (Customer, Product, etc.) from the upper text box of the field list down to the lower text boxes.
Assume we want a pivot table that shows the total amount each customer has spent on each product. Then we drag Customer to the Row Labels text box, Product to the Column Labels text box, and Total sales to the Values text box, so that the pivot table in figure 3.10 appears. The field list disappears when we click in a cell outside the pivot table. Notice the tab PivotTable Tools with the context tabs Options and Design that appears on the ribbon when we click on a pivot table. Under these tabs we find many different tools for pivot tables.
If we also want to see the number of units each customer has bought of each product, we can drag Number to the Row Labels text box, to get the pivot table in figure 3.11. Notice that Customer is displayed over Number in the text box. If we put them in reverse order (by clicking and dragging), the pivot table will look like figure 3.12, showing the customers that have bought 1 unit of the different products, 2 units of the different products, and so on.
Editing pivot tables
From figure 3.10 we see that sums of columns and rows are automatically added as default to the pivot table. If we choose PivotTable Tools/Design and Grand Totals in the group Layout, a menu appears where we can choose the results we want. If we want to replace the sums with other results, we can press the right mouse button while standing in the pivot table. Then a menu appears where we can choose Summarize Values By, and for instance Average in the next menu.
Figure 3.10 Pivot table showing the total amount each customer has spent on each product.
Figure 3.11 Pivot table.
Figure 3.12 Pivot table.
In the menu that appears when we press the right mouse button (while standing in the pivot table), we can also choose Show Values As. The new menu that appears offers different choices such as % of Grand Total and % of Column Total. If we choose % of Grand Total, the pivot table will be as illustrated in figure 3.14. If we compare figure 3.10 with figure 3.14, we see that the content of cell M48 is changed from 17 to 17/4924 = 0.35 per cent.
If we press the right mouse button (while standing in the pivot table) and choose Value Field Settings, the dialogue box in figure 3.13 appears. Under the tab Summarize Values By, we can choose among different calculations such as sum, average and max. Under the tab Show Values As we can choose to rank values, show each value as a percentage of its row sum, etc. In figure 3.13 we choose to show all the values in the pivot table as percentages of the total sum. The resulting pivot table is shown in figure 3.14.
Figure 3.13 The Value Field Settings dialogue box.
Figure 3.14 Pivot table.
Sorting and filtering
Sorting and filtering of the values in a pivot table can be done in different ways. Let’s take a look again at figure 3.10. To the right of the headings Row Labels and Column Labels we find buttons with arrows. If we click on one of these, a menu that offers different sorting alternatives appears. The same menu shows up if we click on the arrow that appears when we move the mouse pointer over one of the fields (Customer, Product, etc.) in the upper part of the field list.
If we click on the button to the right of Row Labels, and choose Value Filters and Greater than in the menu, the dialogue box in figure 3.15 appears. If we write in 500 and click OK, the pivot table will look like figure 3.16 showing the customers that have been shopping for more than 500.
Figure 3.15 The Value Filter dialogue box.
Figure 3.16 Filtering.
In the lower part of the above-mentioned menu, we can tick the names (row labels) we want in the pivot table. If we tick Eve, Franck and Holly, we get a pivot table like the one in figure 3.16.
Excel 2010 offers a new method called “slicers” for filtering pivot tables. If we click on the pivot table in figure 3.10, choose the tabs PivotTable Tools and Options, and Insert Slicer in the group Sort and Filter, the dialogue box in figure 3.17 appears. If we tick Customer and Product, and click OK, the two slicers in the upper part of figure 3.18 appear. In a slicer we can click on the different subjects that should be listed in the pivot table. If we click on Sweets in the slicer Product, we get the pivot table in the lower part of figure 3.18 showing the customers that have bought sweets. We may also choose more than one alternative from a slicer by holding down the Ctrl-button while clicking on the alternatives.
Figure 3.17 The Slicer dialogue box.
Figure 3.18 Pivot table with slicers.
In the field list in figure 3.10 we find a box called Report Filter which can be used to present filtered data. If we, for instance, want a report for each sales assistant, we can drag the field Sales assistant down to the box Report Filter. Then we choose the tab PivotTable Tools/Options and click the arrow to the right of Options in the group PivotTable. In the menu that appears we choose Show Report Filter Pages, and then OK in the appearing dialogue box. Then we get a new spreadsheet with a pivot table for each sales assistant. Figure 3.19 shows the pivot table for sales assistant Olson.
Figure 3.19 Report for sales assistant Olson.
Pivot charts
It is relatively simple to draw a chart from the data in a pivot table. To the left in figure 3.20 we have changed the table from figure 3.10 into a table with products as row labels and total amount as values. If we click somewhere in the table and choose the tab PivotTable Tools/Options, and Pivot Chart in the group Tools, the dialogue box Insert Chart appears. If we choose Clustered Column, we get the chart in figure 3.20. Another possibility is to choose the tab Insert and then the desired type of chart in the group Charts while standing in the pivot table.
Figure 3.20 Pivot table with pivot chart.
Like the pivot table, the pivot chart is also dynamic. If we click on the chart, a field list similar to the one in figure 3.10 appears. Figure 3.21 shows that in this field list the headings Row Labels and Column Labels are replaced with Axis Fields and Legend Fields respectively. If we drag the field Customer down to the box Legend Fields, the chart changes into the one in figure 3.21, showing how much each customer spent on different products.
If we click with the right mouse button on one of the bars in the chart in figure 3.21, a menu appears where we can choose Sort and Filter. If we tick Adam and Grace in the next menu, we get the chart in figure 3.22 showing how much Adam and Grace have spent on different products.
Figure 3.21 Pivot table with pivot chart.
Figure 3.22 Pivot chart.
Data tables can be very helpful if we need to perform systematic calculations repeatedly. Depending on the problem, both one- and two-dimensional data tables can be useful. Let’s demonstrate such tables by looking at two sensitivity analyses where we study how contribution margin and profit change when price per unit and number of units sold change for a manufacturer.
A manufacturer produces a product with a variable cost per unit of $20 and a fixed cost per year of $100,000. We need a summary that shows the profit at different combinations of price per unit and number of units sold per year. This can easily be done with a two-dimensional data table.
The spreadsheet in figure 3.23 calculates the profit in cell B13 based on the number of units sold per year, price per unit, variable cost per unit, and fixed cost per year (in the cells B3:B6).
Before we generate the data table we write appropriate values for price per unit in the cells F3:P3 and for units sold per year in the cells E4:E16. What we want is to put the content of the cells E4:E16 into cell B3 and the content of the cells F3:P3 into cell B4, calculate the different profits and then put these values into the data table.
Cell E3 in the upper left corner of the data table must contain the formula used to calculate the values in the data table. In this case the formula is “=B13” which is the address to the cell that calculates the profit.
When we have written the desired numbers in the cells F3:P3 and E4:E16, and the correct formula in cell E3, we mark the area E3:P16. Then we choose the tab Data and then What-If Analysis and Data Table so that the dialogue box in figure 3.23 appears. As Row input cell we choose cell B4 which will contain price per unit. As Column input cell we choose cell B3 which will contain units sold per year. When we click OK, the prices in the cells F3:P3 are sent to the input cell B4, and the numbers of units sold per year in the cells E4:E16 are sent to the input cell B3. Then the data table is filled with calculated profits from cell B13 as illustrated in figure 3.24.
Figure 3.23 Creating a two-dimensional data table.
Figure 3.24 Data table.
The value that appears in the formula cell (E3) is of no interest. We can make this value disappear by choosing identical colours as font colour and fill colour.
Example 3.2
Let’s study the same manufacturer as we did in example 3.1. We assume that the price per unit is $50 and want to make a data table showing contribution margins and profits at different numbers of units sold per year.
In this case a one-dimensional data table with different numbers of units sold in the column to the left of the table is appropriate. See figure 3.25. The table must include one column for calculated contribution margins and one column for calculated profits. At the top of the column for contribution margins we include the formula “=B11”, which is the cell address for the calculated contribution margin. At the top of the column for calculated profits we include the formula “=B13”, which is the cell address for the calculated profit.
Figure 3.25 Creating a one-dimensional data table.
To generate the data table we mark the area E3:G19 and choose Data, What-If Analysis and Data Table (on the ribbon) so that the dialogue box in figure 3.25 appears. Since we want to generate a one-dimensional table, we leave the window Row input cell empty. As Column input cell we choose cell B3, which will contain units sold per year. When we click OK, the data table in figure 3.26 is generated.
Figure 3.26 One-dimensional data table.
For some problems the input cells must be included directly in the data table’s formula. Example 3.3 illustrates this.
Generate a multiplication table for the numbers 1–10.
In figure 3.27 we have written the numbers 1–10 in one row and in one column to form a table. When generating the desired data table we choose two arbitrary empty cells (outside the table) such as C2 and A4 as input cells. In this case the formula in cell B3 must be “=C2*A4” so that the numbers for the row C3:L3 and the column B4:B13 are sent to the formula in cell B3 and the results are written in the data table.
Figure 3.27 Creating a two-dimensional data table.
The final data table is illustrated in figure 3.28.
Figure 3.28 Multiplication table.
3-1. The following overview shows the exam results for 36 students. Make a spreadsheet model that counts the number of A, B, and so on. Show these results graphically.
3-2. Find the appropriate functions in Excel and calculate the average, the median, the mode, variance and standard deviation. Make a frequency table (showing how many times 512 appears, 513 appears, etc.). Draw a bar chart from the data in the frequency table.
3-3. The following is an overview of stock prices for a company at the end of each month.
Draw a bar chart showing the stock price for each month.
3-4. Draw a binomial distribution with p = 0.35 and n = 9.
3-5. Draw a normal distribution with µ = 57 and σ = 13.
3-6. The present value (i.e. the total value measured in today’s value of money) of receiving $1 at the end of each year for the next n years when the interest is r can be calculated as:
Generate a data table with values for An,r where n varies from 1 to 30 years and r varies from 1 per cent to 20 per cent.