NEWVena-Logo NEWVena-Logo
Request Demo

10 Excel Tips & Tricks Everyone Should Know

January 30, 2020 |

Excel has dominated the business world unlike any other software since it arrived on the scene in the 1980s. Even today, if you try to take Excel away from finance folks, don’t be surprised if you’re faced with a mutiny.

Excel has become so important that you’d be hard-pressed to find a self-respecting finance professional who can work effectively without it. Consider this for a second—when was the last time you went through a budgeting cycle that didn’t involve any spreadsheets?

Beyond the finance department, it’s clear that anyone working with numbers in just about any capacity can harness the power of Excel. Huge, complex spreadsheets are now common in the modern workplace across all business functions, which really isn’t surprising considering Excel’s seemingly limitless capabilities. Even as organizations grow, their reliance on Excel isn’t likely to diminish. The familiarity and ease of use that spreadsheets provide is undeniable.

While much has been written about Excel and how to use it, there are many tips and tricks that even the most committed spreadsheet gurus might not be aware of. Some of these tips reduce the number of clicks required for common activities, while others increase Excel’s overall power at your fingertips.

Here are 10 tips to help you supercharge your Excel experience:

1. Use the Format Painter

The Format Painter makes it easy to copy the formatting from one cell and apply it to another cell or group of cells elsewhere on the spreadsheet. Here’s how to do it:

Another helpful trick with the Format Painter is the shortcut for copying formatting to multiple cells at a time, or multiple non-adjacent groups of cells:

2. Select Entire Spreadsheets, Columns or Rows

This feature comes in handy when you want to perform actions on whole spreadsheets, columns or rows all at once. Let’s say, for example, that you were trying to wipe out the contents of an entire spreadsheet, or clear the values from a specific row or column. You might find yourself performing this task at the beginning of a budgeting cycle when you’re preparing the spreadsheets to receive fresh data.

To select an entire spreadsheet:

To select an entire column:

To select an entire row:

3. Hide Small Amounts of Data

Sometimes it’s necessary to hide spreadsheet data that isn’t relevant to the reader, but still needs to be there to ensure accurate formulas and results, as is often the case with detailed cash flow projections. For aesthetic reasons, or to avoid having the values changed accidentally, you might want to hide the individual lines that lead up to a major revenue item.

To hide an entire column:

To hide multiple columns:

To hide an entire row or multiple rows:

To hide specific cells:

4. Add More Than One New Row or Column

Use this feature to add multiple rows and columns to your spreadsheet quickly, instead of just adding them one at a time. Let’s say you’ve just completed a financial statement, but your boss wants to see new rows of subtotals underneath the fixed expenses. You’ll need to insert some new rows!

To add multiple rows:

To add multiple columns:

5. Use Data Validation to Make Drop-Down Menus

Data validation using drop-down menus is a useful feature that ensures users are only able to enter values that are predefined from a separate list. Let’s say, for instance, that you’re working on an expense report and you want to include a column that categorizes your line items from a list of specific expense categories (transportation, meals, supplies, entertainment, etc.) You’d need to create a drop-down box so that instead of manually typing the category names into each cell, you can input them quickly, and only choose from your list options. Here’s how:

6. Transpose Data from a Row to a Column

Transposing data between rows and columns is useful for when you want to rearrange your data after it’s been inputted. For example, on a revenue analysis statement, you may have entered product names in a row but later realized that they would be better off in a column. Instead of moving the contents of each cell into a column manually, you can transpose the data like this:

7. Use Pivot Tables

Excel’s pivot table feature is a data summarization and analysis tool that allows users to extract relevant metrics from large, complex data sets. With pivot tables, you can slice and dice large quantities of data in order to better understand what those numbers are telling you. Let’s say you were looking to analyze some product sales details, for example. Instead of sifting through a large spreadsheet, you could summarize the sales data with a pivot table that only focuses on relevant fields like product ID, units sold, revenue and more. 

To start the process, you need to have an open spreadsheet containing the data you want to summarize. Next:

8. Use Conditional Formatting

Conditional formatting allows you to format cells automatically when their values meet certain conditions. For example, you might want to highlight values greater than 1,000 with a specific color in the variance column of your budget vs. actuals expense comparison. Follow these steps to color values greater than 1,000 a light shade of red:

9. Turbocharge Excel with Data Integration

As companies grow, finance leaders might start thinking that they’ve outgrown Excel due to its scalability concerns, lack of automated workflows, and version control issues. But scrapping Excel completely just isn’t an option for most finance professionals, so why give it up when you can turbocharge Excel with data integration?

Modern FP&A software leverages the power and popularity of spreadsheets by linking Excel to existing data source systems and providing one centralized platform for the full spectrum of finance functions. These integration capabilities come with significant advantages, such as:

10. Remove Duplicate Data

Sometimes it’s necessary to remove duplicate data from a spreadsheet due to entry errors. This might happen if you’re dealing with multiple versions of the same spreadsheet and accidentally input the same data twice. Instead of finding and deleting the duplicates manually, you can fix this fast with just a few clicks. Here’s how:

The Power of Excel for Finance

With its numerous features, ease of use and immeasurable influence on the way people work, Excel’s place in the finance world is hard to unseat. But most users still haven’t tapped into Excel’s full power. And for the companies that actually have taken the next step and streamlined their spreadsheets with the right tools, reverting back to manual processes would be unthinkable.

Keep in mind that these tips are just the tip of the iceberg. To truly make the most out of Excel, learn what data integration can do to turbocharge your budgeting, financial planning and analysis, account reconciliations, and everything in between.

Ready to take your spreadsheets to the next level? Download our eBook and learn how to keep on winning with Excel.

Recommended Posts

Ease of Use the #1 Driver of BPM Software Success, Survey Finds

June 17, 2020 |

Read More

How to Make an FP&A Mindset Part of Your Company DNA

June 16, 2020 |

Read More

3 Reasons Why the Time is Now for Finance to Adopt AI

June 11, 2020 |

Read More