Advanced tools for analytics, reporting and data visualization are great. But in many cases, you can achieve similar ends by using tried-and-true Excel.
Chances are, your finance staffers already know the program extremely well. (And unlike other some programs, it doesn’t put a big strain on your company’s pocketbook.)
Check out three ways you and your finance staffers can take their Excel skills up a notch to analyze data and help your team make more strategic decisions.
1. Evaluate spend and trends
You know examining company spend, from T&E expenses to department budgets, is one way to identify improvement opportunities. Excel’s Conditional Formatting feature can help with that.
Let’s say you wanted to find expenses that came close to or exceeded a certain spend limit. You would:
- Select your data set of expenses.
- On the Home tab, in the Styles group, click Conditional Formatting.
- From the drop-down menu, select Highlight Cell Rules, then Greater than.
- Enter your values (e.g., $300) and select your fill (e.g., light red fill).
- Click OK. All the values greater than your number will be highlighted.
In this example, seeing what expenses came close to or exceeded spend limits could help you:
- Decide whether these limits need to be adjusted.
- Spot potential fraud or risks. (Often, fraudulent purchases are just below company limits.)
- Identify specific employees or departments your team may need to review policies
You can also use Highlight Cell Rules to find numbers less than, equal to, etc.
2. Note outliers in a data set
Sometimes, those one-off outliers in your financial data can provide just as important insight as regular trends and patterns.
To find unique values (ones that appear only once in your data set), follow these steps:
- Select your data set. (Or type Ctrl + Ato select the entire spreadsheet.)
- Click the Data tab. In the Sort & Filter Group, click Advanced.
- When the dialog box appears, you can first choose to either “filter the list, in-place” or “copy to another location.” (If you choose the second option, add where you want the list of unique values to go in the “Copy to:” field.)
- Make sure the range fields are filled out correctly (matching the data you selected earlier).
- Click the checkbox that says, “Unique Records Only,” then click OK.
3. Add better visuals to tell a story
Sharing important financial data with other business units or executives? There are six new chart options to display data in fresh, and perhaps more impactful, ways.
Here’s a roundup of the six additional charts available if you have Excel 2016 or a newer version:
- Treemap charts show a hierarchical view of data, with categories (“tree branches”) and subcategories.
- Sunburst charts also lend well to hierarchical data. They visually illustrate how the outer rings relate to the inner rings.
- Histogram charts demonstrates data frequency. Each column shows how data is distributed in that “frequency bin.”
- Box and Whisker charts show data distributed in They also spotlight the data average (mean) and any big outliers.
- Waterfall charts display a “running total” of data as values are added or removed.
- Funnel charts present values across different stages of a particular process.
To use any of these charts, click the Insert tab, then Recommended Charts and All Charts.
You can see more details on these new charts, as well as other chart options, on Microsoft’s chart types web page.
