Archive for MS Excel 2010

Visually analyse data with Conditional Formatting

Applying conditional formatting allows you to change the format of the cells in your spreadsheet, depending on certain conditions. For example, you could highlight cells in a particular colour when the numbers in those cells match a certain value. This is set using a type of conditional formatting, “Highlight Cells Rules”.

Types of conditional formatting

Conditional formatting can be found on the Home tab of the ribbon. If you click on the conditional formatting button, you can then choose the type of conditional formatting to apply:

  • Highlight Cells Rules
  • Top/Bottom Rules
  • Data Bars
  • Colour Scales
  • Icon Sets
  • New Rule (create custom rules)

Highlight Cells Rules

Looks for cells containing data (numbers, text or dates) that you want to apply formatting, based on criteria, or can be used to find duplicate values
Numbers – Use the ‘Greater Than’, ‘Less Than’, ‘Between’ or ‘Equal To’ options to set the required values.
Text – Use the ‘Text That Contains’ option to type in required text.
Dates – Use the ‘A Date Occurring’ option to set a date comparison to the current date e.g. Yesterday or Next Week. Note; to set a specific date or an alternative comparison, you will need to set a custom rule (see ‘New Rule’ below)
Duplicates – Use the ‘Duplicate Values’ option to highlight all of the duplicate cell values in the selected range of cells.

Top/Bottom Rules

Looks for highest and lowest values based on an amount, percentage or variance from average.

Data Bars

Applies formatting to all selected cells. The longer the bar, the higher the value.
This example uses the blue coloured bars to visually display the size of the cell values.

Colour Scales

Applies formatting to all selected cells. Certain colours will represent higher values than other colours.
The Green-Yellow-Red option can be used to graphically display the size of the cell values e.g. Red is used to highlight the low values, going up to orange, then yellow, light green the dark green to display the highest values.

Icon Sets

Applies formatting to all selected cells. Each icon set has between 3 and 5 categories, each representing low, middle or high values.
An example is the 3 arrow icon set. The red down arrow representing the lowest values, the orange right arrow for the middle values and the green up arrows for the highest values.

New Rule

You can create customised rules, rather than using the pre-set rules, so you can use different colours/formatting options and also use formulas for your criteria.
Example – using a formula to highlight dates which are more than 1 week ago (where A2 is the first date):

  1. Go to Home tab >Conditional Formatting
  2. Select ‘Use a formula to determine…’
  3. Type: =A2
  4. Click on Format
  5. Select required formatting, then OK

The formula must always start with ‘=’. The cell reference ‘A2’ is the first cell in the selected range. ‘Today()-7’ represents today’s date less 7 days i.e. more than 1 week ago. If you click on the first cell in the range rather than typing the cell reference (in this example ‘A2’), it will insert ‘$’ symbols in the cell reference to read ‘$A$2’. It will only then compare the data in that one cell, rather than comparing each cell in the range.

Learn more about Microsoft Excel >>

20 Must-know Shortcut Keys in Microsoft Excel

Microsoft ExcelWant to know how to navigate, select, format and perform other simple functions in Microsoft Excel quickly and efficiently? Here are some must-know shortcut keys to improve your productivity…

NAVIGATING IN EXCEL

1. Go to end of work area

Ctrl + End

This shortcut will move your active cell to the bottom right corner of your data.

2. Go to start of work area

Ctrl + Home

This shortcut will move your active cell to the top left corner of your data. This will usually be cell A1, unless you have turned on Freeze Panes, in which case this shortcut will take you to the top left cell of the unfrozen section of your data.

3. Go to previous screen above

Pg Up

This shortcut key will move your active cell up one screen at a time, remaining in the same column.

4. Go to next screen below

Pg Dn

This shortcut key will move your active cell down one screen at a time, remaining in the same column

SELECTING DATA

5. Select multiple individual cells (or ranges of cells)

Ctrl + Click

Holding down the Ctrl key whilst selecting cells (or ranges of cells) allows you to select multiple, non-adjacent cells at the same time.

6. Select a large range of cells

Click + Shift + Click

To select a large range (rectangular area of adjacent cells) click in the cell in the top left corner of the area to select, hold down the Shift key, then click in the cell in the bottom right corner of the area to select.

7. Select the whole spreadsheet

Ctrl + A

This shortcut key will select the entire spreadsheet. Alternatively click on the button to the left of column header A.

8. Select a range of cells

Ctrl + *

This shortcut key selects the current range of cells i.e. a rectangular area of cells with no adjacent data. Note: Use the * key on the number pad of your keyboard. If you do not have a number pad then you will need to use the shortcut Ctrl + Shift + 8. FORMATTING

9. Format data in active cell(s) as Bold

Ctrl + B

10. Format data in active cell(s) as Italic

Ctrl + I

11. Format data in active cell(s) as Underlined

Ctrl +U OTHER SHORTCUTS

12. Create a new line within a cell

Alt + Enter

Using Alt and Enter will create a new line in a cell, just like creating a new line in a document. If you are using Excel 2007 or later version, you will be able to expand the formula bar to view multiple lines in a cell.
13. Open a new workbook

Ctrl + N

14. Open an existing workbook

Ctrl + O

15. Save current workbook

Ctrl + S

16. Print worksheet

Ctrl + P

17. Undo last action

Ctrl + Z

18. Copy selected cells

Ctrl + C

This shortcut key selects cells for copying. The second step is to select where to copy this data to (Paste).

19. Move selected cells

Ctrl + X

This shortcut key selects cells for moving (Cut). The second step is to select where to move this data to (Paste).

20. Paste selected cells

Ctrl + V

Printer friendly version

Logical functions

There are several logical functions in Excel. This guide provides details of the 3 most common logical functions:

  • IF – used to return one value if the result of a test is true and another value if the result of a test is false.
  • AND – used in conjunction with the IF function to allow multiple tests to be performed, then returning one value if ALL the results of the tests are true and another value if the results of ALL the tests are false.
  • OR – used in conjunction with the IF function to allow multiple tests to be performed, then returning one value if ANY of the results of the tests are true and another value if the results of ANY of the tests are false.

IF function

The IF function uses 3 arguments as follows:

Operator Action
Greater than
Less than
= Equal to
<>  Not equal to
<= Less than or equal to
>= Greater than or equal to

 

  1. The test – comparing one value to another using the above operators
  2. The value to be displayed where the result of the test is true
  3. The value to be displayed where the result of the test is false

Example – performing a single test

A worksheet lists sales agent names and their annual sales. If the sales agents’ sales have exceeded a specific sales target, then text is to be displayed as “Exceeded”, otherwise text is to be displayed as “Not Exceeded”.

An IF function can be used to display either text result in column D, based on a test to compare each agents sales to the target of $34,000 in cell E2.

The function in cell D5 would be as follows:

Note that if text is to be displayed it must have the double quotation marks (“”) around the text in the function.

  1. The test comparing the agents’ sales to the target of $34,000
  2. The result to be displayed if the agents’ sales exceed the target
  3. The result to be displayed if the agents’ sales is less than the target

Example – performing more than one test

If you need to perform more than one test when constructing an IF function, you can use either an AND function or an OR function (see above for definitions).

AND function

In the example below we need to create a function that will calculate the agents’ commission, but only if they have a) exceeded the sales target AND b) have a Gold classification:

The formula in cell E3 would be as follows:

  1. Two tests (separate tests by a comma)
  2. The result to be displayed if both the 2 tests are true
  3. The result to be displayed if both the tests are not true

This example would only result in Janet Costas getting commission as she was the only agent to have Gold classification and exceeded the target.

OR function

In the example below we need to create a function that will calculate the agents’ commission if they have EITHER a) exceeded the sales target OR b) have a Gold classification:

The formula in cell E3 would be as follows:

  1. Two tests (separate 1 tests by a comma)
  2. The result to be displayed if either of the 2 tests are true
  3. The result to be displayed if neither of the tests are true

This example would result in Janet, Brian, Norris & Alex all getting commission as these agents have either Gold classification or have exceeded the target (or both).

Printable version