New to Excel? Click here.
November 24th, 2010 | Categories: Data Manipulation | Tags: , ,

In these two tutorials (two parts) you will learn a few cool tips regarding pivot tables: 1) To fill the empty values with zeros:Right click the base data field (circled below) Select Table Options… Type 0 in the empty cells, show (circled below) Click OK. The pivot table now should have replaced the blanks with [...]

November 21st, 2010 | Categories: Data Manipulation | Tags: ,

A Pivot table is in essence a way to extract data and present it in a readable form. Pivot tables are a great way to interrogate data and create tables of information that can be refreshed when the raw data changes! Working example: you require a table that shows the following: How many positions (Actual [...]

November 21st, 2010 | Categories: Data Manipulation | Tags: ,

The advanced filter can be a very useful tool when trying to remove duplicate values from a field. Working example: To remove duplicate position numbers from an establishment report. From the toolbar select Data > Filter > Advanced Filter… Click the selector tool for the List Range (circled below) Click the header bar for the [...]

November 21st, 2010 | Categories: Data Manipulation | Tags: , , , ,

There are quick ways to use these everyday functions: Click the cell you wish to display the new value (ie – total) Select the sum button from the toolbar Check the range is correct (for the values you wish to include) If the range is incorrect you can highlight a new range Click OK. The [...]

November 13th, 2010 | Categories: Data Manipulation | Tags: , ,

The AutoFilter tool is very useful for manipulating the data to find data subsets at the click of a button. To turn the AutoFilter on: Highlight the data including the header column (if applicable). Note: the filter can be turned on without highlighting the data and it predicts the data range (usually all the data [...]

November 13th, 2010 | Categories: Data Manipulation | Tags: ,

Highlight the data From the toolbar select Data > Sort… Select your sort and order (up to 3 sorts can be applied simultaneously). Click OK. The data should now be sorted alphabetically by Service Centre.

November 13th, 2010 | Categories: Data Manipulation | Tags: , ,

A useful method when performing data cleansing is simply “Find and Replace”. Working example: From the toolbar select Edit > Replace… (or press CTRL + H) Enter the find and replace characters (the default is not case sensitive, if you require this click options) Click “Replace All” (if some are not to be changed click [...]

November 13th, 2010 | Categories: Data Manipulation | Tags: , ,

To change the data type of a cell (or region): Right click > Format Cells… Select the category and type for the data type (ie – catergory Date and type dd/mm/yyyy) Cells with Numbers There are shortcuts on the toolbar that may help you save time: For decimal numbers use the rounding buttons For percentages [...]

November 13th, 2010 | Categories: Data Manipulation | Tags:

As you probably already know, Microsoft Excel can be used to store information of any kind within its cells (numbers, dates, text…etc). It is a fact that information is only as accurate as the data it promotes, therefore it is best to keep the raw data almost 100% of the time. This provides you with [...]

November 13th, 2010 | Categories: Guide | Tags: ,

Microsoft Excel is a spreadsheet program which allows one to enter numerical values or data into the rows or columns of a spreadsheet, and to use these numerical entries for such things as calculations, graphs, and statistical analysis. Why use Excel? Spreadsheets (like Microsoft Excel) can be very useful for student interactive activities, interactive lectures, [...]