OUR FAVORITE EXCEL TIPS
Making Sense of Percents Compute Work Days Sorting Data
TIP #1: MAKING SENSE OF PERCENTS
Percentages are becoming a necessary fixture in my every day life. I use them to track mutual fund performance. Most business stories you read use percentages; retail sales are measured in terms of percentage increases or decreases. If you look for trends or measure changes, chances are you use percentages, too.
In Excel, it’s easy to set up a percentage. To display in cell C3 the contents of cell C1 as a percentage of cell C2, use the formula =C1/C2 in cell C3. Then use the Format/Cell command to change the format of cell C3 from General to Percentage. (You can also click on the Percentage button on the Formatting toolbar.) You can likewise specify the number of decimal points displayed in cell C3 (the default is 2) with the Format/Cell command.
A shortcut comes into play when you enter a number that is also a percentage. For example, I want to compare the percentage in cell C3 to a target percentage – say, 10%. A shortcut lets you enter the value AND set the format all at once: In cell C4 I enter the value 10% (two digits plus the percent sign). Excel places the value .1 in cell C4, sets the format to percentage, and displays the value as 10%.
This automatic formatting brings with it some special behavior. Enter 10.0% or even 10.% (note the decimal point before the percent sign) and you'll see 10.00%. That's because once you type the decimal point, Excel uses the "2 decimal places" default to display the value. Enter .4% and you get a leading zero AND two decimal places (that is, Excel displays the value as 0.40%). You can, of course, change the number of decimal points using the Format/Cell command for individual cells or ranges of cells, but there is no global setting to change the default number of decimal places. It's two. Period. Go figure.
Let's say you have a cell that has been formatted as a percent. Now what you enter takes on new significance. Though the Excel help file states, "Numbers above 1 are automatically entered as percentages; and numbers below 1 are converted to percentages by multiplying by 100," that may or may not be how things actually work.
For example, when I entered 50 in a cell previously formatted as a percentage, I thought I would see 50%, if the help file is to be believed. Instead I saw 5000%. Oops.
To have Excel behave as described, you have to turn on the Enable automatic percent entry feature. (This is the default in Excel 2002, but not in Excel 2000.) To turn the feature on, choose Tools/Options/Edit, and be sure the "Enable automatic percent entry" box is checked.
There's actually a quick way to know if this option is turned on. If you begin to enter a new value and the percent sign remains displayed, the feature is turned ON. If you begin entering "123" (for the sake of argument) and the percent sign disappears, the feature is turned OFF.
Here's yet another twist to using the Percent sign. When you enter the percent sign after a cell reference in a formula, Excel multiplies the cell value by .01 when performing the calculation.
For instance, enter 5 in cell A1 and the formula =A1% in cell B1. The result: .05, which is the
value in cell A1 multiplied by .01 (or, seen another way, the value in cell A1 divided by 100).
Though you've used the percent sign, Excel has not changed the formatting of the cell to a percentage.
-- James E. Powell
^ Top of page ^
TIP #2: COMPUTE WORK DAYS
Reader Leon Graves asked how to find the number of weekdays between two dates, excluding holidays. Since he didn’t specify which Office application he was using, I’ll start with Excel, since that’s the easiest. Believe it or not, the Excel Analysis ToolPak contains a Visual Basic for Applications (VBA) function designed for this very purpose!
In a cell, enter:
=NetWorkDays(“01/01/2001”,”03/31/2001”) and tab out of the
cell. It should show 65, the number of weekdays in the
first quarter of 2001. Changing your function to
=NetWorkDays(“01/01/01”,”03/31/01”,{“01/01/01”,”02/19/01”})
eliminates New Year's Day and President's Day, yielding 63 work days.
To eliminate a longer list of holidays, replace the
third function parameter with a range of cells containing
the dates to be excluded. For example,
=NetWorkDays(“01/01/01”,”12/31/01”,E1:E12) where column E
contains a list of holidays in rows 1 through 12.
If the NetWorkDays function is not available, run the
Setup program to install the Analysis ToolPak. After you
install the Analysis ToolPak, you must enable it by using
the Add-Ins command on the Tools menu. More information on
the NetWorkDays function can be found in Excel’s help file.
Public Function WorkDays(D1 As Date, D2 As Date) As Long
Dim vDate As Date, vHolidays(2) As Date, I As Integer
'Initialze variables
vHolidays(1) = "01/01/01"
vHolidays(2) = "02/19/01"
WorkDays = 0
vDate = D1
'Loop through the rnge of dates
While vDate <= D2
If Weekday(vDate) > 1 And Weekday(vDate) < 7 Then
WorkDays = WorkDays + 1
End If
For I = 1 To 2
If vDate = vHolidays(I) Then
WorkDays = WorkDays - 1
End If
Next I
vDate = DateAdd("d", 1, vDate)
Wend
End Function
The array vHolidays could be expanded to hold more dates
and it could be loaded from a table if you wish.
-- Dick Archer
^ Top of page ^
TIP #3: SORTING DATA
The instructions, tips, and techniques described here apply to Excel 97, 2000, and 2002 (aka XP). We'll explain how to sort data in a list. It isn't difficult, but you can get into trouble quickly, so our tips focus on how to ensure you don't lose or corrupt your data.
To get started, open a new worksheet in Excel and enter the following data in rows 1 through 12, columns A through C.
Agent Sale Date Region Quantity
Adams 10/01/01 North 175
Baker 10/06/01 South 165
Baker 10/06/01 East 340
Carter 10/11/01 West 150
Adams 10/16/01 East 400
Baker 10/21/01 North 170
Adams 10/26/01 West 200
Baker 11/05/01 West 250
Adams 11/20/01 North 200
Carter 12/03/01 North 350
Baker 12/08/01 East 300
To save time you can download the data in a spreadsheet from:
http://www.officeletter.com/exceldata.xls
SORTING DATA IN EXCEL
Our data contains a header row, plus 11 rows of data. It is currently arranged in order by sale date, but in this exercise we'll explore how to view the data sorted by agent.
Your first idea may be to select column A (the column containing the agent name) and perform a sort on that column. Unfortunately, that doesn't keep the sale information (quantity, region, and date) together with the agent. You must first select ALL columns that contain data for the agent (in our example, you must select all four columns), then sort on the column(s) you choose.
To sort our data by agent, select cells A1 through D12. Shortcut: click on any cell in the range and press Ctrl+Shift+* (hold down Ctrl and Shift, then press the 8 key on the top row of your keyboard).
From the menu choose Data/Sort. Notice that Excel recognizes that our data has a header row. Excel has selected the "Header row" option at the bottom of the dialog box, and the first row is now no longer part of our data range. Excel also suggests we sort by the first column of our data. Excel uses the text in the header row for the column names, so the Sort by field is pre-filled with "Agent" -- the name of our first column.
Ascending is the desired order (we want to sort alphabetically), so choose OK.
Don't like the sort? Choose Edit/Undo, or click the Undo button in your toolbar.
MULTIPLE SORTS
In this example, we'll sort by Region, and within Region by Agent.
With all cells in the range selected (including Row 1), select Data/Sort. In Sort by, choose Region from the pull-down list. In Then by, choose Agent. Click on OK, and your data now appears in the proper order.
SORTING TIPS
Strictly speaking, you don't need to select all the cells before you use the Data/Sort command if your data is in a contiguous range (no empty rows or columns). You need only click on any cell within the range before using the Data/Sort command. Excel assumes the dimensions of the data range automatically. We suggest you double-check the range just to make sure everything is selected.
If you sort the same range more than once, you may notice that Excel remembers the sort fields (and sort order) of your last sort. When you save a file, your sort parameters are saved as well.
Excel sorts text alphabetically without regard to capitalization. Thus, the words adam, before, and After sort as:
adam, After, before
Shortcut: If you want to sort your data by the first column, select all the rows and columns, then use the Sort Ascending or Sort Descending button on Excel's toolbar. (Depending on which version you use, your toolbar may only show the Sort Ascending button.) The Sort Ascending button has the letter A above the letter Z, with a down-pointing arrow to the right. The Sort Descending's face has the Z on top, the A underneath, and a down-pointing arrow to the right.
-- James E. Powell
^ Top of page ^
|