10 Excel Tips & Tricks Everyone Should Know

loading="lazy"

Tools to Drive Your Business Forward

Vena automates financial reporting processes so you can collect and analyze data to make smart, data-driven business decisions.

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:

• Highlight the cell you want to copy the formatting from.
• Then click on the Format Painter icon, which is located in the Home tab near the upper-left corner.
• Hover the cursor over the cell you want to format and you'll see an active paintbrush. Apply the new formatting by clicking the cell. You could also click and drag the cursor if you want to apply the new formatting to multiple adjacent cells all at once.

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:

• Highlight the cell you want to copy the formatting from.
• Instead of clicking the Format Painter icon once, double click on it.
• Then single click the cell you're copying to. The paintbrush will remain active so you can continue copying to other cells or groups of cells.
• When finished, press the Escape key to get rid of the active paintbrush.

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:

• Click on the top left corner of the spreadsheet where the columns and rows meet, right below the name box. (Blue box in the picture below)

To select an entire column:

• Click on the column header, which is labeled with a letter. (Red box in the picture below)
• You can select multiple columns by clicking and dragging the cursor over two or more adjacent columns.

To select an entire row:

• Click on the row number. (Purple box in the picture below)
• You can select multiple rows by clicking and dragging the cursor over two or more adjacent rows.

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:

• Right click on the column header.
• The entire column will now be selected and a menu box will pop up.
• Click Hide.

To hide multiple columns:

• Select multiple columns.
• Right-click anywhere on the highlighted area.
• In the menu box, click Hide.

To hide an entire row or multiple rows:

• You can hide entire rows of data by following the same instructions for columns, but clicking on the row numbers instead of the column headers.

To hide specific cells:

• In some cases, you might want to hide the data from one cell or multiple cells in various parts of the spreadsheet. To do this, just select the cells you want to hide, then change the Font Colour (not the Fill Colour) to white. The data will be invisible unless that specific cell is selected.

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:

• Click and drag the cursor over multiple row numbers, i.e. the number of blank rows you want to add in that spot.
• Keep your cursor over the highlighted area and right-click.
• Then, in the ensuing menu box, select Insert. Your data will be shifted down to accommodate the blank rows you've just inserted.

To add multiple columns:

• You can add multiple columns using the same instructions as above if you click and drag your cursor along the column headers instead. Data will be shifted to the right to accommodate the blank columns when you insert them.

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:

• Create the list either in the same spreadsheet where the drop-down menu will be located, or on a separate spreadsheet within the same file.
• Select the first cell in the column where you want the drop-down formatting to begin. Under the Data tab, look to the right for the Data Tools section and click on the Data Validation icon. (See below)
• In the ensuing Data Validation box under Validation Criteria, click the Allow box.
• A drop-down list menu appear. Select List.
• Another box labeled Source will now be visible. Click on the box.
• Now, move your cursor to the top of the list of category names you've created. Click and drag the cursor down to the end of the list. The source box will now display the cell range where your  list is located. Click OK.
• A validation drop-down menu will be created in your selected cell. To apply the drop-down formatting to multiple cells, just copy over the formatting using the Format Painter.

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:

• Select the row data you want to transpose, then right-click and select Copy.
• Place your cursor on an empty cell where you want your new column will start, and make sure there are enough blank cells directly below it for your data.
• Right click on this first cell and select Paste Special. Then click on the Transpose box at the bottom right of the ensuing pop-up menu and hit OK.

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:

• Under the Insert tab, click the PivotTable icon.
• The next menu box is where you'll select the data you want to summarize. Use your cursor to select either the entire spreadsheet, or a specific range of cells. Select New Worksheet below to build the pivot table on a new sheet and then hit OK. Alternatively, you could select Existing Worksheet to keep the pivot table on the same spreadsheet as your original data.
  
• Excel will open a new sheet with a PivotTable area on the left side and PivotTable Fields on the right side.
• All the column names from your original data will be displayed under the Field Name section in the PivotTable Fields box on the right side of the screen.
  
  
• Under PivotTable Fields, check the boxes of all the specific fields that you want to include in your pivot table.
• The data will automatically arrange itself in the table on the left.
• Review the pivot table to make sure the relevant fields are summarized and formatted the way you want.
  
  
• If it doesn't look right, then go back to the right side and review the four boxes: Filters, Columns, Rows and Values.
• You can move the fields from one box to another by dragging and dropping. Watch the table on the left of the screen to see how it gets arranged as you experiment with these fields.
• Once satisfied, save the file. Keep in mind that as you update the data on your original spreadsheet, the pivot table will also update automatically.

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:

• First, select the column you want to apply this rule to.
• In the Home tab, look to the right for the Styles section and click on the Conditional Formatting icon.
  
  
• Then select Highlight Cell Rules and Greater Than.
  
  
• A pop-up box will appear reflecting your previous selections. In the empty box, enter 1000 and use the drop-down menu on the right to select a specific formatting option. Click OK.
  
  
• You can always create other formatting rules using the available options. As you get more proficient with Excel, you'll even be able to create custom formatting conditions with your own unique formulas.

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:

• Seamless data transfers between Excel and other applications.
• Eliminating the need to manually populate spreadsheets and manually re-enter spreadsheet data into other programs.
• Synchronizing Excel with other programs to transfer data on demand, or at predetermined times.
• Allowing users to work on native Excel and access live data from the company's enterprise resource planning system (ERP).
• Enabling drill-down and/or drill-through capabilities.
• More robust reporting capabilities.

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:

• Select the columns where the duplicates are located.
• Under the Data tab in the Data Tools section, click on the Remove Duplicates icon.
• Follow the prompts thereafter.

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.

FP&A software powering your plan to grow

Drive smarter, more data-driven business decisions with numbers you can trust.