Making Excel Work for FP&A excel, microsoft excel, making excel work, 5 hidden excel tips, hidden excel tips, excel for FP&A, excel for finance, excel tips for fp&a, fp&a excel, finance excel

Select Only Cells That Are Visible

Eliminate Outliers, Zeros and #N/A Errors

Turn Off the GETPIVOTDATA Prefix

Create Data Analysis Expressions (DAX)

Build Your Own Power Query

Click on each object to learn how to perform the hidden task.

While Excel is an integral part of the FP&A department, it’s easy to get stuck in a rut with an application you use so frequently. But there’s a world of limitless potential with Excel that you probably never knew existed. Here are five hidden secrets to help make the most out of the software.

Making Excel Work for You

AFP GUIDE TO

Underwritten by

Download the Guide for More Excel Tricks

Select Only Cells That Are Visible

NEXT TIP

BACK TO HOME PAGE

Highlight the cells Go to Find & Select Go to Special Visible cells only Copy and paste

When data is grouped or subtotaled, there are rows that aren’t displayed onscreen but are captured with a standard copy and paste. Instead, to only select what you see:

Download the Guide for More Excel Tricks

Eliminate Outliers, Zeros and #N/A Errors

NEXT TIP

BACK TO TIP 1

A great tool for data cleanup Type “Ctrl+H” to find and replace Enter what you want to find, then leave the replace field empty It will find the value and replace with an empty cell

Download the Guide for More Excel Tricks

Turn Off the GETPIVOTDATA Prefix

NEXT TIP

BACK TO TIP 2

This will make it easier to write PivotTables. For Excel 2007 or 2010, go to “Options” tab (“Analyze” in Excel 2013 or later) Click on the drop-down arrow C Check off “Generate GETPIVOTDATA”

Download the Guide for More Excel Tricks

Create Data Analysis Expressions (DAX)

NEXT TIP

BACK TO TIP 3

Excel can build formulas and operators that can be combined into more complicated formulas and expressions. Importing data into a workbook is the easy part—DAX allows you to create new information from data already in the model.

See How to Perform a DAX Live

Build Your Own Power Query

BACK TO TIP 4

FP&A Guide to Making Excel Work for You

DOWNLOAD

Underwritten by Vena Solutions

Want to learn more about Excel?

Power queries are add-ins that can help build your own data acquisition or transformation tools. They allow you to identify relevant data from multiple, disparate data sources and combine it, to prepare for further analysis. The queries you create can then be shared with others in the organization easily, via “Search.”