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\"}

 

1. You can build a pivot table in about one minute

Many people have the idea that building a pivot table is complicated and time-consuming, but it's simply not true. Compared to the time it would take you to build an equivalent report manually, pivot tables are incredibly fast.  If you have well-organized source data, you can create a pivot table less than a minute. Here's how:

1. Select any cell in the source data
2. On the Insert tab of the ribbon, click the PivotTable button
3. In the Create PivotTable dialog box, check the data and click OK
4. Drag a "label" field into the Row Labels area (e.g. customer)
5. Drag a numeric field into the Values area (e.g. sales)

Select any cell in the data to start.

A very simple pivot table in about 30 seconds

2. Perfect your source data

To minimize problems down the road, always use good quality source data, organized in a tabular layout. "Perfect" source data will have no blank rows or columns, and no subtotals. Each column will have a unique name (on one row only), every field will have a value in every row, and columns will not hold repeated groups of data (i.e. month names, location names, region names, etc.).

Video: How to quickly fill in missing data

Perfect data for a pivot table!

3. Count the data first

When you first create a pivot table, use it to generate a simple count first to make sure the pivot table is processing the data as you expect. To do this, simply add any text field as a Value field. You'll see a very small pivot table that displays the total record count, that is, the total number of rows in your data. If this number makes sense to you, you're good to go. If the number doesn't make sense to you, it's possible the pivot table is not reading the data correctly or that the data has not been defined correctly.

300 first names means we have 300 employees. Check.

4. Plan before you build

Although it's a lot of fun dragging fields around a pivot table, and watching Excel churn out yet another unusual representation of the data, you can find yourself going down a lot of unproductive rabbit holes very easily. An hour later, it's not so fun anymore. Before you start building, jot down what you are trying to measure or understand, and sketch out a few simple reports on a notepad. These simple notes will help guide you through the huge number of choices you have at your disposal. Keep things simple, and focus on the questions you need to answer.

5. Use a table for your data to create a "dynamic range"

If you use an Excel Table for the source data of your pivot table, you get a very nice benefit: your data range becomes "dynamic". A dynamic range will automatically expand and shrink the table as you add or remove data, so won't have to worry that the pivot table is missing the latest data. When you use a Table for your pivot table, the pivot table will always be in sync with your data.

To use a Table for your pivot table:

1. Select any cell in the data use the keyboard shortcut Ctrl-T to create a Table
2. Click the Summarize with PivotTable button (TableTools > Design)
3. Build your pivot table normally
4. Profit: data you add to your Table will automatically appear in your Pivot table on refresh

Video: Use a table for your next pivot table

Creating a simple Table from the data using (Ctrl-T)

Now that we have a table, we can use Summarize with a  Pivot Table

6. Use a pivot table to count things

By default, a Pivot Table will count any text field. This can be a really handy feature in a lot of general business situations. For example, suppose you have a list of employees and want to get a count by department? To get a breakdown by department,  follow these steps:

1. Create a pivot table normally
2. Add the Department as a Row Label
3. Add the employee Name field as a Value
4. The pivot table will display a count of employee by Department

Employee breakdown by department

7. Show totals as a percentage

In many pivot tables, you'll want to show a percentage rather than a count. For example, perhaps you want to show a breakdown of sales by product. But, rather than show the total sales for each product, you want to show sales as a percentage of the total sales. Assuming you have a field called Sales in your data, just follow these steps:

1. Add Product to the pivot table as a Row Label
2. Add Sales to the pivot table as a Value
3. Right-click the Sales field, and set "Show Values As" to "% of Grand Total"

See the tip below "Add a field more than once to a pivot table" to learn how to show total sales and sales as a percent of total at the same time.

Changing value display to % of total

Sum of employees displayed as % of total

8. Use a pivot table to build a list of unique values

Because pivot tables summarize data, they can be used to find unique values in a field. This is a good way to quickly see all the values that appear in a field and also find typos, and other inconsistencies. For example, suppose you have sales data and you want to see a list of every product that was sold. To create a product list:

1. Create a pivot table normally
2. Add the Product as a Row Label
3. Add any other text field (category, customer, etc) as a Value
4. The pivot table will show a list of all products that appear in the sales data

Every product that appears in the data is listed (including a typo)

9. Create a self-contained pivot table

When you've created a pivot table from data in the same worksheet, you can remove the data if you like and the pivot table will continue to operate normally. This is because a pivot table has a pivot cache that contains an exact duplicate of the data used to create the pivot table.

1. Refresh the pivot table to ensure cache is up to date (PivotTable Tools > Refresh)
2. Delete the worksheet that contains the data
3. Use your pivot table normally

See: How to make a self-contained pivot table

10. Group a pivot table manually

Although pivot tables automatically group data in many ways, you can also group items manually into your own custom groups. For example, assume you have a pivot table that shows a breakdown of employees by department. Suppose you want to further group the Engineering, Fulfillment, and Support departments into Group 1, and Sales and Marketing into Group 2. Group 1 and Group 2 don't appear in the data, they are your own custom groups. To group the pivot table into the ad hoc groups, Group 1 and Group 2:

1. Control-click to select each item in the first group
2. Right-click one of the items and choose Group from the menu
3. Excel creates a new group, "Group1"
4. Select Marketing and Sales in column B, and group as above
5. Excel creates another group, "Group2"

Starting to group manually

Half way through manual grouping - Group 1 is done

Finished grouping manually

11. Group numeric data into ranges

One of the most interesting and powerful features that every pivot table has is the ability to group numeric data into ranges or buckets. For example, assume you have a list of voting results that includes voter age, and you want to summarize the results by age group:

1. Create your pivot table normally
2. Add Age as a Row Label, Vote as a Column Label, and Name as a Value
3. Right-click any value in the Age field and choose Group
4. Enter 10 as the interval in the "By:" input area
5. When you click OK, you'll see the voting data grouped by age into 10-year buckets

The source data for voting results

Grouping the age field into 10 year buckets

Done grouping voting results by age range

12. Rename fields for better readability

When you add fields to a pivot table, the pivot table will display the name that appears in the source data. Value field names will appear with "Sum of " or "Count of" when they are added to a pivot table. For example, you'll see Sum of Sales, Count of Region, and so on. However, you can simply overwrite this name with your own. Just select the cell that contains the field you want to rename and type a new name.

Rename a field by typing over the original name

13. Add a space to field names when Excel complains

When you try to rename fields, you might run into a problem if you try to use exactly the same field name that appears in the data. For example, suppose you have a field called Sales in your source data. As a value field, it appears as Sum of Sales, but (sensibly) you want it to say Sales. However, when you try to use Sales, Excel complains that the field already exists, and throws a "PivotTable field name already exists" error message.

Excel doesn't like your new field name

As a simple workaround, just add a space to the end of your new field name. You can't see a difference, and Excel won't complain.

Adding a space to the name avoids the problem

14. Add a field more than once to a pivot table

There are many situations when it makes sense to add the same field to a pivot table more than once. It may seem odd, but you can indeed add the same field to a pivot table more than once. For example suppose you have a pivot table that shows a count of employees by department.

The count works fine, but you also want to show the count as a percentage of total employees. In this case, the simplest solution is to add the same field twice as a Value field:

1. Add a text field to the Value area (e.g. First name, Name, etc.)
2. By default, you'll get a count for text fields
3. Add the same field again to the Value area
4. Right-click the second instance, and change Show Values As to "% of Grand Total"
5. Rename both fields as as you wish

Setting a field to show percent of total

The Name field has been added twice

15. Automatically format all value fields

Any time you add a numeric field as a Value in a pivot table, you should set the number format directly on the field. You may be tempted to format the values you see in the pivot table directly, but this is not a good idea, because it's not reliable as the pivot table changes. Setting the format directly on the field will ensure that the field is displayed using the format you want, no matter how big or small the pivot table becomes.

For example, assume a pivot table that shows a breakdown of sales by Region. When you first add the Sales field to the pivot table, it will be displayed in General number format, since it's a numeric field. To apply the Accounting number format to the field itself:

1. Right-click on the Sales field and select Value Field Settings from the menu
2. Click the Number Format button in the Value field settings dialog that appears
3. Set the format to Accounting and click OK to exit

Setting format directly on a value field

16. Drill down to see (or extract) the data behind any total

Whenever you see a total displayed in a pivot table, you can easily see and extract the data that makes up the total by "drilling down". For example, assume you are looking at a pivot table that shows employee count by department. You can see that there are 50 employees in the Engineering department, but you want to see the actual names. To see the 50 people that make up this number, double-click directly on the number 50 and Excel will add a new sheet to your workbook that contains the exact data used to calculate 50 engineers. You can use this same approach to see and extract data behind totals wherever you see them in a pivot table.

Double click a total to "drill down"

The 50 Enginners, extracted into a new sheet automatically

17. Clone your pivot tables when you need another view

Once you have one pivot table set up, you might want to see a different view of the same data. You could of course just rearrange your existing pivot table to create the new view. But if you're building a report that you plan to use and update on an on-going basis, the easiest thing to do is clone an existing pivot table, so that both views of the data are always available.

There are two easy ways to clone a pivot table. The first way involved duplicating the worksheet that holds the pivot table. If you have a pivot table set up in worksheet with a title, etc., you can just right click the worksheet tab to copy the worksheet into the same workbook. Another way to clone a pivot table is to copy the pivot table, and paste it somewhere else. Using these approaches, you can make as many copies as you like.

When you clone a pivot table this way, both pivot tables share the same pivot cache. This means that when you refresh any one of the clones (or the original) all of the related pivot tables will be refreshed.

Video: How to clone a pivot table

18. Unclone a pivot table to refresh independently

After you've cloned a pivot table, you might run into a situation where you really don't want the clone to be linked to the same pivot cache as the original. A common example is after you've grouped a date field in one pivot table, refresh, and discover that you've also accidentally grouped the same date field in another pivot table that you didn't intend to change. When pivot tables share the same pivot cache, they also share field grouping as well.

Here's one way to unclone a pivot table, that is, unlink it from the pivot cache it shares with other pivot tables in the same worksheet:

1. Cut the entire pivot table to the clipboard
2. Paste the pivot table into a brand new workbook
3. Refresh the pivot table
4. Copy it again to the clipboard
5. Paste it back into the original workbook
6. Discard the temporary workbook

Your pivot table will now use it's own pivot cache and will not refresh with the other pivot table(s) in the workbook, or share the same field grouping.

19. Get rid of useless headings

The default layout for new pivot tables is the Compact layout. This layout will display "Row Labels" and "Column Labels" as headings in the pivot table. These aren't the most intuitive headings, especially for people that don't often use pivot tables. An easy way to get rid of these odd headings is to switch the pivot table layout from Compact to Outline or Tabular layout. This will cause the pivot table to display the actual field names as headings in the pivot table, which is much more sensible. To get rid of these labels altogether, look for a button called Field Headers on the Analyze tab of the Pivot Table Tools ribbon. Clicking this button will disable headings completely.

Note the useless and confusing field headings

Switching the layout from Compact to Outline

Field headings in Outline layout are much more sensible

20. Add a little white space around your pivot tables

This is just a simple design tip. All good designers know that a pleasing design requires a little white space. White space just means empty space set aside to give the layout breathing room. After you create a pivot table, insert an extra column to the left and an extra row or two at the top. This will give your pivot table some breathing room and create a better looking layout. In most cases, I also recommend that you turn off gridlines on the worksheet. The pivot table itself will present a strong visual grid, so the gridlines outside the pivot table are unnecessary, and will simply create visual noise.

A little white space makes your pivot tables look more polished

Inspiration: 5 pivot tables you haven't seen before.

21. Get rid of row and column grand totals

By default, pivot tables show totals for both rows and columns, but you can easily disable one or both of these totals if you don't want them. On the Pivot Table tab of the ribbon, just click the Totals button and choose the options you want.

You can remove grand totals for both rows and columns

22. Format empty cells

If you have a pivot table that has a lot of blank cells, you can control the character that is displayed in each blank cell. By default, empty cells will display nothing at all. To set your own character, right-click inside the pivot table and select Pivot Table options. Then make sure that "Empty cells as:" is checked, and enter the character you want to see. Keep in mind that this setting respects the applied number format. For example. if you are using the accounting number format for a numeric value field, and enter a zero, you'll see a hyphen "-" displayed in the pivot table, since that's how zero values are displayed with the Accounting format.

Empty cells set to display 0 (zero) and Accounting number format gives you hyphens

23. Turn off AutoFit when necessary

By default, when you refresh a pivot table, the columns that contain data are adjusted automatically to best fit the data. Normally, this is a good thing, but it can drive you crazy if you have other things on worksheet along with the pivot table, or if you have carefully adjusted the column widths manually and don't want them changed. To disable this feature, right-click inside the pivot table and choose PivotTable Options. In the first tab of the options (or the layout tab on a Mac), uncheck "AutoFit Column Widths on Update".

Pivot table column autofit option for Windows

Pivot table column autofit option for Mac

A pivot table is a program tool that allows you to reorganize and summarize selected columns and rows of data in a spreadsheet or database table to obtain a desired report. A pivot table doesn't actually change the spreadsheet or database itself. A pivot table allows you to extract the significance from a large, detailed data set, thus turning "data overflow" into meaningful reports.

Use a pivot table to build a list of unique values. Because pivot tables summarize data, they can be used to find unique values in a field. This is a good way to quickly see all the values that appear in a field and also find typos, and other inconsistencies.

Pivot tables accomplish this task by using "Values Areas". The Values area displays the data (values) that we want to summarize in our pivot table report. When you drag a field into the Values area, the pivot table will automatically sum or count the data in that field. If the data in the field contains numbers, then the sum will be calculated.

Pivot Table Example:

Insert a Pivot Table | Drag fields | Sort | Filter | Change Summary Calculation | Two-dimensional Pivot Table

Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the significance from a large, detailed data set.

Our data set consists of 213 records and 6 fields. Order ID, Product, Category, Amount, Date and Country.

Insert a Pivot Table

To insert a pivot table, execute the following steps.

1. Click any single cell inside the data set.

2. On the Insert tab, in the Tables group, click PivotTable.

The following dialog box appears. Excel automatically selects the data for you. The default location for a new pivot table is New Worksheet.

3. Click OK.

Drag fields

The PivotTable Fields pane appears. To get the total amount exported of each product, drag the following fields to the different areas.

1. Product field to the Rows area.

2. Amount field to the Values area.

3. Country field to the Filters area.

Below you can find the pivot table. Bananas are our main export product. That's how easy pivot tables can be!

Sort

To get Banana at the top of the list, sort the pivot table.

1. Click any cell inside the Sum of Amount column.

2. Right click and click on Sort, Sort Largest to Smallest.

Result.

Filter

Because we added the Country field to the Filters area, we can filter this pivot table by Country. For example, which products do we export the most to France?

1. Click the filter drop-down and select France.

Result. Apples are our main export product to France.

Note: you can use the standard filter (triangle next to Row Labels) to only show the amounts of specific products.

Change Summary Calculation

By default, Excel summarizes your data by either summing or counting the items. To change the type of calculation that you want to use, execute the following steps.

1. Click any cell inside the Sum of Amount column.

2. Right click and click on Value Field Settings.

3. Choose the type of calculation you want to use. For example, click Count.

4. Click OK.

Result. 16 out of the 28 orders to France were 'Apple' orders.

Two-dimensional Pivot Table

If you drag a field to the Rows area and Columns area, you can create a two-dimensional pivot table. First, insert a pivot table. Next, to get the total amount exported to each country, of each product, drag the following fields to the different areas.

1. Country field to the Rows area.

2. Product field to the Columns area.

3. Amount field to the Values area.

4. Category field to the Filters area.

Below you can find the two-dimensional pivot table.

To easily compare these numbers, create a pivot chart and apply a filter. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful pivot table features Excel has to offer.

 

 

Imagine you need to log time for different clients and projects and periodically report your time by client and project. There are of course many applications dedicated to time-tracking, but you can easily create your own flexible system using Pivot Tables.

At a minimum, what you need to record is a date, the time you spent, the client name, and the project. So, after you enter the data consistently, you might end up with source data that looks something like this:

Note that there are no blank lines - you just need to enter the data as you go.

Now the summaries. First, you might want an overview of your time by week. Here we are using the week numbers provided by Excel's WEEKNUM function (see column C of the source data):

You might also want to arrange the pivot table to show a more traditional timesheet layout, with days of the week across the top:

Each time you filter on a different week number, your pivot table will build a new time sheet that displays the dates that belong to the week.

Note that by adding a column for Name to the data, you could track and report time for multiple people. You could also add a rate column to the data and use a pivot table to summarize the cost or billing rate of time logged.

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.