• FREE RESOURCES
        • Accounts Payable
          Finally! The trick to securing greater T&E compliance
          Benefits
          Rooting out folks who don’t belong on your health plan: A 6-point dependent audit checklist
          IT
          3 costly misconceptions about biz email compromise
          Credit and Collections
          Collecting via email: 4 must-make moves in your subject line
          Accounts Payable
          5 Tough-to-spot signs that an invoice is fake
  • PREMIUM CONTENT
        • Staff management
          120 Proven Communications Tips for Today’s CFO
        • Payroll
          Handling Nonexempt Employee Pay: Stay Compliant and Avoid DOL Audits
          Accounts Payable
          T&E Best Practices: Complete Guide to Ensure Compliance
          Payroll
          Payroll Best Practices: 4 Ways to Save Time and Money
        • Staff management
          Email Best Practices: A 6-Question Quiz
          Staff management
          Innovative Communications Strategies: An Email Case Study
          Staff management
          A 5-part Framework for Successful Workplace Communications
        • SEE MORE
          PREMIUM RESOURCES
  • CORONAVIRUS RESOURCES
  • LOGIN
  • SIGN UP FREE

CFO Daily News

  • FREE RESOURCES
        • Accounts Payable
          Finally! The trick to securing greater T&E compliance
          Benefits
          Rooting out folks who don’t belong on your health plan: A 6-point dependent audit checklist
          IT
          3 costly misconceptions about biz email compromise
          Credit and Collections
          Collecting via email: 4 must-make moves in your subject line
          Accounts Payable
          5 Tough-to-spot signs that an invoice is fake
  • PREMIUM CONTENT
        • Staff management
          120 Proven Communications Tips for Today’s CFO
        • Payroll
          Handling Nonexempt Employee Pay: Stay Compliant and Avoid DOL Audits
          Accounts Payable
          T&E Best Practices: Complete Guide to Ensure Compliance
          Payroll
          Payroll Best Practices: 4 Ways to Save Time and Money
        • Staff management
          Email Best Practices: A 6-Question Quiz
          Staff management
          Innovative Communications Strategies: An Email Case Study
          Staff management
          A 5-part Framework for Successful Workplace Communications
        • SEE MORE
          PREMIUM RESOURCES
  • CORONAVIRUS RESOURCES
  • Accounts Payable
  • Credit and Collections
  • Payroll
  • Accounting
  • Benefits
  • Finance Technology
  • More
    • Employment Law
    • Strategy
    • Policy and Culture
    • Fraud
    • Payments and Transactions
    • Budgeting and Forecasting
    • Banking
    • Staff Management
    • Cost Control
    • Supply Chain
    • IT

3 next-level uses for Excel that will improve data analysis, reporting

Alyssa Pedrick
by Alyssa Pedrick
May 31, 2019
  • Strategy
3 minute read
  • SHARE ON

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:

  1. Select your data set of expenses.
  2. On the Home tab, in the Styles group, click Conditional Formatting.
  3. From the drop-down menu, select Highlight Cell Rules, then Greater than.
  4. Enter your values (e.g., $300) and select your fill (e.g., light red fill).
  5. 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:

  1. Select your data set. (Or type Ctrl + Ato select the entire spreadsheet.)
  2. Click the Data tab. In the Sort & Filter Group, click Advanced.
  3. 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.)
  4. Make sure the range fields are filled out correctly (matching the data you selected earlier).
  5. 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:

  1. Treemap charts show a hierarchical view of data, with categories (“tree branches”) and subcategories.
  2. Sunburst charts also lend well to hierarchical data. They visually illustrate how the outer rings relate to the inner rings.
  3. Histogram charts demonstrates data frequency. Each column shows how data is distributed in that “frequency bin.”
  4. Box and Whisker charts show data distributed in They also spotlight the data average (mean) and any big outliers.
  5. Waterfall charts display a “running total” of data as values are added or removed.
  6. 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.

 

 

Alyssa Pedrick
Alyssa Pedrick
Alyssa, a member of the CFO Daily News staff, has written extensively on business and finance for several years. She has produced content for accounts payable professionals and finance executives and has developed whitepapers and infographics for the finance and accounting industry.

Keep Up To Date with the Latest Finance News

With CFO Daily News arriving in your inbox, you will never miss critical stories on accounting, benefits, payroll & employment law strategies.

Sign up for a free CFO Daily News membership and get our newsletter!
  • This field is for validation purposes and should be left unchanged.
CFO Daily News Logo
  • ABOUT CFO DAILY NEWS
  • ADVERTISE WITH US
  • WRITE FOR US
  • CONTACT
  • Accounting
  • Benefits
  • Payroll
  • Policy and Culture
  • Employment Law
  • Fraud
  • Finance Technology
  • Accounts Payable
  • Credit and Collections
  • Strategy
  • Payments and Transactions
  • Budgeting and Forecasting
  • Banking
  • Staff Management
  • Cost Control
  • Supply Chain
  • IT

CFO Daily News, part of the SuccessFuel Network, provides the latest Finance and employment law news for Finance professionals in the trenches of small-to-medium-sized businesses. Rather than simply regurgitating the day’s headlines, CFO Daily News delivers actionable insights, helping Finance execs understand what Finance trends mean to their business.

Privacy Policy Terms of Service
Copyright © 2021 SuccessFuel

WELCOME BACK!

Enter your username and password below to log in

Forget Your Username or Password?

Reset Password

Lost your password? Please enter your username or email address. You will receive a link to create a new password via email.

Log In

preloader