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, and instructor use for developing materials for class. Example aspects of spreadsheets that are relevant to science education are:
- Using Excel as a calculator to explore what mathematical equations can tell us about how the real world works for specific input conditions or for a range of possible values.
- The calculator can be pre-constructed with a focus on student exploration or students can be guided to construct their own calculators and then explore. The first option saves time but the second option prepares students to use Excel for their own projects and future activities.
- Graphically displaying equations (analytical models) and real data.
- Obtaining numerical solutions to more mathematically complex models.
- Graphically comparing results from a model and observations.
- Statistical analysis including mean, standard deviation, error bars on graphs, linear and polynomial fits, multivariate analysis, etc.
- Spectral analysis (Fast Fourier Transforms).
- Displaying histograms of students results or student response to exams or questions.
How to use Excel?
Microsoft Excel is easy to use and has very many features so the more you use it the more you learn. The Excel spreadsheet has great documentation through the built-in help menu. AutomateExcel will guide you on using the program, though you can always just give it a run!
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 means of checking the validity of the information provided.
Raw data is data at its purest and hasn’t been processed (ie – remains untouched!) From this raw data you can produce anything you wish, without it you are left wondering if someone has changed it (ie – by applying a filter or removing org units).
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 use the percent button
Cells with Dates
Sometimes when copying dates they appear as numbers (ie – 40179). The reason for this is at copy time the cell has been set to display data as a number and not a date. To correct simply change the data type to Date (explained above).
Cells with Text
Cells that contain text have different behaviour to those that contain numbers:
They automatically overlap empty adjacent cells to the right (the text belongs to cell in the J column below).
If the adjacent cells to the right contain text it doesn’t overlap and shrinks out of view.
There are two ways to correct this:
1. Double click the column divider to “Fit column to size”.
2. Format the cell to “Wrap Text”. Rick click > Format Cell… > Alignment (tab) > Wrap Text
It should now appear in the J column as should below:
A useful method when performing data cleansing is simply “Find and Replace”.
- 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 just find next and replace one by one).
- 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.
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).
- From the toolbar select Data > Filter > AutoFilter…(the downward arrows will appear for each heading)
Note: the filter can be turned on without highlighting the data and it predicts the data range (usually all the data on the worksheet).
Working example: You want to find out what Service Centres have a boiling point of higher than 80.
- Select the Boiling Point downward arrow
- Select Custom
- Select is greater than and enter 80 into the value box
- The data now only displays those rows that match the criteria. How this works is that the rows that do not match the criteria are hidden not removed so be careful when editing the data and dragging formulas as this is prone to overwrite data that is not in view without you knowing! It is advisable to copy the data from here to another worksheet and then edit it from there especially when you’re dealing with large number of rows.
- You can now sort in order of Boiling Point as follows:
To show all the data again:
- From the toolbar select Data > Filter > Show All
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 result will appear in the cell you selected (you have to manually type in the words “Total”, “Average” etc to the left of the formula cells)
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 G column (Position No) to select all the data in that column
- It should be automatic but check the Criteria Range is the range for all the data you wish to include in the filter
- Tick the box “Unique records only”
- Click OK.The duplicates (rows 9 and 10) that contain the same position number as row 8 have now been hidden from view.
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 FTE) you have in total
- Broken down by Position Title
- Broken down by Employment Status (Perm/Temp/Casual)
Once you have the raw data you create the pivot table on your raw data:
- From the toolbar select Data > Pivot Table and Pivot Chart Report…
- Click Next
- Check that the data you want to include in the pivot table is highlighted in the Range
- Click Next
- If you want your pivot table to appear in a new worksheet (tab) click finish. Otherwise, If you wish it to appear in the same worksheet (ie – below the data) click Existing worksheet and select the first cell after the data and then click finish
The pivot table skeleton should appear.
If the Pivot Table Field List (shown on the right, above) is not in view then you will need to open it:
- Right click the toolbar (anywhere in the square below):
- Select PivotTable.
- Click Show Field List
- The fields should now be in view and you can drag the box to the right to lock it in place.
- Drag the following fields into the areas:HIDDEN FILTER AREA: None
COLUMN SEPARATOR AREA: Position Type
ROW SEPARATOR AREA: Position Title
BASE DATA AREA: Actual FTE
- To change the base field to a sum right click the field (circled below):
Select Field Settings…
Select Number and 2 decimal places then click OK.
The data should have changed to decimals.
- To re-order the Position Type columns:Right click Casual > Order > Move to End
You now have your finished table with the columns in order of Permanent, Temporary, Casual.
The next step is to drag the fields you need into the table areas to create the table. The pivot table structure is as follows:
Typical usage of the areas:
HIDDEN FILTER AREA: Recurrently funded positions filter
COLUMN SEPARATOR AREA: Position Type, Employment Type, Employment Basis, Classification
ROW SEPARATOR AREA: Org Units, Locations, Position Titles
BASE DATA AREA: Headcounts, FTE’s
The pivot table should look like this:
Notice that the base data has defaulted to “Count of Actual FTE”. This means that each position has been counted as 1 irrelevant of the FTE value. If the base field was Headcount this would be correct (as all values would be 1) however it’s FTE values and they require a sum not a count.
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)
The pivot table now should have replaced the blanks with zeros.
2) To hide the Totals column:
Right click the base data field (“Sum of Actual FTE” shown above)
Select Table Options…
Uncheck Grand Total for Rows (circled below)
The totals column should now have been removed.
3) To add a hidden filter to the pivot table:Drag the field that contains the filter into the hidden data filter area (ie – Org unit level 04)
Click the down arrow to select the org unit filter. Select the required filter (ie – Sport And Recreation)
The pivot table now shows only those positions in Org unit level 04, Sport And Recreation.
4) To show only selected position titles:
Click the down arrow to select the Position Title filter
To make selection easier drag the list outwards so more positions are visible
If there are many values in the list and you only want a few, start by un-checking Show All (shown below) to deselect all values
Select the positions you wish to show in the table. (ie – Athletes and Managers)
The pivot table should now only contain Athletes and Managers.
5) To show percentages of position type for each position:
Right click the base data field (in this case “Sum of Actual FTE”)
Select Field Settings…
Click Options > >
Select % of row for Show data as
Note: In this list there are also options for % of column and % of total with the latter being very useful.
The pivot table now shows percentages but there may be error values for the rows that have no positions (as you can’t divide by zero!).
6) To remove errors from the pivot table:Right click the base data field (in this case “Sum of Actual FTE”)
Select Table Options…
Enter 0 in the For error values, show box
All errors should now be replaced by zeros.