Excel Data Analysis – lots of techniques to be used for different reasons
This week we’re looking at Excel data analysis techniques which form a good part of our Intermediate and Advanced courses.
By Data Analysis I mean in this context Sorting, Sub Totalling, Filtering and Pivot Tables and in response to the question above…then it depends on what you are trying to do! They can all be the best choice in certain situations.
Laying out your data
One of the key set up features for these topics in Excel is to lay out your data properly.
- Row 1 and just row 1 ideally should be used for short column (field) headings
- Rows 2 to infinity! Should contain rows of data with no blank rows (also no blank columns)
- Selecting one active cell is sufficient to get started off in all four topics (not selecting the column or whole range)
Data Analysis Options
- The Data Tab has Sorting, Filtering and Sub Totals commands within it
- Custom Sort lists are hidden away but include jan,feb,mar,apr etc. There are 4 different Excel Custom Lists built in to Excel as default and you are able to add your own to that list
- Sub Totalling examples include: ‘add together all the salaries by department’ or ‘count the number of parts per customer’
- Once you have got the hang of Sorting, Filtering and Sub Totalling, then progress onto the wealth of functionality and power of Pivot Tables which include all three functions
- Pivot Tables rely on fields and functions like SUM, COUNT etc. and you can chart data in this area too
You may have attended our Intermediate or Advanced Excel course before but if you haven’t, then why not take a look at our Microsoft Office Training page on our website with all the details of our courses and agendas.
All other hints and tips can be found still on our website here.
We’ve also been running courses for clients bespoked to their requirements recently i.e. taking sections out of different level based courses.
We wish you well in your use of all Microsoft products. Please keep in touch