Excel is the most popular spreadsheet software available and is part of the Microsoft Office package. It is ideally used to store a range of data and to easily analyse it and pull out relevant information. One of the key skills to learn when using Excel is how to properly analyse this data to get the most out of the software.
Preparing the data
Before you start analysing data, it is a good idea to ‘clean’ the table. This simply means going through the table and making sure all the data is consistent. This means that if you have number and word columns, ensure that there are no crossovers with words in the number’s column and vice versa. Also, make sure there is consistent spelling for certain terms used throughout. For example, make sure you use the term ‘customer’ every time, not ‘customers’ as the software will treat these as different things.
The Sort function
The Sort function is the core way to organise data in order to analyse it. You can select all of the data by pressing CTRL + A then choose the Sort option on the main menu. A pop-up box will appear that allows you to customise the sort in several ways. You can choose to sort by any of the columns which will appear by the name on the first line if you have selected the ‘my data has headers’ tick box in the top right corner.
Sort lets you look for trends, analyse small parts of data and combines with formulas to discover more trends within the data. For example, the COUNTIFS lets you search for the number of occurrences with two selected data types – customer and female for example – then adds a number at the bottom of how many times both appear in a line.
Pivot tables
Pivot tables are ideal when you have a large amount of data to analyse and the process of manually counting or using formulas would be too time-consuming. It will automatically sort data and list values. To create a pivot table, select the data then on the Insert tab select Pivot table.
The table will open in a new Excel sheet. Next, you can add values by selecting the required information from the box that says, ‘choose fields to add to report’. You can also sort by a particular column by dragging the name of the column label into the rows box.
Data display
Another powerful benefit of Excel is the ability to show data in different ways in addition to the spreadsheet. This is done with charts. First, you select the data you want to include in the chart on the Pivot table then go to the Pivot Chart button on the menu. You can then select the type of charge from the range listed and change the settings as required. This will then create a chart on the Excel sheet that helps visualise the data.
Powerful tool
Excel is a powerful tool, and these are just a few of the basic settings. With its formulas, there’s no end to the ways you can analyse data and make easy to understand visual representations of it to share with others.
If you or members of your team could benefit from personalised Microsoft Excel training delivered by experts in the field, Elliott Training provides Excel Training Courses for all levels. We can help your business and your team bring these skills to life. To find out more, simply fill out our contact form or call us on 01454 203355 to discuss your requirements.