 |
Microsoft Excel Tips and Tricks
Newsletter
The tips that follow formed part of a series in our free
monthly newsletter:
Subscribe now!
Contents:
Totals are not Exact (Rounding):
Your Own Excel Shortcut Key
Comparing Cells (=)
Introducing =IF:
More on =IF: Nesting
Introducing CHOOSE:
Conditional Formatting 1:
Conditional Formatting 2:
Conditional Formatting 3:
Lookups: VLOOKUP and HLOOKUP
Back to Basics: SUM
Basic Functions: AVERAGE
Basic Functions: COUNT
Copy & Paste Special (Values)
Parsing Data with "Text-to-Columns"
Numeric Formats
Date, Time and other Formats
Special Formats
Format Cells, Alignment
Format Cells, Font / Border / Patterns / Protection
Introducing Data Validation
Copy Column Widths
Superscripts
IF() Again
SUMIF()
COUNTIF()
COUNT, COUNTA, COUNTBLANK
Unusual but Useful: FLOOR and CEILING
Unusual but Interesting: EVEN and ODD
Really Odd Functions
The Cowboy Function, ROUNDUP (and its companion)
A Little Time-Saver: Entering Decimals Without the Point
Add Useful Features to Your Toolbar
Using Excel to Produce Data for Other Programs
Using Excel Formulas to Produce Data for Other Programs (II)
Using Excel Formulas to Produce Data for Other Programs (III)
Using Excel String Formulas to Help Other Programs
Helpline Tip: How to Protect Data in Excel
Contributed Tips
Question: In Excel I am adding Sales Tax or VAT to a
series of prices, and then adding them. The total is sometimes
wrong by a few cents/pence/etc.. Why is this?
Answer: If your prices have cent amounts, then 14% VAT
will give you fractions of cents, which you won’t see if your
figures are formatted for Currency with two decimals. These can
add up so that your total doesn’t agree with what you see on
screen, e.g. R1.11 plus 14% VAT is R 1.2654, formatted to two
decimals you see R 1.27: If you add two of these, Excel shows
you R 2.53 (actually R 2.5308), whereas you expect R 1.27 + R
1.27 = R 2.54.
Q: How can I fix this?
A: Use the =Round() function on every calculation.
Instead of =B2*1.14 use =ROUND(B2*1.14,2). This will round each
result to two decimals (which is what you see anyway) and your
total will then be correct.
Q: Can I round to whole Rand?
A: Yes. Use =ROUND(G2*1.14,0) – the “,0” tells Excel to
use zero decimal places. You can also round to tens of Rand, or
hundreds, by using negative figures (-1 and -2 respectively).
Q: How can I create my own Excel shortcut key for
frequently-used operations?
A: Record the operation as a Macro: Tools > Macro >
Record New Macro. Name the Macro (no spaces). Put it in the
Personal Macro Workbook if you want it to be available at all
times. Here you specify the shortcut key: Hold in the Shift
key and press a letter on the keyboard. (Shortcut keys with "Shift"
work best because there is no risk of their replacing built-in
shortcuts like Ctrl+C (Copy). You can however use the
letter unshifted.) During recording, pay attention to whether
you want to record Absolute or Relative references –the results
can be very different! When you have finished performing the
actions, Stop Recording! Save your file in case of mishaps. To
test your macro, hold in the Ctrl key and press the
Shift+Key combination you set earlier.
The Problem: To get a quick comparison of two columns of
figures or text, where some figures differ but many are the same
– for example where you send somebody data, and they change some
of it and send it back.
Setup: To simulate the problem, open a new Excel
Workbook, type several items (numbers or text) into column A
(starting in cell A1), copy them to column B and then change
some of them.
Solution 1 (Simple): In cell C1, enter: =A1=B1
(remember that you can type “=”, then click on, or use the left
arrow key to point at, cell A1, then type “=”, then click on, or
use the left arrow key to point at, cell B1, then press Enter).
You will see the word TRUE in the cell if the two cells
match,
FALSE
if they do not.
Now copy the formula in cell C1 down as far as your data goes
(remember that you must select the cell, then you can grab the
little box at the bottom right corner of the cell and drag it
down as far as you need to. Another way is to highlight the
formula and the cells below it as far down as necessary, then
press Ctrl+D
(fill down)).
All the non-matching cells will show up as FALSE.
Solution 2 (More fun): In cell D1 (keeping the previous
solution in C1), enter =IF(A1=B1,””,”***”), then copy
down as before.
You will see asterisks opposite the mismatches, blanks where the
cells match – this makes the mismatches stand out more.
How this works: The syntax of the IF function is
IF(comparison, true part, false part). If the comparison
is True, you see true part
in the cell (in this case just a blank, “”), otherwise you
see false part (in this case three asterisks “***”). Note
that the strings (e.g. “***”) have to be inside double quotes.
The Problem: Last lesson we looked at the IF function
(IF(comparison, true part, false part). This is great if you
have an either-or situation, but what if I have, say, three
possibilities? For example, when comparing two values A1 and B1,
I want to know if the two are equal, or which one is bigger.
Solution: The solution here is to nest your functions,
i.e. to put one inside another. In cell C1, enter =IF(A1=B1,”=”,
IF(A1<B1,”<”,”>”)) – if you are comparing a column of
figures, copy down.
How this works: The first IF checks if A1=B1 and,
if the comparison is True, shows “=” in the cell. If the
condition is false, the second IF takes place, checking
if A1 is less than B1. If this is true it shows a “less than”
(“<”), otherwise you see false part “greater than” (“>”), which
in this case is the only other possibility.
Remember with the “<” and “>” symbols, the wide part is at the
big end and the point at the little end.
If you prefer, and have the space, you could write it out more
explicitly, e.g. =IF(A1=B1,”A=B”, IF(A1<B1,”A<B”,”A>B”)) or
even =IF(A1=B1,”Equal”, IF(A1<B1,”A is smaller than B”,”A is
bigger than B”)).
Comparisons like this are particularly useful with dates, where
it isn’t always easy to see at a glance which is bigger.
Next: What if we have a dozen choices? Thereafter: Making
the results appear in different colours!
The Problem: Last lesson (above) we looked at the IF
function and saw how if we have, say, three possibilities, we
can nest IF functions, i.e. put one inside another. This is fine
for up to three or four choices, but what if we have a dozen?
For example, let’s say we’d like a function that will return the
name of the day of the week, given a number.
Solution: The CHOOSE function has the syntax =CHOOSE(Index,Value1,Value2,
Value3,…)
How this works: If Index (which might be a literal
number, a formula, or a cell reference) is 1, the formula
shows you Value1, if
2, Value2, etc.
Example: Type the headings Date; Day No, and
Day in cells A1, B1 and C1 respectively.
In cell A2 enter today’s date, e.g. 13/6
In cell B2 enter the formula =WEEKDAY(A2) – this returns
a number, where 1 = Sunday to 7 = Saturday.
In cell C2, enter the formula =CHOOSE(B2,"Sun","Mon","Tues","Wednes","Thurs","Fri","Satur")&"day"
– this will display the day of the week, e.g. Friday.
Now that you’ve seen how it works, you can combine the two
formulas in B2 and C2 into one, by nesting (try this in D2): =CHOOSE(WEEKDAY(A2),"Sun","Mon","Tues","Wednes","Thurs","Fri","Satur")&"day"
Change the date in A2 to see the results change, or select the
date and the formulas, then drag the “+” at the bottom right of
the selection down to cells below.
Next lesson: Making the results appear in different
colours!
Background: Over the last few lessons we have looked at
functions that can show us radically different results in a cell
depending on certain choices. To make these differences more
obvious, we would like to format the results in different
colours.
The Problem: We are running our bank account on a
spreadsheet and we want debit amounts and overdrawn balances to
show up in
red.
Solution: Use a currency format with negatives in red.
Example: Select everything in the table below:
|
Date |
Description |
Amount |
Balance
|
|
01/07/2003 |
Brought Forward |
|
2000 |
|
02/07/2003 |
Rent |
-2000 |
|
|
03/07/2003 |
Credit Card |
-1500 |
|
|
04/07/2003 |
Salary |
5000 |
|
Copy to the Clipboard (shortcut: Ctrl+C).
Swap to Excel. Start a new workbook.
In Cell A1, Paste (Ctrl+V) (if you prefer to retype the
spreadsheet, the heading Date must be in cell A1).
If the dates don’t show correctly, re-enter the top one, and
fill down (Grab the little cross at the bottom right-hand corner
of the cell and drag it down).
Widen columns as required (drag or double-click between column
headings).
To calculate the running Balance, we need a formula in D3: =D2+C3
– this calculates the next balance as the previous balance plus
the new Amount.
Copy the formula down (Select D3. Grab the little cross at the
bottom right-hand corner of the cell and drag it down to D5).
Now we are ready to format the cells. Select C2 to D5 (the
amount and balance columns).
Click Format > Cells (or press the Ctrl+1
shortcut). Select the “Number” tab at the top.
You have two choices: Number, or Currency, depending on whether
you would like a currency symbol. In the case of “Number”,
choose “Use 1000 Separator”. For Currency, you can change the
symbol. In both cases you can set the number of decimals. The
important point for us is that you should choose the bottom
choice under “Negative Numbers” – this shows the figure in red,
with a minus sign.
Your debit amounts and balances will now show up in red.
Next lesson: Making the credit amounts appear in a
different colour!
PS: Save this spreadsheet for the next lesson!
Background:Above we looked at using Format >
Cells (Ctrl+1) > Number > Number or
Currency, to show negative numbers in red.
Here we will look at an alternative that will let us use different
colours (and other formatting) for various conditions.
The Problem:
We are running our bank account on a spreadsheet.
We have an overdraft limit of R1200 and we want balances over this
limit to show up in bold red. We also want debit balances (i.e.
between 0 and -R1200) to show up in orange.
Solution:
Use Conditional Formatting.
Example:
Last
lesson we set up the following example, and saved it for this lesson:
|
|
A
|
B
|
C
|
D
|
|
1
|
Date
|
Description
|
Amount
|
Balance
|
|
2
|
01/07/2003
|
Brought Forward
|
|
1800.00
|
|
3
|
02/07/2003
|
Rent
|
-2000.00
|
-200.00
|
|
4
|
03/07/2003
|
Credit Card
|
-1500.00
|
-1700.00
|
|
5
|
04/07/2003
|
Salary
|
5000.00
|
3300.00
|
(The formula in D3: =D2+C3,
copied to the cells below, is used to calculate the running Balance)
Note that we’ve reduced the figure in cell D2
from last lesson’s example!
We want to conditionally format the
Balances, so select cells D2..D5.
Click (menu) Format > Conditional
Formatting.
Set Condition 1: Cell Value is | less than | -1500;
Format Colour = Orange, Font Style = Bold.
Click Add for a second condition.
Set Condition 2: Cell Value is | less than | 0 (zero);
Format Colour = Orange.
Click OK.
Your –R200 debit balance will now show up
in orange, and the –R1700 balance in red bold.
Note the order in which we did the
Conditional Formatting: It applies the first formatting it finds that
matches a condition.
Exercise: Can you get the Credit and
Zero balances to show up in Green?
Next lesson: Dealing with varying conditions.
PS: Save this spreadsheet for the next
lesson.
Background: Above we looked at using Format > Conditional
Formatting to show debit balances down to a fixed overdraft limit in
orange and balances below that limit in
bold red. Now we will look at dealing with a
varying limit.
The Problem: We are running our bank account on a
spreadsheet. We have a reducing
overdraft limit and we want balances over this limit to show up in bold red.
We also want higher debit balances to show up in orange.
Solution: Use Conditional Formatting.
Example: Use the spreadsheet we set up last lesson,
or copy and paste the one below.
-
Add a
new column
E
for the Limits.
-
In E2
enter the starting Limit, –1900.
-
In E3
the formula:
=E2+100 – copy this down.
-
Copy the
format from cell D3 (negative values in red) to this
new column.
-
The formula in D3: =D2+C3, copied to the cells below, is used to
calculate the running Balance.
-
Add a
few more rows of data and, except for the formatting of the
Balances, you will have this effect:
|
|
A
|
B
|
C
|
D
|
E
|
|
1
|
Date
|
Description
|
Amount
|
Balance
|
Limit
|
|
2
|
01/07/2003
|
Brought Forward
|
|
1800.00
|
-1900.00
|
|
3
|
02/07/2003
|
Rent
|
-2000.00
|
-200.00
|
-1800.00
|
|
4
|
03/07/2003
|
Credit Card
|
-1500.00
|
-1700.00
|
-1700.00
|
|
5
|
04/07/2003
|
Tips
|
50.00
|
-1650.00
|
-1600.00
|
|
6
|
05/07/2003
|
Tips
|
60.00
|
-1590.00
|
-1500.00
|
|
7
|
06/07/2003
|
Salary
|
5000.00
|
3410.00
|
-1400.00
|
We want to
conditionally format the Balance and then copy the format, so select
cell D2 only:
Click
(menu) Format > Conditional Formatting and modify your existing
conditions:
Modify
Condition 1: Cell Value is | less than | =E2; Format
Colour = Red, Font Style = Bold.
Note
that if you use the red arrow button on the extreme right of
Condition 1 to point out cell E2, it will appear as =$E$2. You must delete the dollar
signs because we want the formula to refer to different cells as we
copy it.
Keep
Condition 2: Cell Value is | less than | 0 (zero);
Format Colour = Orange.
Click
OK.
Now copy
the formula down to the remaining balances. The easiest way to do this is
to click on the format painter (“paintbrush”) tool (on the Standard
Toolbar) and then drag over the remaining cells.
Your
debit balances will now show up in orange while they are above or on
the adjacent limit, and in red bold when below it, as shown above.
Click on
cell D3, Format > Conditional Formatting.
And
notice that it uses =E3 in the condition, not =E2 as used in cell D2.
Exercise:
Save the file and repeat the Conditional Formatting, but instead use
=$E$2 in the condition.
Copy the format down and notice the difference!
|
|
|
The formula is: VLOOKUP(Value,Range,Column,FALSE)
VLOOKUP finds the lookup value in the first column of the
lookup range ("Range").
It then returns the value in that row, for the required
column of the range.
Without FALSE, it finds exact values
only. If you
want
it to find non-matching values (they must be sorted
in ascending order), omit FALSE.
Then, if does not find the exact value,
it uses the smaller one, e.g. if values in column A are 10; 50
and 100 and we look up 90, it will return the lookup value
corresponding to 50. Note that is
does not interpolate!
HLOOKUP(Value,Range,Row,FALSE)
works identically, but
horizontally: It goes across row one until it
finds (or passes) the lookup value, then goes down by the number
of rows specified.
The Sigma (Σ) button on the "Standard" toolbar creates the
=SUM() formula in the selected cell (or cells). It will try to
select the correct range: If there are figures above the cell,
it will select up until it hits a blank cell or text. If
it gets it wrong, you can easily correct it, because recent
versions of Excel outline the range (usually in blue): You can
then grab the outline and drag it elsewhere, or just drag a
corner.
The format of SUM is "=SUM(range)", where range is of the form
cell1:cell2, e.g. =SUM(A2:A6). However, you can use separate cells or
ranges, separated by commas, e.g. =SUM(A2:A6, A8, A10:A12). Of
course, you don't have to use the toolbar button, you can just type the
formula.
What you don't need to do, is do SUM's work for it: =SUM(A2+A4+A6) is
unnecessary: For that you only need =A2+A4+A6 or =SUM(A2, A4, A6) !
The AVERAGE Function returns the arithmetic mean of the arguments.
The syntax is exactly like SUM i.e. "=AVERAGE(range)", where range is of
the form cell1:cell2, e.g. =AVERAGE(A2:A6). However, you can use
separate cells or ranges, separated by commas, e.g. =AVERAGE(A2:A6,
A8, A10:A12).
Tip: Watch out for blank (empty) cells: They are not the same as
cells containing zero. Zeros are counted, and will therefore reduce
the average (more cells included, but nothing added to totals) whereas blank
cells are not used (e.g. 7 cells in range, but 3 are blank, so the average
is taken of 4 cells only).
The COUNT Function counts the number of cells in the target range that
contain numbers. Its syntax is exactly like SUM i.e.
"=COUNT(range)", where “range” is of the usual form “cell1:cell2”, e.g.
=COUNT(A2:A6).
Watch out for blank (empty) cells: They are not the same as cells
containing zero. Zeros are counted, blank cells are not! It can
be confusing if you have turned off zero values (Tools > Options > View
>Zero Values unticked).
Tip: If
you want to count all filled cells including those that contain text –not
just numbers– use the COUNTA (“Count All”) function instead.
This lesson's tip arises from a user's question. She had an Excel
spreadsheet that was to be imported into Maximizer, but the dialling code
and telephone number were in separate columns: How could they be combined
into one column?
To demonstrate the solution, set up a sample spreadsheet with column
headings "Code" and "Phone" in cells A1 and B1 respectively. Put some
dialling codes and phone numbers on the rows below (start the dialling codes
with a single quote (') so that Excel does not treat them as numbers and
lose the leading zero).
Solution:
Find or insert a blank column to the right. In this column, row 2,
enter the formula =A2&" "&B2 --we are using the ampersand (&) to
concatenate two strings (the dialling code in A2 and phone number in B2)
with a space in between (inside double-quotes). The formula should
show the combined code and phone number. Now copy it down all the rows
that have data. To replace the contents of the "Phone" column with the
combined number, copy the formulas, select cell B2, and Edit > Pastel
Special > Values. This copies the answers to the formula, over the old
phone numbers. Delete the "Code" column (A) and the formula column,
and you are done!
With Excel's wide variety of formulas, there are many ways you can
manipulate your data using this method. I use it for setting up blocks
of repetitive Visual Basic code!
Next lesson we'll talk about how you separate data that should be in
different
columns!
Last lesson we looked at joining data from several columns into one.
Now we'll look at the opposite: How to split up data into several columns.
For example, suppose you had a column of addresses, with the individual
"lines" separated by commas, for example "PO Box 987, Gallo Manor, 2052".
You want to break this up into three columns.
Set up a sample spreadsheet with a column containing a few addresses in
this format.
Solution:
Select the data. Click Data > Test to Columns. A "wizard"
dialog box will pop up. Our first choice is "delimited", because we
are going to break up the data at commas. Click Next>>. The
second step is to choose the delimiter: Comma. Click Next>>. The
third and final step lets us define the data type. It is a good idea
to click on each column that could contain a postal code, and set its column
data format to text. That way you will not lose the leading zeros on
Pretoria postal codes. Click "Finish" and the data will be broken up
into columns.
Next lesson: Numeric Formats and formatting.
Let's look at formatting in more detail...
If you select a cell or range of cells and either click Format > Cells,
or press Ctrl + 1, the following dialog box pops up: (yours may be on a
different tab - click the "Number" Tab, the first one). We'll briefly look through the categories:
- General – no options here: This is exactly as entered.
- Number – as above: You can set the number of decimal places, the
1000 separator (usually a comma, but depends on your Windows Regional
Settings, as in fact does your decimal symbol), and how it treats
negatives.
Currency – Similar to Number, but you can select a currency
symbol. The way negatives are treated (minus sign or parentheses
"()"), and the placement of the negative and the currency symbol, depend
on your Windows Regional Settings fro Currency.
- Accounting – fewer options here: You can set the number of decimal
places and the symbol, but not how negative appear. The currency
symbols are lined up against the left margin.
Last lesson we started looking at formatting. Select a cell or
range of cells and either click Format > Cells, or press Ctrl + 1. The
"Format Cells" dialog box pops up: Click the "Number" Tab (the first one),
then select "Date":
When you click on a format, the sample at the top changes to show you
today's date in that format. Notice that the top two, marked with
asterisks (*) change depending on your PC's settings: A good choice if you
want the user to see the date in his preferred format, but may be confusing
on a PC with other settings.
The PC's date settings, by the way, are done under Start > Settings >
Control Panel > Regional and Language Options, firstly by language - English
(South Africa), and then (if you don't like your dates to have the year
first!) under Customize, on the Date tab (this is for Windows XP
Professional - it will be a little different on other versions).
Let's look at the other number types:
- Time – not much here: Again a regional setting, and some others.
- Percentage – You can set the number of decimal places. The
number is shown multiplied by 100, with a "%" sign at the end.
- Fraction – Did you know Excel could show fractions? OK, they
are written out with a slash, i.e. ¼ is shown as 1/4, but good
nevertheless.
- Scientific – The number shows with one digit, the decimal symbol, as
many decimal places as you set, an E (for "exponent") and a power of 10.
e.g. 1.23E+03 means 1.23 x 10³ = 1.23 x 1000 = 1230.
- Text – Numbers show exactly as entered, and are treated as text
instead of numbers. You can get a similar effect by typing a
single quote (') at the beginning of numbers you enter, for example when
you are entering Tshwane postal codes, type
'0040
(note the quote at the start) or format your column as text first,
and Excel won't change it to 40!
Below we'll look at creating your own formats if none of the above suits
you, using custom formats.
Last Tip we looked at the standard number (and
other) formats in Excel.
Now we'll look at creating your own formats if none
of those suits you, using Format > Cells > Number > custom formats.
Here are some interesting formats:
-
#,##0.00;[Red]-#,##0.00
– Thousands, two decimals, negatives in red with minus sign.
-
R #,##0;R -#,##0 –
Thousands, currency (R), no decimals.
-
_ R * #,##0_ ;_ R *
-#,##0_ ;_ R * "-"_ ;_ @_ – Accounting (currency symbol left-aligned),
no decimals.
0.00" Credit";0.00" Debit"
– displays positive numbers followed by the word
"Credit"; negatives followed by "Debit".
[Red][<=100];[Blue][>500] – the number will be
shown Red if less than or equal to 100, Blue if greater than 500.
dd/mm/yyyy – day /
month number / 4-digit year.
dd-mmm-yy hh:mm –
day - month name (3 letters) - 2-digit year, time in hours and minutes
(24-hr clock).
[h]:mm:ss –
Hours:minutes:seconds, where hours can exceed 24 ("hh" only runs to 23
before it carries into days).
For numeric formats, the formatting characters
are: # displays digits only if the number is big enough. 0 (zero) displays zeros
if the number has fewer digits than there are zeros in the format. Excel
understands the colour names Red, Green, Blue, Yellow, Magenta, Cyan, Black and
White.
For numeric formats, you can have up to four
sections separated by semicolons (;). The first section formats positive
numbers; the second is for negative numbers; the third for zero; and the fourth
for text. If you use only section 1 it is applied to all numbers; if you
use only two sections, it uses the formatting of the first section for zeros.
You can hide positives, negatives, or zeros by using a blank section (i.e. a
semicolon only).
For example, the format
[Blue]#,##0;[Green]#,##0;[Magenta]"Nil";[Red]"Text!" will display positive
numbers in blue, negative numbers in green (with no minus sign), the word "Nil"
in magenta if the cell contains zero, and the word "Text!" in red if it contains
any text (to display the actual text, use @ or omit the last section).
To find out more, search Excel Help for
"Guidelines for custom number formats" or e-mail us for the
Special Formats spreadsheet.
Our Story
(for "Prince Valiant" readers): In the last few tips we have looked at the
number formats in Excel.
Now we'll look at the next tab of the same dialog,
Format > Cells > Alignment:
Horizontally, you can align text to the left, centre, or right. "General"
(the default) aligns text to the left, numbers to the right, and logical and
error values to the centre. "Left", "Right", and "Distributed" allow you
to indent by a given number of characters. "Justify" aligns with both
margins, but you can't indent from them. A drawback to "Distributed", as
compared to "Justify", is the way it handles the last line –see top example
above right.
Vertically, your choices are "Top", Bottom", "Centre", "Distributed", and
"Justify". The latter two seem to behave the same way: If the row height
is larger than is required for the text (because it was set manually, or there
is higher text on a different row) the text is spaced out vertically to cover
the cell (see second example above right). With these two choices, the
text automatically wraps, otherwise you must use "Wrap Text" for multi-line
text.
The text angle does not work with all options.
If you merge cells, only the text in the top left cell is displayed, across all
the cells. Automatic row sizing does not work if you merge cells.
This means that you have to select each range of cells to merge, individually.
One way around this is to use "Center Across Selection": It will merge the cells
horizontally only, while keeping rows separate.
We have been unable to work out what the greyed checkbox "Justify distributed"
does or how to make it available and, judging by the fact that Help says nothing
about it, Microsoft are equally puzzled!
In the last few issues we looked at the first two
tabs of the
Format > Cells dialog in Excel, which cover the number formats and
alignment. We will now briefly cover the other tabs in the dialog box.
On the Font tab, things are pretty
straightforward. Two points worth noting: For the size, you can type any
number between 1 and 1638, not just those in the Size list. If you tick
the Normal Font check box, the settings on the Font tab will reset to the
default style.
On the Border tab, just remember to choose
the Line Style first, before you tell Excel where to put it.
The Patterns tab lets you select the
background colour for the cell, and then overlay a pattern (always in black!)
over it if desired. Not as versatile as PowerPoint where you can get one
colour to shade into another!
The final tab, Protection, is the sparsest
of the lot, but there is a fair bit to be said about it. The checkbox "Hidden"
hides the formula in a cell so that it is not visible in the editing box when
you click on the cell. The "Locked" checkbox stops the selected cells
being changed, resized, moved, or deleted. However, both these
settings only work if the sheet is protected. The trick is to first set
all the required formula cells hidden, and unlock all the cells where you want
to allow the user to enter data (notice that all cells are marked "locked" by
default) and then protect the sheet, by clicking Tools > Protection > Protect
Sheet (password optional). To make it
obvious to users where they can enter data, I like to give the
spreadsheet a background colour, such as grey or light blue, and
give the unlocked cells "no colour" (white), possibly with an
outline border. People are used to white text boxes on
grey Windows forms, so this seems intuitive. Of course
these colour setting must be set before
setting Protection on!
Wouldn't it be nice to prevent users from entering
wrong data into your spreadsheets? Now you can: With Data Validation.
On the Data menu, click Validation.
A dialog with three tabs appears: Settings, Input Message, and Error Alert.
On the Settings tab you set the Validation Criteria, in
other words you tell Excel what data are acceptable. Some
possibilities:
- Any Value (the default)
- Whole numbers (integers) between 0 and 100.
- Decimal numbers greater than 20.
- Decimal numbers greater than a specified cell.
- Dates greater than or equal to =TODAY()
- Text with a length of 4 characters (e.g. for Post Codes - format the
cell as Text too).
- List from values A,B,C,D using "In-cell dropdown" (this gives a
drop-down list using the values specified –the drop-down arrow only appears
when you click on the cell).
- List from cells =$A$4:$A$8, using "In-cell dropdown" (this gives a
drop-down list using the range specified). Since these cells could
contain formulas, imagine how powerful this can be!
The Input Message tab lets you set a message, with a bold title, that
appears as a tooltip when the cell is selected.
On the Error Alert tab you specify a message that tells the user what
they have done wrong. It appears when they enter data that does not fit
your criteria. Explain in detail what is required. The "Style"
determines the icon that is displayed: Stop, Warning or Information.
Stop prevents the user from continuing with bad data, the other two let the
user accept bad data and carry on (or try again).
Data validation works independently of whether
you have worksheet protection set on or not. It's good practice, though,
to set the cells where you want to allow data entry with "Locked" off,
and then set Tools > Protection > Protect Sheet ON, as discussed in the
last Tip. Also see it for a discussion of background colour for the
worksheet and unlocked cells
Do
you have one spreadsheet with carefully-set column widths, and you'd like to
easily set other columns (in the same spreadsheet, or another one) to the same
widths? Easy, with Excel!
- Select the columns, the widths of which you want to copy (any cells in
the columns will do)
- Copy (Edit > Copy, Ctrl+C, or use the toolbar button).
- Click in the leftmost of the columns that must get the new widths.
- Edit > Paste Special > Column Widths > OK.
That's all there is to it!
If
you are doing a document like a Bill of Materials that requires square metres or
cubic metres, you can make it look more professional by using the correct
scientific abbreviations: m² and m³. You can either use symbols or
superscripts.
To use symbols (this also works in Word and
FrontPage, and the symbols can be copied into Access):
- Click Insert > Symbol
- On the top left, choose the font you want. On the top right, look
in the "Subset: Basic Latin", between the lower case letters and the
accented ones.
- Click the symbol you want, then the Insert button. Next time it
will appear in the list at the bottom.
To
use superscripts (Word is similar):
- Type your "2" or "3" and select it.
- Click Format > Cells (or press Ctrl+1).
- Tick the "Superscript" checkbox, and click OK.
- This method applies to all superscripts (and subscripts), but will make
your row width a bit higher.
If you want Excel to check your figures and
point out mistakes for you, try an IF formula. For example, in our
free Bank Reconciliation spreadsheet –which you can get by just visiting
our
web site
and sending us an e-mail– we compare the balance copied from your
internet banking (column E), with the running total in the spreadsheet
(column F). This effectively warns you if you have missed an item
or copied it twice.
The formula is:
=IF(E18=F18,"ok","???")
If the two figures are identical, it
displays "ok", otherwise "???".
How this works: The syntax of the IF function is
IF(comparison, true part, false part). If the comparison is
True, you see true part in the cell (in this case, “ok”),
otherwise you see false part (in this case, three question
marks). Note that strings (e.g. “ok”) have to be inside double
quotes. You could of course instead use a cell reference, a
number, or even another function.
In the spreadsheet we actually use two
nested "IF" functions, like this (blue
part as above):
=IF(E18="","",IF(E18=F18,"ok","???"))
This allows us to copy the formulas down
below the data we have copied from internet banking, without it
displaying question marks. See if you can work out how it works!
Next Tip:
Totalling a range of figures that meet a particular condition!
Last
Tip we looked at the IF function (Syntax:
IF(comparison, true part, false part) ) used to return one of two values
(or formulas) based on a comparison.
Now consider this scenario: You have your bank
statement on
Excel, imported from Internet Banking. In a column next to the data, you
put in an allocation –for example Groceries, Bank Charges, Municipal, Vehicles,
etc. Of course you know how to use =SUM() to get a total for all the
figures, but what if you want separate totals for each allocation? You use
=SUMIF.
The syntax is:
=SUMIF(range, criteria
[,sum_range])
Range is the range of
cells you want Excel to check for the Criteria, which can be any logical
expression (in quotes), text, or a cell reference.
Sum_range is the range of cells you want
Excel to sum. [sum_range
is shown in brackets to indicate that it is optional. You don't
enter the brackets.] If you leave out
sum_range, Excel will sum
Range.
How to use this: Let's say that you have the statement
on a sheet named "April", containing the columns A: Date, B: Description, C:
Amount, D: Balance, and E: Allocation. You want to sum the amount from
Column C for each allocation in Column E. We'll assume the headings are in
row 1 and the data starts in row 2.
Insert a new sheet, let's call it Totals, and
create two column headings, in A1: Allocation, in B1: April.
From cell A2 downwards, list your allocations, one per cell, in column A.
In cell B2, enter the formula: =SUMIF(April!$E$2:$E$1000, Totals!A2,
April!$C$2:$C$1000)
This tells it to look on the April sheet in the
range E2:E1000 (expand if necessary) for the allocation (criteria) in
A2 (the top allocation) and, where the criteria matches, sum the value in column
C (sum_range C2:C1000).
Copy this formula down as far as your allocations
go, and you will get a total for each allocation.
Bonus: As a check that you have not missed
any allocations (or added new ones later!), it is a good idea to do a SUM of the
SUMIF formulas, then compare this with =SUM(April!$C$2:$C$1000) which is of
course the sum all the amounts. Can you think of a formula that will do
the check for you and display either "OK" or "*** Error! Amounts not Allocated!
***" ?
Next
Tip: Counting a range of figures that meet a
condition.
Last Tip we looked at the SUMIF function (Syntax:
SUMIF(range, criteria
[,sum_range]) ) to sum values that match a criterion.
You can also count the
values
that match a criterion: Use COUNTIF.
The syntax is:
=COUNTIF(range,
criteria)
Range is the range of
cells you want Excel to check for the Criteria, which can be any logical
expression (in quotes), text, or a cell reference.
How to use this: Let's say that you have a bank
statement on a sheet named "April", containing the columns A: Date, B:
Description, and C: Amount. You want to count the number of credits, that
is the number of positive amounts in Column C. We'll assume the headings
are in row 1 and the data starts in row 2.
Insert a new sheet, let's call it Statistics, and create a column heading
in B1: April.
In cell B2, enter the formula: =COUNTIF(April!C2:C1000, ">0")
This tells it to look on the April sheet in the
range C2:C1000 for the criteria that the number is positive (">0" -
note the quotes!) and, where the criteria is true, count it.
Last Tip we looked at the
COUNTIF function (Syntax:
=COUNTIF(range,
criteria) ) to
count the
values that match a criterion.
To complete our knowledge of the "counting" functions, this
edition we will look at three simpler ones:
COUNT(Range) counts
the cells that contain numbers.
COUNTA(Range) (Count
All) counts the cells that are not blank (contain numbers or text).
COUNTBLANK(Range)
counts the cells in the range that are blank (empty). The formula counts
any cell that looks empty if unformatted, including formulas that return
empty strings (""), and blank strings (single quote in cell), but not zeros.
The
range may be a single range or several ranges or cells separated by commas
(e.g. C2:C10,D12,E2:E10 ). In the first two functions you can also put in
values instead of ranges and they are counted appropriately.
| Syntax: |
FLOOR(number, multiple) |
| |
CEILING(number, multiple) |
FLOOR truncates "number"
down (toward zero) to the next lower multiple of "multiple".
CEILING truncates "number" up (away from zero) to the next
higher multiple of "multiple".
Unlike other
rounding functions, these two allow you to use any multiple, so
you are not restricted to whole numbers. You can "round" to
multiples of 0.065 or 100 000 (the latter is useful if you run a
shop in Zimbabwe). Bear in mind that this is not really
"rounding", since the result is always down for FLOOR, or
up for CEILING –it does not go to the nearest
value as ROUND would do, and it is taking it to a multiple.
Example:
You need 11.03 square metres of tiles, but they are sold in
boxes of 0.4 m². How many square metres must you buy?
Answer: =CEILING(11.03, 0.4) =11.2
(A pundit might say, obviously these are CEILING tiles, not
FLOOR tiles!)
One quirk:
Both "number" and "significance" must be of the same sign.
=FLOOR(-23, -2) works (-22), but =FLOOR(-23, 2) gives #NUM!
| Syntax: |
EVEN(number) |
rounds "number"
up (away from zero) to the next even whole number. |
| |
ODD(number) |
rounds "number"
up (away from zero) to the next odd whole number. |
Of "EVEN", Microsoft Help says: "You can use this function
for processing items that come in twos. For example, a packing
crate accepts rows of one or two items. The crate is full when
the number of items, rounded up to the nearest two, matches the
crate's capacity."
Microsoft Help
doesn't give an example of where you
might use the latter function. Which is odd, if you think about
it.
Our
contrived Example: The Odd Fellows Hiking Club hiked
upstream on Sunday 7 January 2007, downstream the next Sunday,
and has alternated since then. We want a formula that will give
the direction of next Sunday's hike. On a blank spreadsheet,
copy the following into the indicated cells:
| Cell |
Formula |
Explanation |
| A2: |
=TODAY()-WEEKDAY(TODAY())+8 |
Date next Sunday |
| A3: |
=A2-DATE(2007,1,7) |
Number of days since first hike |
| A4: |
=IF(ODD(A3)=A3,
"downstream", "upstream") |
Direction |
We looked at ODD last month, but these functions are odder!
We haven't found a use for them but, who knows, you might! Let
us know if you do!
| Syntax: |
Description |
Example |
| =ROMAN(number) |
Displays the number in roman
numerals. Various styles are available. |
ROMAN(1984) = MCMLXXXIV |
| =FACT(number) |
Returns the factorial of a
number. The factorial of "n" is equal to 1*2*3*...* n. |
FACT(4) = 24 |
|
=PRODUCT(number1, number2,...) |
Multiplies all the numbers given as arguments and
returns the product. Why not just enter number1 *
number2 * number3…? You might want to use an array:
=PRODUCT(A2:A8) is slightly less typing than
A2*A3*A4*A5*A6*A7*A8 |
PRODUCT(1,2,3,4) = 24 |
|
=COMBIN(number, number chosen) |
Number of combinations for a given number of items.
Use COMBIN to determine the total possible number of
groups made up from a given number of items. E.g. Given
15 Proteas in the squad, how many different cricket
teams could you make? |
COMBIN(15,11) = 1365 |
|
=PERMUT(number, number chosen) |
Number of permutations of "number
chosen" objects that can be selected from "number"
objects. A permutation is a set of objects or events
where the order is significant. E.g. Given 15 Proteas,
how many batting orders could you have? |
PERMUT(15,11) = 54486432000 |
And you thought Mickey Arthur had an easy job!
We have previously mentioned the function =ROUND(number,
digits), which rounds a number to a given number of digits. It
always rounds to the nearest number that has the required number
of digits. E.g. ROUND(3.1415, 3) = 3.142, ROUND(-3.1415, 3) =
-3.142, and ROUND(3141.59, -2) = 3100.
Here are two lesser-known functions that
always round in the same direction:
| Syntax: |
Description |
Example |
| =ROUNDUP(number, digits) |
Rounds a number up (away from zero) to the given number of digits. |
ROUNDUP(PI(), 2) = 3.15 |
| =ROUNDDOWN(number, digits) |
Rounds a number down (towards zero) to the given number of digits. |
ROUNDDOWN(PI(), 2) = 3.14 |
Both behave like ROUND, except that ROUNDUP
always rounds a number up and ROUNDDOWN always rounds a number
down. As with ROUND, a negative value for digits works too: -1
rounds to tens, -2 to hundreds, etc.
As with last
month's functions, haven't found a use for these two –but you
might!
Try Tools > Options > Edit
tab > Fixed Decimal. When turned on, this allows you to
enter numbers without the decimal. For example, if under this
option, "Places" is set to 2, you can enter 1054 and get 10.54
in the cell. It saves hunting for the full stop if you are
entering a lot of numbers that have cents anyway.
It can also be hell to find if you happen to
turn it on by accident and then forget what you did, as a
customer of our found recently! Or if a co-worker decides to
play a practical joke on you and you don't know about this!
Suddenly all the numbers you enter are being divided by 100!
Fortunately, if you type the decimal point, Excel still respects
it.
If you
frequently Paste Special > Values, or Paste Special > Formats,
etc. you can speed up your work by adding these features to your
toolbar. Do the following:
- Make sure the
"Standard" toolbar is visible: Click on the grey
area to the right of the Help menu, and on the
pop-up menu click "Standard" if it not already
ticked.
- Click again on the
grey area to the right of the Help menu, and on the
bottom of the pop-up menu click "Customize".
- The "Customize"
dialog box pops up. On the second tab, "Commands",
under Categories on the left. click "Edit".
- Scroll done in the
right-hand list until you see "Paste Formatting"
(see illustration >>).
- Drag "Paste
Special", "Paste Formatting", "Paste Values" and
anything else you use frequently, onto the Standard
toolbar, probably best next to the Copy button.
- Click Close when
done.
The toolbar will look like this when you are finished
(new items circled):
| |
|
Excel formulas are very useful to
calculate text that you can copy into other programs.
For example, we go walking on the
Sandspruit most Sundays (and you are welcome to join us, just
phone first). I want to make up a list of Sundays with their
dates to put into an e-mail and on the web site.
Start by entering a known Sunday, say 10
Feb, into cell A2. Press Ctrl+1 to get Format > Cells. On the
Number tab, choose Custom, and type in the format dddd, d
mmmm yyyy – this formats the cell as Sunday, 10 February
2008.
In cell A3, add 7 days by entering the
formula: =A2+7. Excel is clever enough to format this
the same as cell A2.
Click on cell A3, grab the little plus
sign (+) at the bottom right-hand corner and drag down as far as
required. Your results, ready to copy and paste into any other
program, will look like this:
| Hike Dates |
| Sunday, 10
February 2008 |
|
Sunday, 17 February 2008 |
|
Sunday, 24 February 2008 |
|
Sunday, 2 March 2008 |
This tip is intended
to stimulate you into thinking about ways that you can use Excel
to do your other work more efficiently, for example by avoiding
unnecessary re-typing.
Last time we used a
simple formula with formatting to produce a table like this that
we could copy into a Word document, an e-mail or a web page:
| Hike Dates |
| Sunday,
10 February 2008 |
|
Sunday, 17 February 2008 |
|
Sunday, 24 February 2008 |
|
Sunday, 2 March 2008 |
This time we will
take it a little further. Staying with our example of Sunday
hikes, we walk upstream one weekend and downstream the next.
The easiest way to add this is to use another column on the
right, headed "Direction", and in the two cells under this, the
two directions. The trick here is to select both
directions, grab the little "+" at the bottom right-hand corner
of the selection, and drag it down as far as required. The
two-cell pattern repeats. Your results should look like this
(dates adjusted for the new month):
|
Hike
Dates |
Direction |
| Sunday,
9 March 2008 |
downstream |
|
Sunday, 16 March 2008 |
upstream |
|
Sunday, 23 March 2008 |
downstream |
|
Sunday, 30 March 2008 |
upstream |
|
Sunday, 6 April 2008 |
downstream |
|
Sunday, 13 April 2008 |
upstream |
While you are
unlikely to use this exact example, we hope this tip will help
to stimulate your thinking about ways of using Excel to do your
other work more efficiently by avoiding unnecessary re-typing.
Last time we used an
example with a simple formula to give us two columns, one with
dates and one with our walking direction (upstream and
downstream). Now we will expand on this by using formulas.
To recap: We
have column headings "Hike Dates" in A1 and "Direction"
in B1. In cell A2 we entered the date of a known Sunday. Using
Ctrl+1 (Format > Cells) on the Number tab under Custom, we used
the format dddd, d mmmm yyyy to format the cell. In cell
A3, we added 7 days using the formula: =A2+7, and copied
it down as far as needed. We then alternated the words "upstream"
and "downstream" in column B.
Now put a new column
heading "Date and Hike Direction" in cell C1. In cell C2, enter
the formula =A2&B2 to combine the values from the two
cells.
Not very satisfactory, is it? The problem is that Excel uses
the numeric value of the date, not the formatted string we see
in the cell.
Replace the formula
in cell C2 with this one: =TEXT(A2,"dddd, d mmmm yyyy")&B2
The TEXT function formats the date value to look like a
date.
One further thing is
needed: We need a space between the two values. Replace the
formula in cell C2 with this one: =TEXT(A2,"dddd, d mmmm yyyy")&"
"&B2
We use the ampersand ("&") to concatenate
(join) strings, to put a string consisting of a space
(surrounded by double quotes to show that it is a string) in
between the two parts of the formula. Our results look like
this, with the third column ready to be copied into an e-mail or
Word (dates adjusted for the new month):
| Hike
Dates |
Direction |
Date and Hike Direction |
|
Sunday, 13 April 2008 |
downstream |
Sunday, 13 April 2008 downstream |
|
Sunday, 20 April 2008 |
upstream |
Sunday, 20 April 2008 upstream |
|
Sunday, 27 April 2008 |
downstream |
Sunday, 27 April 2008 downstream |
|
Sunday, 4 May 2008 |
upstream |
Sunday, 4 May 2008 upstream |
|
Sunday, 11 May 2008 |
downstream |
Sunday, 11 May 2008 downstream |
As you know,
we write computer programs. We often need to produce repetitive
code, with small changes from line to line. Excel
formulas are very useful for this. For example, I need to
produce ten lines like this: We$ = We$ & Range$("fieldname",
Me.fieldname) where fieldname is the name of ten
different fields, one on each line.
With Excel this can be done: List the
field names in (say) column B starting at cell B2. In C2, put
the formula =" We$ = We$ & Range$(""" & B2 & """, Me." & B2 &
")" and then copy it down as far as required. It's a
formula, so it starts with =. This is followed by a
double-quote (") because the formula uses strings (text) instead
of numbers. Wherever I want the variable (fieldname) to
appear, I replace it with " & B2 & " – the first double
quote ends the string, the last double-quote starts a new one,
the B2 refers to that cell, and the ampersands (&) concatenate
(join) the strings. Finally the formula ends with a
double-quote to end the final string.
Select the formulas, copy, and paste into
our program.
Notice that in our result we want the
first fieldname in double quotes (" "), but we are
already using double-quotes enclose stings. To show that we
actually want the double-quote character, and we are not
starting or ending a string, we double up the
double-quotes ("").
Kingsley
writes: Please tell me how to protect a column on Excel.
Answer: You can't protect a column
as such. What you do is to (1) Unlock cells in which you want to
allow input, and then (2) Protect the worksheet, which then
allows changes to the unlocked cells only. To do this, you:
-
Select cells for which you want to
allow data input. Click Format > Cells > Protection
tab: CLEAR the checkbox "locked". Click OK.
Repeat for all input cells.
-
Click Tools > Protection > Protect
Sheet. Type a password (optional) if you want extra
security. Click OK.
-
If you do not want to allow people to
add or remove sheets from the workbook, also use Tools >
Protection > Protect Workbook.
When using SUM in Excel you can include random fields by holding
down the Ctrl key and clicking on each field. Very
useful. [by Dan Elliott of Data Solution Services: dan.dss
AT webmail.co.za]
Would you like to add a tip of your own (due acknowledgement
will be given!) –
click here.
See also:
Excel
Programming Tips,
Microsoft Word
Tips,
Microsoft Access Tips,
Maximizer
Tips,
Tips on Windows
and other Windows Programs.
|
Press Ctrl+F to search this page for keywords.
|
|
 |
