Data Analysis techniques using Microsoft Excel
Posted on:A common requirement for business employees is to manage, manipulate and interrogate business data using Microsoft Excel. This blog highlights some of the important simple analysis techniques used in Microsoft Excel; all of which should not take longer than 60 seconds to produce an answer. These techniques are covered in detail on our Microsoft Excel Intermediate course.
[ SIMPLE SORT ]
Data tab, Sort and Filter group: Sort icons x 2 – [A-Z] or [Z-A]
Sort into groups of data by text, date, number or colour. Then highlight the corresponding data and use the status bar features to report the answers using Sum, Min, Max, Count, Count Numbers and Average. Tip: To quickly highlight a column of data; first select a cell of data under its heading; then use the shortcut: Ctrl + Shift + Down Arrow.
Simple questions answered within 30 seconds.
Typical question: How many clients have outstanding invoices and how much do they owe in total.
[ MULTI COLUMN SORT ]
Data tab, Sort and Filter group: Square Sort icon
Sort data by more than one column to group against each other. For example: Sort by Geographical location then Client name and so on. This would allow you to arrange all your data by geographical location and then by client in those locations.
Simple and complex questions answered with 30 seconds.
Typical question: Arrange all our clients by geographical location and report which location has the most clients.
[ FILTER ]
Data tab, Sort and Filter group: Filter icon
Filter by text, number, date, colour. The perfect tool to manipulate large sets of data and answer simple and complex questions. Make sure you click on the “clear” icon in the sort and filter group between questions. Useful to filter out unwanted data and leave only wanted data for you to interrogate.
Simple and complex questions answered with 30 seconds.
Typical question: How many Female, Managers are employed in the UK Division. or Using conditionally formatted cells to highlight invoices that exceed 30 days to red. Filter those invoices, and send a copy to accounts.
Note: You can sort a filtered result.
[ PIVOT TABLE REPORT ]
Insert tab, tables, Pivot Table
A very powerful tool that can be used to arrange your selected data by column, row, data and filter. They are easy to create and modify if you start by keeping it simple. Drag a field header into one of the four layout box’s to build up the pivot table. If the pivot table doesn’t look right then move the field headers around until it is correct.
Simple and complex questions answered with 60 seconds.
Typical question: Create a pivot table report to highlight the distribution of our clients, the products we supply them and the total value of products by client.
Note: If you update your source data you must refresh the pivot table to include the changes to your source data.
Note: You can filter the source data and create a pivot table from the filtered data.
My favourite Microsoft Excel shortcuts
F2 – Select a cell that contains a formula – into edit mode. Why: So you can quickly edit the formula in the cell.
F4 – Select a cell to create an absolute reference by pressing F4, to insert the $ and fix the cell. Why: So it can be used for “What If Analysis techniques” e.g. changing product margins.
F11 – Create a quick chart based on the selected data. Why: Its very quick and easy.
Ctrl + A – Select All. Click inside your data and press Crtl + A. Why: It will select all active data connected to the selected cell.
Ctrl + Z – Undo previous actions to your last save. Why: Its a great deal quicker than using your mouse and finding the icon.
Ctrl + Shift + Down Arrow – Select all active data in a column. Why: Its quick to select all column data.
Alt + Down Arrow – Select an item from a drop down list. Why: Its quick and easy. It also maintains consistency of data.
Microsoft Excel Training Options
Data analysis techniques are covered on our Microsoft Excel Intermediate course.
We run regular scheduled Excel courses at our Southampton training venue.
Training options: On-site training for four or more delegates, tailored to your needs.
Training equipment: IPSO FACTO can supply all training computers at no extra cost.