A little high a little low, but how do you know? A little conditional formatting can help determine who falls where.

Example: Suppose you were assigned to test employees on computer skills and you wanted to know who received above-average scores. If you listed the scores next to the employee number in range H2:H36 then this is how we do it:

1. Select L2:L24.
2. Go to Format: Conditional Formatting.
3. Click the drop-down arrow and select Formula Is.
4. Enter the following formula: =L2>Average($L$2:$L$24)
5. Click the Format button.
6. Select Red in the drop-down Color menu.
7. Click OK twice.

All above-average scores will be red.

Note: When entering the formula in step 4, ensure that the cell references in the Average function are absolute.

While Excel's AVERAGE function ignores blank cells, it doesn't ignore cells that contain 0 (zero). This can result in inaccurate analysis of the data.

Example, let's say you want to calculate the average score for all students who took the final exam. Using the AVERAGE function, you might enter: =AVERAGE(B3:B23).

But if two students were absent and received a grade of 0, the result of the formula wouldn't give a true picture of the average grade. However, you can calculate the average and exclude the absentee students' grades, and this is how:

1. Enter the following formula: =AVERAGE(IF(B3:B23<>0,B3:B23))
2. Press [Ctrl][Shift][Enter].

The entered formula first creates an array that includes only nonzero values in the range. The AVERAGE function then uses this array as its argument.

A chart is supposed to take all of your important information and put it into one, easy-to-read form. If you have an overwhelming amount of hard-hitting information, but no way to show it effectively, your point can be lost on your audience.... 

If your business' profit just skyrocketed in the last year and your chart doesn't show this at a glance, it may not be effective at all, and ultimately, all the time you spent preparing for and giving your presentation has been wasted. When creating a chart, there are three important questions you must ask yourself.

Is the chart type appropriate for my data?

Are all elements clearly labeled?

Does the design distract or confuse my audience?

Here, we'll explore the different chart types and answer these questions so you can create what's considered a good chart to get your point and data across effectively.

Did I use the right chart?

Most likely, if you don't understand the data on your chart or how to read it, your audience will be equally confused.  Don't get carried away with fancy charts just because you think they might look better.  A chart that is clear, and accurate, will always do the job better than a chart that looks great, but is confusing or misleading. 

In order to guarantee that your chart is effective, you first need to check if the format you chose is appropriate for your data.

Columns, bars, or pie charts are generally a safe bet.  They're easy to decipher and people are familiar with reading them. They are great for showing differences in size or growth.

However, when using a pie chart, be careful.  Microsoft® Office Excel® will allow for functions that are irrelevant with that chart type, including plotting negative values. Pie charts are useful for showing how a total value, such as budget forecasts, breaks down into categories, but not for showing changes or comparisons.

Did I clearly label all my elements?

For your audience to be able to read your chart at a glance, all the parts need to be labeled clearly and concisely.  The three main parts of a chart are the

Headline: should be descriptive and should not cause the audience to guess what the chart is going to be about. 

Example: if you're talking about XYZ Company's profits for the year 2007, you wouldn't just write “Profits” as the headline.  You would write "XYZ Company Annual Profits for 2007".

Data: should be accurate so as to not mislead the audience.

Legend:  should either be color coded or use symbols to differentiate between the data being shown.

 
Is my design or color palette distracting?

Data can be enhanced with data graphics and a pleasing yet still readable color palette. A data graphic presents your data as a combination of textual and visual elements, such as flags and progress bars and enhances your data by applying shapes to show data that the shape contains.

To ensure that your color palette doesn't take away from the overall information of your data, stay away from bright colors that distract the eye and are hard to read.  Typical colors that are successful and easy to read are shades of blue, orange, green, black, or red.  Learn how you can print your graphs clearly in color or black and white helping you ensure even more success.  
 
The good the bad and the just okay

Some graphs can be disguised as good, but they are actually either misleading or just plain nonsense.

Misleading Chart:  Even if data is accurate, a chart that misleads can cause data too look much more or less impressive than it really is.

Tip: Take a step back and see if the chart is explaining the exact point you are trying to make. For example, if your sales figures are showing only an increase of 1% (which to you is not a lot) and your y-axis is set at a default that looks zoomed in too closely, you could be creating a visual story that your sales figures are higher than they actually are. View an example of a misleading visual based on y-axis figures not set to show the bigger picture. To update this view (in Excel 2007) use axes tools on Chart tools / Layout tab. On the y-axis, set the minimum value to a more appropriate one.

Nonsense Chart: Even if a chart was easy to create, if it doesn't make sense or is irrelevant, your chart is classified as a nonsense chart.

Remember what makes a successful chart

Good Chart:  A chart is classified as good if it is easy to read and understand. It has a descriptive title, a data table that shows the data plotted on the chart, and the legend.

Complex yet Clear Chart:  This chart may seem daunting to create because of all the data included but you can easily create a clear chart with complex data just by adding a second axis.

Paying attention to factors that affect the effectiveness of your chart makes it easy to create a visually pleasing addition to your presentation.

 

Excel includes a number of functions that allow you to remove printable characters, like spaces, from imported data. But what if your data contains nonprintable characters, like the ones shown in Figure A?

Figure A

To clean up this data, Excel provides the CLEAN function. Follow these steps:

1. Select C2:C10.
2. Enter the following formula:
   =CLEAN(A2)
3. Press Ctrl + Enter (Figure B).

Figure B

Excel displays the contents of the cells without the nonprintable characters. You can now copy the results to another part of the workbook using the Paste Special option to copy the values without the formulas (Figure C).

Figure C

---

Sometimes, you may need to replace a formula with its results - either to preserve a static value or to optimize your sheet by reducing calculations. There’s a pretty simple trick for this, but a word of warning: Be sure you really want to wipe out a formula before you do it. (There could be undesirable consequences.)

In fact, a good practice is to create a backup copy of the workbook as a safety net in case things go awry.

To convert a formula, click in its cell and press [F2] to enable in-cell editing. Next, press [F9] to calculate the formula and display its results. Then, hit [Enter], and your formula will be replaced by the value it produced.

You can also copy the formula and use Paste Special | Values to paste the results someplace else, leaving the formula intact in its original location.

When you copy a formula from one cell to another, Excel will, by default, readjust the formula cell references to reflect the change. However, if you want the formulas to stay the same, this is how we do it:

1. Click on the cell containing the formula you want to copy.

2. Press [F2].

3. Click and drag to select the entire formula.

4. Click the Copy button in the Standard toolbar.

5. Press [Enter].

6. Select the cell into which you want to paste the formula.

7. Click the Paste button in the Standard toolbar.

When you copy an Excel formula in this manner, the formula copies as text and will not adjust its cell reference. You can also copy part of a formula this way. For example, you may want to include the formula as part of an If statement in another part of the worksheet.

'When concatenating date and text cell data in an Excel formula, you need to convert date cells to text to avoid unexpected results. If not, you end up with an unexpected result. Example, suppose cell H11 contained the text Due Date: and cell H12 contained a formula that calculated the date. H12 is correctly formatted for the date data type, m/d/yyyy. If you use the formula =H11&H12 to concatenate these cells, the result comes back with the serial date (such as, Due Date: 39054).

Because Excel ignores the formatting of H12, Excel returns the serial date unless the contents of H12 are converted to text before concatenating, as shown in the following formula:

=H11&TEXT(H12,\" mmmm d, yyyy\")

The correct result of this formula is Due Date: December 3, 2006.

For some strange reason, Excel likes to import fractions as dates. Example: let’s say you are want to import a column of data from an Access database table that contains fractions such as 21/25, 1/25, 2/35, and 30/61. You will need to format the column of cells containing the fractional data to an appropriate fraction format before importing, and this is how we do it:

1. Select the column that will contain the fractional data.
2. Right-click the selection.
3. Select Format Cells.
4. In the Number tab, under Category, select Fraction.
5. Under Type, select Up To Two Digits (21/25), then click OK.

When you import the data to that column, Excel will format each entry as a fraction. Keep in mind that if the data contained fractions such as 30/750, you would select Up To Three Digits (312/943) in Step 5. If you wanted to display your data as fractions over 100 (for example, 30/65 displayed as 46/100), you would select As Hundreds (30/100) in Step 5.

If you won’t be using the fractions in calculations, you can prevent Excel from changing your entries to dates by applying the Text format to the cell before you enter the data. In that case, in Step 4 select Text under the Category list and then click OK.

To me this is kind of lame, but it’s the best Excel can do!

Tired of typing the same list of items across a number of Excel cells for each worksheet you add

to your workbook? Then learn how to create a named formula that will enter the data for you.

 
Example, suppose you need to type the names of your Sales Regions in row 2 of every

worksheet. First, create the named formula by following these steps:

1.  Press [Ctrl][F3]. 
2.  Enter SalesRegions in the Names In Workbook Text box. 
3.  In the Refers To text box, enter the following array formula:                           
     ={\"North West\",\"NorthEast\",\"SouthEast\",\"South West\"}

4.  Click Add and then click OK. 

Then, in order to add this list to row 2 of your worksheet, select the range B2:E2, type

=SalesRegions in the formula bar, and then press [Ctrl][Shift][Enter].

You can also use this method to enter labels down a column. However, instead of using a comma

to separate the regions, you would use a semicolon, as shown below:

={\"North West\";\"North East\";\"South East\";\"South West\"}

 

If you do not apply a fraction format to a cell before you enter a fraction into it, Excel will convert the fraction into a date. But that’s not what I want; I want a fraction...

For example, if you enter 1/50 into a cell, Excel immediately converts it to Jan-50 . If you then try to reformat the same cell to a fraction, Excel converts it to 18264 , the internal number that represents Jan-50. In that case, if you want Excel to recognize your cell entries as fractions, you must apply the appropriate fraction format to the cell before you enter the data.

For example, say you are importing a column of data from an Access database table that contains fractions such as 1/50, 2/70, and 30/65. You will need to format the column of cells containing the data to an appropriate fraction format before importing. Follow these steps:

1. Select the column that will contain the fractional data.
2. Right-click the selection.
3. Select Format Cells.
4. In the Number tab, under Category, select Fraction.
5. Under Type, select Up To Two Digits (21/25), then click OK.

Now, when you import the data to that column, Excel will format each entry as a fraction. Keep in mind that if the data contained fractions such as 30/750, you would select Up To Three Digits (312/943) in Step 5. If you wanted to display your data as fractions over 100 (for example, 30/65 displayed as 46/100), you would select As Hundreds (30/100) in Step 5.

Alternately, if you won’t be using the fractions in calculations, you can prevent Excel from changing your entries to dates by applying the Text format to the cell before you enter the data. In that case, in Step 4 select Text under the Category list, and then click OK.

If you do not apply a fraction format to a cell before you enter a fraction into it, Excel will convert the fraction into a date. But that’s not what I want. I want a fraction...

Have you ever set up a worksheet using one structure that seems to make sense, only to realize it would make a whole lot more sense if the rows were columns and the columns were rows. Microsoft makes it easy with the Transpose option to facilitate the necessary flipping of data from Rows to Columns OR from Columns to Rows, and this is how we do it:

1. Select the range of cells you want to transpose and click Copy or press [Ctrl]C.
2. Click in a new location (not overlapping your selection).
3. Go to Edit | Paste Special and select the Transpose check box. In Excel 2007, click Paste in the Clipboard group of the Home tab and select Transpose.
4. You can then delete your original, wrong-structured data.

VLOOKUP searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find.

VLOOKUP will work with a list where the table arguments are sorted, and you will get the closest match to a table argument that does not exceed your lookup value.
(for sorted lists use TRUE or default for a *close* match)

Syntax: (As always look in HELP for more information)
     VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
     range_lookup can be TRUE or FALSE, if omitted the default is TRUE

VLOOKUP will work with a list where the arguments are unordered, and you either get an exact match or fail with #N/A!.
(Whether sorted or not when an *exact* match is required so is the use of FALSE)

To suppress N/A errors:
=IF(ISNA(VLOOKUP(...,...,...,False)),"Item not found",VLOOKUP(...,...,...,False))

A cause of #VALUE! error is a zero value for col_index_num value in the function.

Do not mix cells defined as numbers with cells defined as text in the argument column of your table.  Some tips on determining data type and actual content of your data.  Your table must be consistent, but your lookup value can be forced to look like the table by using one or the other of these tricks (Peo Sjoblom 3003-01-15).

        =VLOOKUP(TEXT(A1,"00000"),Table,2,FALSE)

     or =VLOOKUP(A1+0,Table,2,FALSE)

This is the simplest example that I can come up with.  Note the use of TRUE in the formulas indicating that the value found in the table does not have to be an exact match but must be less than or equal to the lookup_value used.  For VLOOKUP the first column of the range is the used to match the argument, the 2 used in the example indicates to return the second column of the table.  Since TRUE is used an exact match is not required, but because an exact match is not equired, the table must be in ordered in ascending order to obtain the correct result.  If VLOOKUP can’t find lookup_value, and range_lookup is TRUE, it uses the largest table argument value that is less than or equal to lookup_value.

VLOOKUP To Find Your Perfect Match

Using the FALSE argument in Excel can help you find a true match in a lookup table, while eliminating a lot of the confusion that is common with such comparisons. However, you can use the FALSE argument to your advantage.

 

Sometimes lookup tables do not require an exact match. Example, to find shipping costs, the cost of the item must fall within a certain range. However, some situations, such as one that matches up an ID number with a particular person's name, require an exact match. The lookup formula in this instance requires the FALSE argument at the end of the statement.

Suppose you have a list of employee ID numbers in A1:A100; the list of their corresponding names is in B1:B100. To create a lookup formula that looks for an exact match to an employee's ID number, follow these steps:

1. Select A1:B100.
2. Click in the Name box of the formula bar and enter Employee_List.
3. In D1, enter What is the employee ID number?
4. In D2, enter The employee's name is.
5. In E2, enter the following formula:
6. =IF(ISNA(VLOOKUP(E1,Employee_List,2,FALSE)),"Not
   Found",VLOOKUP(E1,Employee_List,2,FALSE))

When you enter an employee ID number from Employee_List in E1, the name of the employee will appear in E2. If no exact match appears, Excel will display "Notfound" in E2. Note: By placing the FALSE argument in the VLOOKUP formula, you do not need to sort the list of employee ID numbers.