During our latest installment of the “Excel Like a Pro” Webinar Series, Nick O’Neil of Cooper Technologies tackled two great ways to look at your Excel data: 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 can be found in the “Data” tab of the home ribbon, right next to “General Number Formatting.”
To make this easier to follow, we’ll use sales orders as an example:
- Highlight the data set you want to format (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 Here you’ll find a number of automated options to choose from (such as highlighting “Rules That Are Equal To” by typing WA into the dialogue box).
- Conditional Formatting will highlight each cell that’s ‘equal to’ WA in the States column.
To locate sales data related to a particular state:
- Select a new blank cell and type a new header such as “highlight ” You’ll be inputting your new data in the cell below this space. (If your new header is cell J1, you’ll be using J2 for this.)
- Select the entire sales order data set, and select “Create A New Rule” from the Conditional Formatting drop down menu. You’ll be creating a new formula that selects the cells to format. (The Excel formula will look through your highlighted data and select appropriate data to highlight based on the values you 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 that a “$” will appear in your dialogue box. The “$” is what defines a specific address within a cell. 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 “$” 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. (Choose the cell beneath your new header, J2.)
- Leave the “$”s in place, as a specific address is required here.
- Then, choose a color option from the dialogue box to highlight your data sets, and click “Okay.”
- Next, type the value you want to search for in your reference cell. (If you type OR, all of the rows containing OR as a value will be highlighted.) If you delete OR and type WA instead, all of the rows containing WA will be highlighted.
You can now view all Conditional Formatting that was 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 you applied to your 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 the “<” symbol followed by the amount you’ll be using as your criteria. Your formula will look like “$D2<120”.
- Choose the color you’d like to highlight this data (green is a good choice, as this is a “positive” value) and click apply.
- Any value that’s above $120 will now be highlighted in your chosen color.
A Conditional Formatting feature, that’s available in Excel 2013 and subsequent versions, is the ability to quickly sift through complicated data sets to find a particular trend.
- With the Data Bars feature found in the Conditional Formatting drop-down menu, you can highlight values based on an average, maximum, or minimum.
- You also 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 numerical values within the cells, or displayed in place of the value. This is useful if the data set is being used in a presentation where you would rather the numerical values not be 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 in Conditional Formatting is the ability to compare values. This is especially useful when you have two sets of data that were 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 enter 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’d 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 & PivotCharts
These are great tools for analyzing large and complicated data sets, provided the cells in each column and row contain a value of some type. Blank cells render PivotTables ineffective.
Here’s how to create 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. 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).
- Choose to either create your PivotChart in a new worksheet, or in your existing worksheet. (Selecting a 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 named, geographic locations, department, or 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 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 appear in the PivotChart box that displays 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 the “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 a total of the values selected.)
- If your values are of a specific type such, as a dollar, click on the “Number Format” button at the bottom of the dialogue box, and select “Currency.” Click “Okay,” and you’ll see a dollar value in place of the count on your worksheet. Now 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, the second set of bars will appear in the PivotChart. It will be 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 been analyzing sales by county, you could add industries to the “Legend (Series)” box and break this data down further according to the industry displayed in the legend of your PivotChart.
You can change the way the data in your PivotChart is displayed to make it appear cleaner and easier to read.
- Use the options under the “Design” tab in the home ribbon.
- You can also move your chosen headers to a different field box to change the way your data is displayed in the PivotChart.
- If any of the values in your initial data set 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 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
- Click the arrow box in the “Create PivotTable” dialogue box to uncollapse 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:
- Drop a header (such as employee gender) into the “Filters” box at the bottom of the PivotChart “Fields” section, along 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 so only data that fits your filter criteria appear in the chart.
These are just a few of the ways you can put Conditional Formatting, PivotTables and PivotCharts to work for your business. These functions can be applied to virtually any type of data you want. Once you’ve mastered these functions, there’s no limit to what you can do with Excel data.
Want to learn more about the helpful features Excel offers? Get in touch with the Microsoft Experts at Cooper Technologies at firstname.lastname@example.org or (855) 303-5378 . And, be sure to ask about upcoming Webinars to help your business excel!