Our guide to basic data analysis on Excel

Getting your Trinity Audio player ready...

Excel is the world’s leading spreadsheet, used in millions of businesses around the globe. If you run a business, or manage one, or have any dealings with its Excel function, and you don’t think you need to know how to perform basic data analysis in the program, you’re only getting a fraction of the value you can get from your Excel spreadsheet. So we put together a guide to basic data analysis on Excel, to maximize your power over your spreadsheet investment.

The most important elements of data analysis on Excel.

There are a handful of basic data analysis elements every business needs to know how to handle, including sorting, filtering, charts, and pivot tables. Let’s take a whirlwind tour of those elements, so you’ll have a good idea of your way around basic data analysis on Excel.

Sorting.

Sorting is the fundamental process of getting your data in the order in which you need it.

There’s a simple sort and a complex sort.

The simple sort allows you to sort data in one column easily. You simply click on any cell in the column, select the Data option from the ribbon at the top of the page, and press one of two buttons in the top left of the screen. AZ will sort the column’s data in alphabetical order. ZA will do the same thing but in reverse alphabetical order (the same applies to numerical columns – AZ will sort in ascending numerical value, ZA in descending numerical value).

The complex sort involves highlighting all the columns you want to sort.

Then you click the Sort button (next to the AZ and ZA buttons). That opens up a dialog box that allows you to customize levels of sorting. If you want to sort by salesperson’s surname first, and then amount sold by people with similar names, you can specify your primary, secondary and even tertiary sort criteria, so you can build a sort engine that applies to your particular spreadsheet, and with the click of a couple of buttons, manipulate your data so that it displays in the most useful, at-a-glance way for your purposes.

Custom sorting.

So far, so good – if you have alphabetic or numeric values, you can simple sort or complex sort them. But not everything in your spreadsheet will have a value attributable to, for instance, the way they’re spelled. If you have classifications of sales performance like High, Medium, and Low, an easy way to see how your company’s sales are going is to sort by these attributions. For that, you’ll need a custom sort.

The process is similar to a complex sort – select the columns you want to apply the sort to, select the Data tab, and go into the Sort drop-down. In the Order drop-down, you’ll see the tantalizing “Custom List…” option. Select that.

That opens up a separate dialog box, where you can build custom lists – enter the words in the order of priority you want to sort by, for instance high, medium, low, or low, medium, high. Add your new list and click OK to sort your highlighted data by the criteria you’ve selected. Building a number of custom lists is a useful way to bring your data under your control, whatever you need it for.

These lists can also be used for things like monthly formatting (If you tried to do monthly formatting based on alphabetical sorting of course, April and August would show first).

Filtering.

So now you’re a data-sorting wizard. The next step in your basic data analysis training on Excel is filtering. Filtering is how you pick and choose results to be highlighted based on their meeting advanced criteria.

The great thing about filtering is that it can give you deeper levels of insight than straightforward sorting, but in Excel, the process begins in very much the same way.

Highlight the data you want to filter, or click inside a cell if you want to apply the filtering to the whole sheet.

Open the Data tab. Next to the Sort function, you’ll see the Filter button, which looks like a funnel.

What you’ll see now is that arrows have appeared in each of the column headings in your sheet. Click the heading that defines how you want to filter – for instance, if you sell to multiple countries, but you want to filter for US sales only, click on the column heading for by-country sales.

You’ll open a dropdown that allows you to select which of the types of results you want to filter for – eg US sales. Check the box or boxes you want to filter for, click OK, and your spreadsheet will only display results for US sales. Whereas with custom sorting, all your data will continue to be shown, just in the sorted order you want, filtering lets you cut to the chase for custom reports. Once you have your filtered dataset, you can of course then continue to sort it as you wish.

Charts.

They say a picture paints a thousand words, which is why sometimes you want your data displayed graphically.

Fortunately, Excel has an easy way to turn your data into charts.

As usual, highlight the columns of data you want to represent as a chart.

Click the Insert tab. Find the Charts group – you’ll see representations of different types of chart and graph – and click the type you want to use. There are pie charts, various bar graphs, and several types of line graph you can choose from. Once you’ve clicked to choose your chart type, your data will be represented as that type of chart. Add a title to it, for ease of explanation, and you’re good to go.

If it turns out you want to use a different type of chart instead, simply select your chart, open the Chart Design tab, and you’ll see a dropdown list of all the chart types available. Clicking on any of them will give you a sneak preview of how your data will look in each chart type. Select the one you want, press OK, and you’ll replace your previous graph with your new graph.

This can be one of the more satisfying aspects of basic data analysis on Excel, because it can make dry data instantly understandable in a graphical format.

Pivot tables.

Pivot tables are one of the favorite data analysis tools of longstanding Excel users – they’re almost like magic spells for data. If you have a large dataset, getting meaningful takeaways from it can be difficult. Pivot tables make it easy.

Say you sell wing nuts, lug nuts, cross head screws, flat head screws, six types of screwdriver and three varieties of hammer, all with different unit costs. Say you sell domestically and export to ten different countries too. Now imagine you wanted the data for how many lug nuts you sold to France and Germany in the last month. That’s a combination of four data points – lug nuts, Germany, France, and last month.

Pivot tables are how you stop wading through masses of sales data to display only want you want to know.

Click any cell in the data sheet. Open the Insert tab, and click PivotTable. That opens up a dialog box, and pre-fills the most likely data – the whole sheet – and pre-selects the option that lets you create your pivot table in a new sheet, rather than overwriting your current spreadsheet.

Click OK and you open a second dialog box, where you get to specify those elements you want – Product: lug nuts. Country: France and Germany. Month: December, for instance.

You’re effectively telling Excel which data fields you want to combine into the query in your pivot table. When you click OK, you’ll generate your pivot table that will show you your sales numbers of lug nuts in each of your selected countries last month. Pivot tables really are the magic spells of Excel – they allow you to specify the results you want from potentially vast data fields, and to get your answers in seconds, with no calculation on your part.

Setting you free in Excel.

With these four basic data analysis disciplines under your belt, you should be able to do most of the things you need to do on a day-to-day basis, as far as your Excel data is concerned.

With simple sorts, you can clarify your data in a way that makes sense. With complex sorts, you can begin to bring order to the data. With filtering, you can focus on particular elements of the data and only show those elements, for a significant boost in clarity and an easy way to actively interrogate your large data fields.

Pivot tables take you to a whole other level of data interrogation, because you can create relatively complex queries in seconds, that will cut through even enormous data fields and present you concisely with the results you need. And charts help you turn any amount of data into easily digestible graphical representations, be they sales charts, staff costs, overall outgoings or revenue trends.

With these four data analysis power moves in your pocket, you’re able to run your business, getting access to all the data you need in just a handful of heartbeats.