Excel 2008

Excellent Basics of Excel Document from Tufts University

What’s New in Excel 2008?

Elements Gallery - The Elements Gallery is located below the toolbars in Word, PowerPoint, and Excel. It provides quick access to some of the most-used features.

Sheets – In Excel, you can find preformatted ledger sheets in the Elements Gallery

Charts - Create modern-looking charts by using new charting templates and tools that include special effects such as 3-D, transparency, and shadows. You can preview and insert any chart directly from the Charts tab in the Elements Gallery. After you create a chart in Excel, you can use it in other Office documents.

SmartArt Graphics - Quickly create designer-quality diagrams, charts, and other information graphics by using SmartArt graphics. Choose from dozens of SmartArt graphics to visually represent lists, hierarchies, and other relationships.

WordArt – Excel includes updated, modern-looking WordArt that you can use to apply 2-D and 3-D effects to text.

Formula Builder - The Formula Builder helps you create formulas in a simple, step-by-step approach. You don't have to memorize functions or their syntax. Use it to create formulas, search for functions, insert functions and arguments in existing formulas, and get help on all Excel functions.

Formula Auto Complete - Write and edit formulas without having to remember function names, defined names, or other elements of a formula. When you type a formula in a cell, you can choose valid functions, names, and named ranges in context.
Named Ranges – Will also appear in the auto-complete list of choices.

Ledger Sheets - Use these preformatted Excel sheets to balance your check book, track an investment portfolio, create an expense report, and perform other common tasks. Each ledger sheet addresses a specific scenario. For example, if you open an invoice ledger sheet, the sheet contains all of the formulas and columns appropriate for tracking and managing invoices.

New Format - The new default file formats, called Open XML Formats, create smaller files and make it easier to recover damaged files and share files. The new file formats, which are used in Word, PowerPoint, and Excel, also help keep your files more secure. Open XML Formats are also the default file formats for the 2007 Microsoft Office system for the Windows operating system.
When you save a Word document (.docx), a PowerPoint presentation (.pptx), or an Excel workbook (.xlsx), Office saves them in the new file formats.
Compatibility Report


Creating Venn Diagram -
  1. Create a new workbook
  2. Select all cells
  3. Format all cells to pattern - white (Format, Cells, Pattern, Choose white)
  4. Draw circles for diagram - Open Toolbox, Object Palette, Draw Circle
  5. Format the circle to have "No Fill" - Format Shape, Fill, No Fill
  6. Copy Circle
  7. Add pictures if you like - Toolbox, Object Palette, Images
  8. Add labels
  9. Color only after all terms have been added.

Site with lots of Data to work with..



Macros have long been a source of virus transmission and other issues for Office users, but in Office 2008, they are simply gone.
Office (2008) no longer includes support for Visual Basic for Applications,

The End of Macros on the Mac

Macros were replaced with Apple Script integration into Office 2008.
Here is a link to some resources for using AppleScript with office 2008

Pivot Tables

Excel Pivot Table Fundamentals

I won't try and hide the fact from you that I am a big fan of Pivot Tables. I use them a lot in the development of Spreadsheets for my clients. Once the client sees the Pivot Table, they nearly always ask "could I do that?" the answer of course is yes! Unfortunately most people tend to shy away from Pivot Tables, as they see them as too complex. to be honest with you, when you first use a Pivot Table the whole thing can seem a bit daunting. Don't be put off by this as persistence will almost certainly pay off. I find the best part about Pivot Tables is their ability to be manipulated via 'Trial-and-Error' and immediately show the result. If its' not what you expect, simply use the Undo featureand/or have another go! What you must always remember is that you are not changing the structure of your original table in anyway at all, so you can do no harm!

Why are they called Pivot Tables ? - Well, basically they allow us to pivot our data via drag-and-drop to produce meaningful information. This makes Pivot Tables interactive in that once the table is complete we can very easily see what effect moving (or pivoting) our data has on our information. This will become patently clear once you give Pivot Tables a go. Believe me, no matter how experienced you get at Pivot Tables there will always be an element of trial-and-error involved in producing the desired results! What this means is you will find yourself pivoting your table a lot!

What would we use them for ?- We would use a Pivot Table to produce meaningful information from a table of information. Imagine you have a table of data that contains names, addresses, ages, occupations, phone numbers, postcodes etc. With a Pivot Table we could very easily and quickly find out:
              1. How Many People Have The Same Names.
              2. How Many Postcodes Are The Same.
              3. A Count Of A Particular Occupation.
              4. See Only People That Match A Particular Occupation.
              5. Find Out The Addresses Of People That Match A Postcode
In fact the list can go on and on!
What is the advantage ? - Perhaps the biggest advantage to using Pivot Tables is the fact that we can generate and extract meaningful information from a large table of information within a matter of minutes. Or perhaps it is because they will not use up a lot of memory from your PC. In a lot of cases we could get the same results from a table of data by using Excels built in functions, but this would take more time and use far more memory. On top of this, if we wanted some new information we can simply drag-and-drop (pivot). We can also opt to have our information update each time we open the Workbook and/or by clicking refresh.
Pivot Charts - In Excel 2000 Microsoft introduced Pivot Charts. These are simply charts that are read from the table created via the Pivot Table Wizard. In fact Pivot Tables are really no longer just Pivot Tables, they are now PivotTables and PivotChart Reports. These two features go hand-in-hand with each other. By this I mean, when you create a Pivot Table you can also create a Pivot Chart at the same time with no extra effort needed on your part. Pivot Charts allow us to create professional interactive charts that were previously not possible without either VBA or using Excel's Controls.

Tables and Lists Guidelines for Pivot Tables

The most important factors when considering using a Pivot Table is to have our data set up in what could be termed as a table and/or list. As our Pivot Table will be basing all its data on this list or table it is vital that we set our tables and lists up in a uniform way.
In the context we are discussing here, a Table is no more than a List (with a heading) with more than one Column of data and a different heading for each column. A List is often referred to in the context of a Table as well. The 'good practice' that applies to setting up a List will aid us greatly when we need to apply a Pivot Table to our Data. When we extract data via the use of Lookup functions or Database functions we can be a little less stringent in how we set up our Table or List. This is because we can always compensate with the aid of a function and probably still get our result. However, having said this we should always set up our List or Table as well as possible. When we use Excels built-in features they will and do assume a lot about the layout and setting up of your data. They do have a degree of flexibility but more often than not you will find it easier to follow the guidelines for setting up your Table or List.
Let's look at what I believe to be the most important aspects of setting up a Table or List.
  1. Headings. This Is A Must! They Should Always Be In The Row Directly Above Your Data. Never Have A Blank Row Between Your Data And The Headings. Make Them Distinct In Some Way Eg; Bold Them.
  2. Leave At Least Three Blank Rows Above Your Headings. These Can Be Used For Formulas, Critical Data Etc. You Can Hide The Rows If You Wish.
  3. If You Do Have More Than One List Or Table On The Same Worksheet Leave At Least One Blank Column Between Your List And Tables. This Will Aid Excel In Recognizing Them As Separate. However, If The Lists And Tables Are Related To Each Other Make Them Into One Large Table.
  4. Avoid Blank Cells Within Your Data. Rather Than Leaving Blanks For The Same Data In A Column Repeat The Data As Many Times As Needed.
  5. Sort Your List Or Data, Preferably By The Left Most Column. This In Not So Much For Pivot Tables, But For The Person Reading The Data.
If we follow these guidelines as close as possible, using Pivot Tables will be a relatively easy task.

external image PivotIntro.gif
The screen shot above shows both a well laid out table of data and the Layout step of the Pivot Table Wizard. Note that many of the same dates are repeated in the "Date" Column. In front of this data is the Layout step for the data showing the optional Page, Row and Column fields and also the mandatory Data field.

Interesting Sites on the shortcomings of Mac Office 2008

Pivot table basics videos

Pivot Table basics Document from tufts University

Amazing Document on Pivot Tables from Tufts University

In Order to use Pivot tables, you need to have several things included in your data file.
Here are some example files for us to work with today!

Pivot Table Basics

What is a Pivot Table?

A pivot table is a table that stores the summary of an underlying data set in a condensed form. A pivot table consists of rows, columns, page and data fields each of which can be moved around interactively and help the user isolate, expand, group and sum the data in real time.

The advantages of Pivot Tables are:

1. Pivot tables are normally used to show the condensed form of a large underlying set and therefore occupy much lesser screen space.

2. Since pivot tables summarize the data, they offer the reader the ability to get a grasp of the data for more quickly than a raw table..

Pivots tables in Excel enjoy the benefit of other complimentary features:

1. Excel offers many pre-defined functions that help the user extract data from pivot tables far more easily than would be possible with the raw table with multiple columns and rows
2. The data processing power of pivot tables is further enhanced by the use for techniques like conditional formatting, grouping and filtering

How to create a Pivot Table in Excel?

Before we begin to use Pivot tables, let us first understand the various parts of a pivot table. A pivot table is based on a collection of data organized as columns (and rows). Each of these columns needs to have a label which acts as the identifier for the entire column. Each of these identifies can be moved around and placed in four “slots”. There are four of these slots in a pivot table:

1. Data or Value Fields – This field, located in the center of the pivot table, is used for aggregating data. Whichever field identifier is placed here is this slot will be aggregated. So for example if you place “Sales” in this field, the individual Sales figures will be clubbed and then presented to the user. The criteria for clubbing are usually specified in the remaining three slots – row, column and Page Fields.
2. Row Fields – When you place a field here, the information in the Data Field (point 1 above) will be aggregated using this field and then shown row wise, one row for each value of the row field. Let’s take the example of Sales figures over a 12 month period. Having placed “Sales” in the Data Field, we can drag and drop the “Month” field in the row slot. The outcome would be that the row field will have as many rows as there are months and against each month, the data field will the show the aggregate for the sales in that month across all categories and managers who made them. If we were to now drag and drop another field, say “Sales Manager” in the row slot, the number of row would increase and now start showing all the unique combinations of month followed by the Sales Manager. Against each of these rows, there would be the aggregate of Sales – but this time, it would be aggregated for a particular Sales Manager and for a particular month. So, as you just saw, the higher the number of fields that we introduce in the row slot, the higher the granularity that we will have for our data. This is an important feature of the pivot table – you can drill-down the each individual data point and yet with a few clicks aggregate thousands of individual data points into a few rows.
3. Column Fields – Similar to row fields but differ in their orientation. While inserting new row fields increases the number of rows that we have in the pivot table, inserting column fields add new columns to it. Any field that is placed in the row slot can be moved to the column slot simply by dragging and dropping.
4. Page or Report Fields – The page field (Page Field in Excel 2003 and Report Field in Excel 2007) differ from the row and the column fields in the sense that they tend to govern both (and therefore the entire pivot table). Placing a field in the page (or Report) slot will not have an immediate impact on the pivot table. However anytime we select a particular value from the page field drop-down list, the fields in the row and columns will automatically adjust and narrow down to show only those groupings that pertain to the selection in the page field. Bear in mind that the same can be achieved by placing them in the row or column fields and then choosing the appropriate value from the drop-down but that would tend to add additional number of rows or columns. So in a way page fields help avoid clutter and provide a better structure to the pivot table.
There are no hard and fast rules governing what fields should be placed in the row slot and what in the column slot. Often times, the decision is governed by how the user wants to view the information, what makes the information more “graspable”, the amount of space available and the whether a particular placements helps you do further analysis (Ex. Sorting). Having said that, you will find that typically, the date fields (days, months, years etc) are better off being placed in the column field as they help the reader get a sense of variation a bit better. Anything field that is of an immediate interest to the user can be placed in the row field. Finally the fields that may not change too often but help grouping the row and column fields may be placed in the page fields. So in our case, if we want to look at the performance of Sales Managers over time, we would place the Month field in the column slot, the Sales Manager field in the row slot and Divisional and Regional Manager fields in the page slot (and of course with Sales being the Data Field – right in the center). However as I said, the entire point of having a pivot table is to have the freedom to move things around.

To make a pivot table in Excel 2007 and later versions:


To create a pivot table in Excel 2007 and later versions, we first begin by selecting the range which we want to use as the pivot’s base data. Now on the “Insert” menu tab, select “Pivot Table”

In the Pivot table field list that appears, drag and drop the required fields to the various slots. As mentioned earlier, there are four available slots – row, column, report and value.

To make a pivot table in Excel 2003 and prior versions:

To create a pivot table in Excel 2003 and prior versions, we first begin by selecting the range which we want to use as the pivot’s base data. Once the base data range has been selected (see excel shortcuts to make the task easier), you can now use the “Data” -> “Pivot Table and Pivot Chart Report ” option form the menu. Follow the steps 1 to 3 by clicking the OK button. On step 3, click the “Layout” button. In the wizard window which opens up, drag and drop the fields in to respective slots as desired.

Working with Pivot Tables in Excel

Once we’ve placed our data fields in the proper placeholders, you can:

1. Move the fields of the pivot table

By their very nature, pivot tables are meant to slice and dice data – which means that you can drag, move and place the various fields at whatever place you like. This change in orientation allows the user to gather useful information with a few clicks which otherwise would have taken a few hundred formulas.

2. Drill down using the various drop-downs

Whenever you select a drop-down, the pivot table changes itself to reflect data pertaining to only that selection. In our case, if we were to select “Steve Williams” from the list of Divisional Managers in the page drop-down, the entire pivot table would shrink to reflect only those Sales Managers that work under him.

3. Turn on and turn off the totals and subtotals for various fields and the entire pivot table

Pivot tables by default will show the totals for most of the fields. Although it may be useful in a number of cases, in other it may help the reader make better sense of the data by simply turning off the totals. Since each row in the pivot table itself is a summary of the underlying data, under most circumstances, we would not lose out much by doing this step. To turn of totals for each field, select the field and simply right-click -> “Field Settings” and choose “None” under Subtotals option. You will have to repeat this step for each individual field. This can be used with both the row and the column fields. You can also turn off (or on) the subtotals for the entire table by right clicking on the table and selecting “Table Options” (in Excel 2003) or “PivotTable Options” (in Excel 2007) and then unchecking the two check boxes labelled Grand total for rows and columns.

4. Refresh the pivot table when the underlying data changes

Another interesting feature of a pivot table is its ability to update itself with fresh data. Say you created a pivot with some underlying raw data. Next month, fresh data points arrived and were pasted over the earlier data set. Do you need to create the whole pivot table again? No. All you need to do is to right click the pivot table and press “Refresh Data” and he pivot table will be refreshed with the new data set. This feature makes the pivot tables completely re-usable.

5. Pivot Chart using Pivot Table

Another useful feature of the pivot tables is the pivot chart. Think of pivot charts as pivot tales in picture! The shortest way to create a pivot chart is the select any cell in the pivot table area and press F11. Hey presto – we have our pivot chart ready. However since pivot tables will typically have a lot of data, a pivot chart based on such a table will tend to show a lot of data points – which in many cases limits their usefulness. However pivot charts, like pivot tables, offer the user the ability move the various fields around so that you are able to get a better grasp of the data. The fields in the pivot chart can be moved around in the same way as one would do in a pivot table.

6. Formatting a Pivot Table – Auto and Manual Format

Now that we are really getting to know the pivot tables a little better, let’s delve a bit deeper. Another interesting tool that can be used with a pivot table is to format the pivot table. Although a pivot table on their own do a good job of summarizing data, their usefulness can be further enhanced when proper borders and coloring are applied to them. The border and additional formatting help present provide a proper structure to the data in the pivot table and helps improve readability. But before we begin, right click on the pivot table and select “Table Options”. Uncheck “Auto Format Table”. If this options is left on, every time you refresh the table after adding new data or after move the fields around, and changes you made to the like adding colors, fills and special borders, will be lost. By turning this option off, we prevent the table from losing any of our customizations. So now if anyone comes to you and says, hey my pivot keeps on loosing its formatting after every refresh, you know what to tell them.
You can use the auto format feature in Excel 2003 and earlier versions by selecting the pivot table and clicking “Format” -> “Auto Format” in the menu bar and then selecting the format style. In Excel 2007, you can click on the “Design” tab and selecting the PivotTable Style that you need.

7. Pivot Table Display – Show items with no data

By default, the pivot table will not display items that do not have any data. So for example, if we had 12 months of data for all Sales Mangers but had the July data missing for a manager named ‘Cristobal’, when that particular manager is selected from the Sales Managers drop-down, the entire column for the month of July would be missing. In some cases, this could be undesirable and hence we may want to show the column even though it may not have any data in it. To show items with no data, select the field and simply right-click -> “Field Settings” and check “Show Items with no data” option.

8. Pivot Table Sorting – Ascending/Descending Criteria, Top X values

The pivot table can serve the very useful role as a tool for sorting lists.

Sorting using Pivot Table in Excel 2003 and ealier

In order to sort a row or a column field, simply right click it and choose the “Field Settings”. Under the “Advanced” tab, you can turn on the Ascending or Descending option. You can also specify which field to use as the criteria (the field itself or any of the aggregated fields). Once you’re done click ok. Your pivot table is now fully sorted and you can easily pick say, the top 10 or bottom N items from the list.

Sorting using Pivot Table in Excel 2007 and later


By default, each row and column field in Excel 2007 provides a drop down list which can be used to sort a pivot table. Additional sort options can be availed by clicking the “More sort options” option.

9. Preventing display of Errors – Pivot Table options and Conditional Formatting

If your underlying raw data is spic and span, you would not encounter any errors in how the pivot table displays errors. However there are cases (especially when you being to experiment with some of the advanced techniques), where the pivot table will being to show errors. To hide these errors, you can either correct the underlying data set (preferable) or hide them. If you decide to do the later – you have two options. The first one is the easier one – right click on the pivot and select “Table Options”. Turn on the “For error values show” message box and leave it blank and type in something more informative such as “value not found”. This will help you hide errors. The second approach it to use conditional formatting.

In the rule box, you can enter the rules as =IF(ISERROR(A1),1,0) where cell A1 is the cell you would like to format. In the color, choose white. By doing this what we ensure that whenever a cell contains an error value, it is displayed in white. If the background of the pivot table is also white, this would tend to hide the errors. Excel 2007 provides far greater control over these formatting rules than earlier versions of Excel. However keep in mind that conditional imposes its own overheads on an Excel spreadsheet and can slow down performance.