TECHNOLOGY EXCEL - Strategic Finance

[Pages:2]TECHNOLOGY

EXCEL By Bill Jelen

Create a Leaderboard in Excel

Walk into most car dealerships, and you'll see a whiteboard on the wall showing how many cars each sales rep sold that month. Using the tools in this article, you can create an eye-catching leaderboard in Excel that shows the top three sales reps.

Calculating Totals for Each Rep

In Figure 1, columns A:C are the original data source. This might be a sales file that's downloaded daily into Excel, or it might be an external query that downloads information from your sales system every few minutes. Convert that range to a table by selecting one cell in the range and pressing Ctrl+T. The table feature, introduced in Excel 2007, allows subsequent formulas to expand as the table expands. You can accept the defaults for the table. In this example, I have typed a new name, Sales, as the Table Name on the Table Tools Design tab of the ribbon.

Column F contains a list of the sales reps. You'll have to remember to manually add or delete people as sales reps join the company. Column G uses the SUMIF function to get the total sales for

each sales rep. The formula in cell G2 is: =SUMIF(Sales[Rep],F2,Sales[Amount]).

Sorting Using Formulas

The table in F1:G8 contains an alphabetical list of sales reps. You want to extract the three sales reps with the most sales and show them in descending order of sales value. Column E is a formula to calculate the rank of each sales rep in the list. You might think you could use =RANK(G2,$G$2:$G$8) in cell E2 and copy down in the column, but this will fail whenever two sales reps have a tie. If two reps are tied for first, Excel will give them each a rank of 1, and no one will be ranked 2. New options in Excel 2010 allow you to assign each of the tied sales rep a rank of 1.5 (which is the average of 1 and 2), but that won't help with the VLOOKUP formulas to come in columns J and K.

The solution is to add a COUNTIF to the formula, which counts how many records above the current record have the exact same sales as the current record. This way, if there's a tie, each sales rep is assigned a different rank. The formula in E2 is: =RANK(G2,$G$2: $G$8)+COUNTIF(G$1:G1,G2). As you

copy this formula down into the rest of the column, the G$1:G1 range will expand.

Column I contains the numbers 1, 2, 3 in sequence. VLOOKUPs in columns J and K return the name of the sales rep ranked first, second, and third, as well as their sales. Cell J2 contains =VLOOKUP ($I2,$E$2:$G$8,2,FALSE), and cell K2 contains =VLOOKUP($I2,$E$2:$G$8,3, FALSE).

Creating an EyeCatching Display

SmartArt diagrams share information from Excel in bold and colorful forms. Start out with a generic diagram using these steps: 1. On the Insert tab, choose SmartArt. 2. Choose the List category, then the

icon for Vertical Box List. This particular diagram is well-suited to longer phrases. 3. Add placeholder text, such as "ABCDEF is in the lead with $9999." The SmartArt will automatically resize based on the size of the placeholder text, so try to include text of the appropriate length. 4. On the SmartArt Tools tab, use the

54

STRATEGIC FINANCE I August 2012

Figure 1

Change Colors dropdown menu to choose a color scheme. In the SmartArt Styles gallery, choose the second of seven 3-D styles. The SmartArt tool is a great way to build a collection of shapes that dynamically resize. The problem is that SmartArt was designed for Office 2007 by the PowerPoint team, and they never thought of allowing the text in the shapes to come from formulas in Excel. You need to convert the SmartArt to regular Excel shapes in order to populate the content from Excel cells. This is sim-

ple in Excel 2010, but it's a bit trickier in Excel 2007.

In Excel 2010, choose the SmartArt and then click Convert to Shapes on the right side of the SmartArt tab.

In Excel 2007, select the SmartArt. Click inside the SmartArt border, but not on any particular shape--find a small bit of white space. Press Ctrl+A to select all shapes, then Ctrl+C to copy. Select a cell in a new location in your workbook and press Ctrl+V to paste the collection of shapes. You can now delete the original SmartArt.

Click on a shape that contains place-

holder text. The first click will select the shape with a solid selection border. A second click will change that border to a dashed line. You can't assign a formula when the dashed line is present. If you see a dashed line, click on the dashed line to change back to a solid line. Click in the formula bar. Type an equals sign (=) and the cell that contains the text for that shape. For example, type =L2 for the first shape. Press the Enter key, and the placeholder text is replaced with the result of the formula in cell L2. Note that the formula assigned to the shape must be a simple reference to a single cell. You can't use =J2&K2 as the formula for a shape. SF

Bill Jelen is the host of and the coauthor of Don't Fear the Spreadsheet. Send questions for future articles to IMA@.

August 2012 I STRATEGIC FINANCE 55

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download