Tips and tricks to help manage your CSR data in Excel
Date posted: 30 December 2016
Posted in: CSR data
If and when you begin strategically collecting CSR data from your organisation, one of the first things you will probably notice is just how much of it there is. It’s amazing how much data can be collected on how many things and soon you will be faced with the daunting task of unifying various datasets from various business functions into something that can be interpreted, analysed and reported on.
GoMarketWise is designed to help you find the right tailor made CSR data management tool for this job – and believe us when we say there are a lot of them. There is one data management tool you’re likely to already have and that’s Excel. Now I want to make it clear from the outset that Excel is not a suitable tool when it comes to managing all your CSR data. That being said, its ability to manipulate and present data in so many ways, as well as its familiarity to so many people, make it an extremely useful tool to have around when analysing top level data.
I want to explore the various pros and cons of using Excel for CSR data management in a future blog post, but for now let’s take a closer look at using Excel. Below are three essential Excel functions that will make your life a lot easier when it comes to CSR data management and analysis.
Sumifs and Countifs
Tab on Ribbon: Formulas (or entered manually into a cell)
Description: Summing or counting cells in a column based on a set of stipulated criteria associated with that row.
Sumifs and countifs are two of the most useful formulas when it comes to pulling out specific information of a large dataset. Both are similar in syntax and function; the only difference being that sumifs sum the total of the cells that meet the criteria of the formula, whilst countifs count instances of that cell, regardless of its contents.
Let’s look at the syntax of each in turn.
Sumifs Syntax: SUMIFS( sum_range, criteria_range1, criteria1, criteria_range2, criteria2…)
Countifs Syntax: SUMIFS(criteria_range1, criteria1, criteria_range2, criteria2…)
As you can see sumifs, stipulate a sum range, before setting out the criteria range and the criteria, whereas countifs don’t require a sum range as they’re only counting cells that meet the given criteria. The criteria can be written manually (surround with “quotation marks” to do this) or simply point to another cell in the spreadsheet.
Power user tip:
By locking the range in the formula using the F4 key you can easily drag the sumifs or countif formula down to show totals based on criteria in a column to the left of your summary table. In our examples below we have quickly returned totals for products 1 through to 3, as well as for January, February and March.
Sort and Filter
Tab on Ribbon: Home
Description: Filtering or sorting an entire dataset based on criteria found in one or multiple columns.
Filters are an absolute must when it comes to any data set. By allowing you to quickly sort or filter data based on any column, filters allow you to quickly see only the data you need to see, or to view it in order of date, value or alphabetically.
To add filters to your data simply select a cell in your dataset and use the drop down Sort & Filter menu on the home tab (to the right) to select Filter. As you can see from the images below, filters can be used to sort by value as well as filter on specific criteria (in our case Product 2).
Power user tip:
There is also a useful option in the Sort & Filter dropdown which clears all filters. This is really useful if you have multiple filters applied and want to make sure you’ve cleared them all and are looking at your dataset in its entirety. It also saves you a lot of time having to clear each column filter separately.
Tab on Ribbon: Home
Description: Changing the formatting in a given row, column or individual cell based on a stipulated rules.
Conditional formatting is considered by some to be a non essential function in Excel that is purely designed to make datasets look more appealing. Whilst it’s true that applying conditional formatting to your data is rarely essential, it can make your data a lot easier to read and even help you weed out anomalies or errors.
Conditional formatting can be applied in two ways. The first is by selecting the column of data you want to apply the formatting to and using one of the many pre-set options on the Conditional Formatting dropdown on the Home tab. One of the most useful are the data bars which show the value of the cell compared to the rest of the column as a coloured bar from left to right (see images 1 and 2 below).
You can also apply conditional formatting by applying rules to the column. Opening up this by clicking new rule, or manage rules and then new rule (the latter option allows you to edit existing rules). There are a ton of options in here which I won’t go into detail with. One of the most useful is ‘Format only Cells that Contain’ which allows you to change cell colour and font based on certain words or value ranges (see image 3 below).
Conditional formatting can be applied to rows or even individual cells but it’s far more likely you’ll be applying it to columns.
Power user tip:
One of the most useful and frequently used conditional formats is the Duplicate Values cell highlight, which can be found as a pre-set rule in the dropdown under Highlight Cell Rules. This will quickly show you if any cells in a given column (or row for that matter) contain duplicate information. If you know each cell in a column should contain unique information then this is a really quick and efficient way of weeding out errors. You can even filter on the cell colour this rule creates.
Tab on Ribbon: Insert
Description: Creating a summary of data based on a larger and more complex dataset, allowing you to see wider trends in the data.
Pivot tables are one of the most powerful features in Excel and the most flexible and useful way in which you can manipulate data. Unlike tables built using lookups, sumifs and countifs, pivot tables are dynamic. This means that you can quickly change the way you see your data without having to alter or rebuild the table. This makes pivots a very powerful tool when it comes to quickly analysing large datasets and drilling down into the data.
Pivot tables need to be inserted into an empty space on your spreadsheet by selecting a cell in your data table and going to the insert tab. Once you’ve done this you will see the Pivot Table Field List (see image 1) on the right of the screen, which will list all the fields in your dataset. To create a pivot table you then need to drag individual fields to any of the four boxes, which I’ll go through in turn.
The values box is essential as it is this that determines what field your pivot table will summing or counting. In our example we have used the value field from our table and decided to sum. Most of the time you will want this field to denote numeric data but on some occasions you might want to count instances of a particular occurrence in your dataset, in which case simply use the drop down arrow, select Value Field Setting and Count (other options are available).
Row and Column Labels
This is where the fun starts! The row and column label boxes allow you to quickly manipulate the data from your original dataset by dragging and dropping fields here.
In image 2 for example, we have ordered the rows of our data by month. Because we have the value field already placed in the values box, Excel just sums each month for us. If, however we wanted a more detailed table we can simply add the product field to the column labels box (image 3) to see each month broken down by product. We could then decide to flip this so that our product field is the row label and our table columns are split into months (image 4) or just have both fields as row labels (image 5).
With a large dataset with many fields, there are literally endless ways in which you can arrange your data.
Report filters allow you to drill down into specific field values by creating a drop down filter in which you can select individual or multiple values. In our example we have taken the pivot table seen in image 2 and used the report filter to restrict it to only show values for product 1 (image 6). You aren’t restricted to one report filter either and can add as many as you want. With large datasets, report filters are very useful for quickly drilling down into individual value sets within a given field without having to change the pivot table layout itself.
Power user tip:
Pivot tables are incredibly useful for turning a huge impenetrable block of data into a summarised and readable format. Because they are dynamic it’s sometimes worth experimenting with the field boxes until you get the layout you want.
Of course pivot tables aren’t always the answer and sometimes it might be more practical to use sumifs to summarise your data. If your dataset is fairly manageable you might only need to use filters and apply a bit of conditional formatting. Whatever Excel tools you use to present and analyse your data, we hope this guide has provided you with a good starting point.