Even Pros Could Learn a Thing or Two From These 8 Advanced Excel Tips

Almost every Excel user has one thing in common: they want to know more. It’s impossible to list all of the different ways to calculate values, give data a new look, and so on in Excel – even when entire books and courses have been developed about the subject! However, even some of the more advanced Excel tips are accessible to everyone.

If you’ve been looking for some Excel advice to take your user experience to the next level (even if you’re already a pro), you’re in the right place! Let’s take a look at some of the top Excel tips that you can employ no matter how you use the software.

1. Paint Cells to a New Format

Let’s imagine you want to modify not just the wrapping in a cell, but the complete look as well—the font, the color, or anything else. You also want to apply it to a large number of other cells. The Format Painter tool – which looks like a paintbrush and is found on the Home tab – is the key. 

Select the cell that has the format you want to apply to other cells and click the Format Painter icon. Then, select the other cell to “paint” that format. Their appearances will match, but the value won’t change. Do you want to use it on several cells and ranges? Just double-click the paintbrush icon, then you can apply the format to multiple cells or ranges. We teach the format painter in Lesson 3 of our free course.

2. FlashFill

Using FlashFill isn’t exactly an advanced Excel secret, but it is often overlooked. When you start typing values in a new column that are derived from existing columns, FlashFill can help speed this up. For example, if you have a single column that contains both first and last name and are trying to create a new column with just the first name, FlashFill can help.

You basically type in the first column value. This sort of gives Excel a sample, or an example of what you want for the rest of the column. Then, select Data > FlashFill. If Excel can detect a pattern, it will extend the pattern down (updating the entire column).

This works for many basic patterns, including splitting columns, combining columns, changing case, and extracting text or date parts. You can learn more about FlashFill here.

3. Sum Cell Values Across Different Sheets

This feature, called 3D Sum, is useful when a workbook contains numerous sheets with the same basic layout (think quarterly or yearly statements). For example, let’s say that cell A2 contains the sales value for the year, and this is consistent for all annual sheets (named Y1, Y2, Y3, and so on).

In a new worksheet, type the formula =SUM(‘Y1:Y10’!A2) in a cell. This tells Excel to use the SUM function to add up the values in A2 for all sheets in the range of sheets between sheet Y1 and sheet Y10. The total of all ten years will be the result.

4. Use Conditional Formatting

Conditional Formatting tells Excel to format a cell based on a rule or set of rules. It is fairly easy to get started with Conditional Formatting, but just know that it has a lot of depth and many applications.

A simple example is to change a cell fill to red if it contains the word “error.” Or, to change the format of cells with a value of less than zero. Or, to format cells that contain duplicate values … in fact, you can use this conditional formatting rule to perform reconciliations (comparing two lists).

More advanced rules can be created by using a conditional formatting formula. This enables you to format a cell based on the value in another, and opens up many interesting possibilities.

5. Add Drop-Down Menus

One of the best advanced Excel tips to help with efficiency is to create a drop-down menu in a cell. Start by entering all of the menu choices in a column.

Then, select the cell location for the drop-down list and click Data > Data Validation. 

In the resulting Data Validation dialog box, go to the Settings tab, then to the Allow box, and then select List. Select the Source field and choose your range of menu choices. When you hit OK, you’ll notice a drop-down button inside the cell! You can click it (or select Alt + Down Arrow) to reveal the list of choices.

6. Save Your Previous Charts as Templates

Excel has more types of charts than you’d probably ever use, but it’s nearly impossible to find a default chart that’s ideal for one specific type of presentation. Luckily, Excel lets you totally personalize them to fit whatever type of presentation you need.

The only downside is that having to recreate your customized graph over and over is a pain. If you find yourself using a similar layout often, saving it as a chart template will shave so much time off your presentation prep!

Once you’ve created your beautiful chart and have applied all of your customizations, simply right-click the chart itself and select “Save as Template.” In the resulting Save Chart Template dialog, provide a name for the chart and Excel saves it as a *.crtx file type.

Then, whenever you are ready to create a new chart with that template, select the data range as normal, and then open the Insert Chart dialog box (Insert > Recommended Charts). In the Insert Chart dialog, select the All Charts tab and then the Templates category. Your chart template (or templates) will appear. Pick the one you want and OK. Nice!

7. Use PivotTables to Summarize Data

Using PivotTables is one of the most popular advanced Excel tips – and for good reason! If you aren’t familiar with PivotTables, they’re essentially summaries of your data set that provide aggregate values like sum and count. 

For example, if you have all of your employee’s paycheck amounts listed for a full year, a PivotTable might help you compute the total for each employee. Anyone who works with large amounts of data should get some practice with PivotTables as they’ll make summarizing your data a breeze. 

If you’re looking for a place to start, check out our Top 5 PivotTable tutorials.

8. Use Dynamic PivotTable-style Reports

In the past, there were two basic methods for creating reports in Excel. You could either enter the report labels and compute the report values using formulas, or you could use a PivotTable. Both options had advantages and disadvantages. The report type would have to be chosen based on the context of our workbook. 

We recently found a way to create an all-new, dynamic, PivotTable-style report that automatically updates data and grows to cover new items, so there’s no need to refresh manually! This is a more advanced Excel tip, but it’ll save you a lot of time and clicks in the long run.

Do you know any advanced Excel tip that has saved you time and clicks? Let us know in the comments!