Home | About Us | Feature | Programs | Support | Training | Development | Downloads | Free Tools | Newsletter | Links | Contact

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


Totals are not Exact (Rounding):

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).

Your Own Excel Shortcut Key

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.

Comparing Cells (=)

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.

Introducing =IF:

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.

More on =IF: Nesting

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!

Introducing CHOOSE:

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!

Conditional Formatting 1:

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!

Conditional Formatting 2:

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.

Conditional Formatting 3:

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!

 

Lookups: VLOOKUP and HLOOKUP

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.

Back to Basics: SUM

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) !

Basic Functions: AVERAGE

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).

Basic Functions: COUNT

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.

Copy & Paste Special (Values)

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!

Parsing Data with "Text-to-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.

Numeric Formats

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.

 

Date, Time and other Formats

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.

Special 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.

Format Cells, Alignment

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!

Format Cells, Font / Border / Patterns / Protection

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!

Introducing Data Validation

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

Copy Column Widths

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!

Superscripts

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() Again

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!

SUMIF()

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.

COUNTIF()

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.

COUNT, COUNTA, COUNTBLANK

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.

Unusual but Useful: FLOOR and CEILING

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!

Unusual but Interesting: EVEN and ODD

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

Really Odd Functions

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!

The Cowboy Function, ROUNDUP (and its faithful companion)

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!

A Little Time-Saver: Entering Decimals Without the Point

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.

Add Useful Features to Your Toolbar

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):

 

Using Excel to Produce Data for Other Programs

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

Using Excel Formulas to Produce Data for Other Programs (II)

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

Using Excel Formulas to Produce Data for Other Programs (III)

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 

Using Excel String Formulas to Help Other Programs

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 ("").

Helpline Tip: How to Protect Data in Excel

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:

  1. 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.

  2. Click Tools > Protection > Protect Sheet. Type a password (optional) if you want extra security. Click OK.

  3. If you do not want to allow people to add or remove sheets from the workbook, also use Tools > Protection > Protect Workbook.


Contributed Tips

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.


| Back to top | ©2000-2013 Communication in Action cc t/a Software Africa. All rights reserved.  Updated 19 June 2013 e-mail Webmaster.