The latest installment of Cooper Technologies’ Excel Like A Pro webinar series, hosted by Nick O’Neil, tackled two great ways to look at your Excel data little differently – Conditional Formatting and PivotCharts. Excel has a ton of powerful tools designed to make data easier to read. It’s just a matter of figuring out how to get these exciting functions to work for you.
There are several different ways to use data sets with Conditional Formatting. In Excel versions 2013 and newer, Conditional Formatting is found in the Data tab of the home ribbon, right next to General Number Formatting.
To make this a little easier to follow, we’ll use sales orders as an example. To highlight a specific data set such as the states items were sold in, click and drag to highlight the States column in your data set. Click Conditional Formatting and choose from the options available in the drop-down box. There are automated options, such as highlighting ‘Rules That Are Equal To’. By typing WA into the dialogue box, Conditional Formatting will highlight each cell that is ‘equal to’ WA in the States column.
To find sales data related to a particular state, select a new blank cell and type a new header, such as ‘highlight state’. You’ll be inputting your new data in the cell underneath. If your new header is cell J1, you’ll be using J2 for this. Select the entire sales order data set, then select ‘Create A New Rule’ from the Conditional Formatting drop down menu. You’ll be creating a new formula that will choose which cells to format. The formula will look through your highlighted data and decide if any of that data should be highlighted based on the values you’ve supplied.
Select the top cell of the highlighted data area you want to look at – in this case the top cell of the States column – and you’ll notice a ‘$’ will appear in your dialogue box. The ‘$’ is what defines a specific address within a cell so that if you were to copy the data contained in that cell across multiple rows or columns, the reference point or “address” for that cell would stay the same for the entire data set you’re using.
In this example, the value your formula will be looking for is a specific state. Remove the ‘$’ from in front of the row number to allow the formula to search the entire States column for a specific value, such as WA or OR. The formula should read ‘=$I2’, with I2 being the cell number. Add another ‘=’ to the formula, and specify another cell on the worksheet to use as a reference, which would be the cell beneath your new header, J2. Leave the ‘$’s in place, as a specific address is needed here. Choose a color option from the dialogue box to highlight your data sets in, and click ‘Okay’.
Now, type the value you want to search for into your reference cell. If you type OR, all of the rows containing OR as a value will be highlighted. Delete that and type WA instead, and all of the rows containing WA as a value will be highlighted.
If you’ve created Conditional Formatting for a worksheet, you can view all of the Conditional Formatting that has been created by clicking the Conditional Formatting option and selecting ‘Manage Rules’ at the bottom of the drop-down menu. This prompts Excel to automatically go through all of the Conditional Formatting that’s been applied to that worksheet. It’s important to note that these are applied in a top-down order, meaning that the Conditional Formatting that appears at the top of the list will be applied first. Changing the order will change which Conditional Formatting will be applied first. If you want to make changes to an existing rule, double-click on that rule to edit it.
To view sales totals that are above or below a certain amount, follow the same initial steps. When creating your formula, specify which column you want to look at using the cell number that contains the first value, in this case, D2. Leave the ‘$’ in place and use a ‘<‘ symbol followed by the amount you’ll be using as your criteria. Your formula will look like ‘$D2<120’. Choose the color you would like to highlight this data in (green is a good choice here as this is a “positive” value) and click apply. Any value that is above $120 will now be highlighted in your chosen color.
A Conditional Formatting feature that is available in Excel 2013 and subsequent versions is the ability to sift through complicated data sets quickly to find a particular trend. The Data Bars feature found in the Conditional Formatting dropdown menu can highlight values based on an average, maximum, or minimum. You have the option of using a gradient fill or a solid fill within these cells.
Depending on how you plan to use this data, you can choose to have the bars display over the number values within the cells, or have the bars displayed in place of the value. This is useful if the data set is being used in a presentation where you would rather not have the numerical values visible to your audience, or you’ve created a chart that shows sensitive data such as salary ranges. The visual representation offered by the bars gets your point across without compromising private information.
Another handy function of Conditional Formatting is the ability to compare values. This is especially useful when you have two sets of data that have been entered manually, and you need to verify that both sets are the same. Highlight the data set, select ‘Create A New Rule’ from the Conditional Formatting drop-down menu, and start your formula. The formula you’ll be using will be a greater than/less than comparison, in this example it would look like ‘$AL4< >$AM4’. Choose the color you would like your data to be highlighted with, and click ‘Apply’. Any cells that don’t match exactly with the second cell in the same row will be highlighted, allowing you to spot mistakes and correct them.
PivotTables and PivotCharts are a great tool for analyzing large and complicated data sets, provided each cell in each column and row contains a value of some kind. Blank cells make PivotTables ineffective. To begin creating a PivotTable, select the data set you want to analyze. If you want to look at the contents of an entire worksheet, click the triangle in the ‘box’ between the ‘A’ column marker and the ‘1’ row marker and Excel will automatically highlight the entire data set.
Click the Insert tab on the home ribbon and select PivotChart. A dialogue box will display asking you to select a data set (which you will have already done) and choose to either create your PivotChart in a new worksheet, or in your existing worksheet. Selecting new worksheet is the simpler option, and will display two boxes on your blank page; a PivotTable box and a PivotChart box. While you can choose to work with one or the other, having both can be very helpful.
Click on the PivotChart box, and a PivotChart Field will appear on the right-hand side of the worksheet. This field will contain all of the headers found in your original data set. Choose which header you want to work with and drag and drop that header into the Axis Categories box at the bottom of the PivotChart Field. Data under your chosen header will appear on the worksheet under the ‘A’ column in place of the PivotTable box. This should be names, geographic locations, department, or any other identifying data.
Choose a second header and drag and drop it into the Values box at the bottom of the PivotChart Field. This header will appear on your worksheet under the ‘B’ column header, and will initially just complete a count, totaling up the number of data entries under each value corresponding to the header placed in the ‘A’ column. A graph will now appear in the PivotChart box to display the values represented on your worksheet.
Typically, the count is not the value you’re interested in reviewing, but rather the values of the data itself. Go back to the Values box at the bottom of the PivotChart Field and click on the header to bring up a menu. Select ‘Value Field Setting’ to bring up a dialogue box that displays several options. Under the ‘Summarize Values By’ tab, choose how you would like the data to be summarized. Selecting ‘Sum’ will provide you with a total of the values selected. If your values are of a specific type such as a dollar value, click on the ‘Number Format’ button at the bottom of the dialogue box and select ‘Currency’. Click okay, and you will now see a dollar value in place of the count on your worksheet, and your PivotChart will reflect those dollar values.
From there, you have several options available to analyze your data set further. By adding another header into the mix, you can dig into the specifics of that data. You can either add another header to the Axis box or add a header to the Legend (Series) box. By using the Legend (Series) box, a second set of bars will appear in the PivotChart, color-coded to match a corresponding legend on the right side of the chart that displays each of the values added to the Legend (Series) box. For example, if you’d initially been analyzing sales by county, you could add industries to the Legend (Series) box and break the county data down further, with each industry displayed in the legend on your PivotChart.
You can change the way the data in your PivotChart is displayed to make it appear cleaner and easier to read using the options under the ‘Design’ tab in the home ribbon. You can also move your chosen headers to a different field box in order to change the way your data is displayed in the PivotChart. If any of the values in our initial dataset change, you can update your PivotChart by selecting the ‘Analyze’ tab in the home ribbon and clicking ‘Refresh’.
A PivotChart is an easy way to share specific data internally among managers and supervisors, especially when that data changes continually. The PivotChart is a graph that displays data visually, while the PivotTable is essentially a condensed version of the original data set, and displays as such.
To create a second PivotChart that looks at different aspects of the data set without altering the chart you’ve already completed, return to the data set worksheet and once again select PivotChart from the ‘Insert’ tab in the home ribbon. This time when prompted to create your chart in an existing worksheet, select that radio button and then click on the arrow box next to the blank field that asks for a location. You can only input a location that matches the worksheet you’re currently in, so in order to create your new PivotChart in the same worksheet as your previous one, you need to collapse the dialogue box and select the correct worksheet from the tabs at the bottom of the screen.
Select a cell in the ‘A’ column, leaving a generous number of blank rows between the data already on the worksheet, and the start of your new data in case changes are made to the first PivotChart. Click the arrow box in the ‘Create PivotTable’ dialogue box to un-collapse it and click ‘Okay’. Repeat the previous steps with your new values and criteria.
If you’d like to look at the same values with more specific criteria, you can drop a header, such as employee gender, into the Filters box at the bottom of the PivotChart Fields section, with the number of employees in the Values box and the geographic area in the Axis box. Change the number of employees to display as a Sum instead of a Count. On your PivotChart, you will see a box at the top left displaying the header you dropped into the Filters box. Click on that box to display the various criteria under that header to have only data that fits that filter criteria appear in your chart.
These are just a few of the ways you can put Conditional Formatting and PivotCharts to work for your business. These functions can be applied to virtually any type of data you can come up with, which means that once you have these functions mastered, there is no limit to what you can do with Excel data.