MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

Overview

• I have entered into a worksheet the number of units sold and total revenue for each salesperson, and can easily compute average price for each salesperson. I have created a nice format for my data. However, when I add new data, it is really annoying to have to copy the formula down and extend the format. Is there an easy way to have my format and formulas automatically copy down when new data is added?

• I have entered into my worksheet several years of natural gas prices, and have created a nice line chart displaying the monthly variation in prices. Can I set things up so that when I add new gas price data, my chart automatically updates?

• For each sales transaction, I have the salesperson, date, product, location, and size of the transaction. Can I easily summarize, for example, total lipstick sales in the east made by Jen or Colleen?

• How can I easily refer to portions of a table in other parts of my workbook?

• Do conditional formats automatically apply to new data added to a table?

When most of us use Microsoft Office Excel, we often enter new data. Then we manually update our formulas, formats, and charts. What a drag! But the Excel 2007 table capabilities can make this drudgery a thing of the past.

• I have entered into a worksheet the number of units sold and total revenue for each salesperson, and can easily compute average price for each salesperson. I have created a nice format for my data. However, when I add new data, it is really annoying to have to copy the formula down and extend the format. Is there an easy way to have my format and formulas automatically copy down when new data is added?

• The file Tableexampletemp.xlsx (see Figure 24-1 on the next page) contains units sold and revenue data for each of six salespersons. We know that new data will be added (beginning in row 12). Also, in column H, we would like to calculate the average price (Units/Revenue) earned by each salesperson. We would like to create an attractive format for the data, and have our formula for average price copy down automatically as new data is added.

  Figure 24-1: Data for creating a table

• Creating a table will allow our analysis and formatting to automatically update as new data is added. Begin by selecting the current range of data (E5:G11), including headers. Next, either click Table from the Insert tab, or press Ctrl+T. After checking the My Table Has Headers box, you will see that the table range (E5:G11) is formatted beautifully. This formatting will continue automatically whenever new data is entered into the table. Note that when you are working in a table, many styles and options are available on the Design tab (see Figure 24-2). You can then select a formatting style that will be continued as new data is added to the table.

  Figure 24-2: Table design options

• Note that our column headings have arrows, or filters (see Figure 24-3), that can be used to sort or filter the table (more on filtering later in this chapter).

  Figure 24-3: Formatted table with filters

• The cells in your selected table (excluding the headers) are given the name Table1 by default. We changed the name to Sales in the Properties group on the Design tab. If you click the Formulas tab, and then click Name Manager, you will see that range E6:G11 is named Sales. The beauty of this range name (and the table) is that the range dynamically expands to include new rows added to the bottom of the table, and new columns added to the right of the table. In Chapter 20, “The OFFSET Function,” we used the OFFSET function to create a dynamic range, but the new table capabilities make creation of a dynamic range a snap.

• Suppose that in D15 we want to compute total revenue. We begin by typing =SUM(S. Excel will offer us the option to automatically complete the entry with the table range Sales. We implemented Autocomplete by double clicking on the range name. You can also implement Autocomplete by moving the cursor to Sales and pressing the Tab key. Then, when we see =SUM(Sales and type [ (an opening bracket), Formula AutoComplete offers the option to complete the formula with column headings from the Sales table (see Figure 24-4). We can complete our formula as =SUM(Sales[Revenue]) and calculate total revenue as $155,480. (See Figure 24-5.) Note that we will select the entries in the AutoComplete box that begin with number signs (#) later in this chapter.

  Figure 24-4: AutoComplete options for a table

  Figure 24-5: Total revenue for original data

• If new rows of data are added, then the data in these rows will automatically be added to the formula.

• To illustrate this idea, suppose we add new data in row 12: Amanda sold 400 units for $5,000. As shown in Figure 24-6 on the next page, our total revenue has increased by $5,000 to $160,480.

  Figure 24-6: New data added to table in row 12

• Our formatting has been extended to row 12, and our total units and total revenue formulas have automatically updated to include Amanda’s data! Even if data is added within the table (instead of at the bottom), everything will be updated in a consistent fashion.

• Now suppose that we want to compute in column H the price per unit earned by each salesperson. We simply type Unit Price in H5 as the column heading, and then in cell H6 type =SALES[. In the AutoComplete list, click Revenue. The formula is now =SALES[Revenue]. Type / and use Formula AutoComplete to complete the formula as =SALES[Revenue]}/Sales[Units]. An amazing thing happens. Excel automatically copies the formula down to the bottom of the table in cell H12 (see Figure 24-7). If you go to any cell in column H, the formula will show up as =[Revenue]/[Units]. Of course, =[Revenue]/[Units] is a lot easier to understand than =G6/F6. This formula can be interpreted as taking whatever is in the current row in the Revenue column and divided it by whatever is in the current row in the Units column.

  Figure 24-7: Unit price formula autocopy

• If we click anywhere in a table, the Table Tools contextual tab appears on the Ribbon, and offers choices including the following:

  • Change Table Name-(we changed our name from Table1 (the default) to Sales).

  • Convert to Range-Converts the table range to normal cells and removes the table structure.

  • Resize Table-Adds or subtracts rows and/or columns to the defined table range.

  • Remove Duplicates-Removes rows which contain duplicates. For example, selecting only the Name column in the Remove Duplicates dialog box will ensure that a Name will not occur more than once. Checking both the Names and Units columns will ensure that no rows in the table will match both Name and Units, and so on.

  • Header Row-If checked, the header row is displayed. If unchecked, the header row is not displayed.

  • Total Row-We will discuss the Total Row later in this chapter.

  • First Column-If checked, a special format can be applied to the first column of the table.

  • Last Column-If checked, the last column of the table will be assigned a special format.

  • Banded Rows-If checked, even-numbered rows in the table are given a different format than odd-numbered rows.

  • Banded Columns-If checked, odd-numbered columns in the table are given a different format than even-numbered columns.

  • Table Styles-Select from any of the table formats shown in this group. Of course, if the table expands or contracts, the format will adjust appropriately.

• I have entered into my worksheet several years of natural gas prices, and have created a nice line chart displaying the monthly variation in prices. Can I set things up so that when I add new gas price data, my chart automatically updates?

• In the file Gasprices507.xlsx, the worksheet Original contains natural gas prices per thousand feet from July 2002 through December 2004 (see Figure 24-8 on the next page). As previously described, we selected B5:C34 (containing months and prices) and pressed Ctrl+T to create a table from this range. Then we created a line graph to display this data by clicking Line in the Charts group on the Insert tab, and selecting the fourth type of line graph. The line graph we created is shown in Figure 24-9 on the next page.

  Figure 24-8: Gas price data: 2002–2004

  Figure 24-9: Gas price line graph: 2002–2004 data

• Next, we copied this worksheet (by right-clicking the worksheet name, clicking Move Or Copy Sheet, and then clicking Make A Copy) and added gas prices through July 2006 (the data now extends to row 53). We named the new worksheet New Data. Note the line graph in this worksheet automatically updated to include the new data! (See Figure 24-10.)

  Figure 24-10: Gas price line graph: 2002–2006 data

• This example illustrates that if you create a table of your data before creating a chart, new data will automatically be included in the chart.

• For each sales transaction I have the salesperson, date, product, location, and size of the transaction. Can I easily summarize, for example, total lipstick sales in the east made by Jen or Colleen?

• The file Tablemakeuptemp.xlsx contains sales transactions (see Figure 24-11 on the next page). For each transaction, we have the following information: Transaction Number, Name, Date, Product, Location, Dollars, and Units sold. If we format this data as a table, we can add a Total row to the Units and Dollars columns, and then use the filter arrows to make the Total rows include the desired subset of transactions. To begin, we place our cursor anywhere within our data and create a table by pressing Ctrl+T. Note that if we scroll through the table, our header rows remain visible. With your cursor in the table, check the Total Row box in the Table Style Options group on the Design tab. By default, Excel will enter the total number of rows in the table in cell K1895, which we will delete. Click the arrow to the right of cells I1895 and J1895, and then click Sum. This totals all entries in columns I and J of our table. Thus, currently the total revenue is $239,012.67, and 78,707 units were sold (see Figure 24-12 on the next page and file Tablemakeuptotals.xlsx).

  Figure 24-11: Makeup sales data

  Figure 24-12: Total revenue and units sold

• To make our totals reflect only lipstick sales in the East made by either Jen or Colleen, click the arrow in F3 (to right of the Name header). Clear the Select All box, so that no names are selected. Then check the Colleen and the Jen boxes (see Figure 24-13), and then click OK. Next, click the Product arrow and check the lipstick box, and then click the Location arrow and check the East box. Use the same approach to show only lipstick in the Product column and only East in the location column. We now see all the data fitting our filtering criteria in Figure 24-14 and in the file Tablemakeupfinal.xlsx. We find that Colleen and Jennifer sold 497 units of lipstick in the East for a total of $1,515.89. This table filtering feature makes it a snap to easily compute totals for any subset of rows in an Excel worksheet!

  Figure 24-13: Selecting names from the table

  Figure 24-14: Filtered subtotals for units and revenue

• How can I easily refer to portions of a table in other parts of my workbook?

• The file Tablestructure.xlsx shows many examples of how we can refer to parts of a table when we are outside of the table. These references are often called structured references (see Figure 24-15 on the next page). When you enter a table name into a formula, Auto-Complete will make the column names and the following table specifiers available for selection:

  • Table Name-All cells in the table, excluding the header and total rows.

  • #All-All cells in the table, including the total row (if any).

  • #Data-All cells in the table except for the first row and the total row.

  • #Headers-Just the header row.

  • #Totals-Just the total row. If there is no total row, this returns an empty cell range.

  • #This Row-All table entries in the current row.

  • A column reference includes all cells in a table column, excluding the header and total row entry (if any).

  Figure 24-15: Structured references

• Here are some examples of how table specifiers can be used in formulas:

  • In cell C15, the formula =COUNTA(Table1[All#]) yields 55 because the table contains 55 entries.

  • In cell C16, the formula =COUNTA([Table1]) yields 45 because the headers and total row are not counted.

  • In cell C17, the formula =COUNTA(Table[#data]) yields 45 because the cell range D5:H13 is referenced.

  • In cell C18, the formula =COUNTA(Table1[#Headers]) yields 5 because only the header row (D4:H4) is referenced.

  • In cell C19, the formula =SUM(Table1[Q1]) yields 367 because the formula sums the entries in E5:E13.

  • In cell C20, the formula =SUM(Table1[#Totals]) sums up all entries in the total row and yields 1,340, which is the total sum of all table entries.

  • In cell C21, the formula =SUM(Table1[[Data#],[Q1]:[Q3]]) sums up all data entries that are in columns Q1:Q3, inclusive (cells E5:G13). Thus, column names separated by a colon include all data entries between and including the column name before the colon and the column name after the colon.

  • In cell B8, the formula =SUM(Table1[#This Row]) sums the entries in row 8 (41+28+49+40).

• Of course, all of these formulas will automatically update if new data is added to the table.

• Do conditional formats automatically apply to new data added to a table?

• Yes, conditional formats will automatically include new table data. (See Figure 24-16.) To illustrate, we placed in worksheet Original of file Tablestructure.xlsx a conditional format to highlight the three largest Q1 sales in column E. Then entries in rows 7, 12, and 13 are highlighted. In worksheet Add Biggersale, we added in cell E14 the entry 90. This becomes the largest entry in the column and is immediately highlighted. Cell E7 is no longer highlighted because it is no longer one of the three largest numbers in column E of the table.

  Figure 24-16: Conditional formatting extends automatically to new table data