50 Hacks To Help You Become an Excel Master

If you’re running a business, you’ll often need to assess the cost of financing your operations. With Excel’s simplified Weighted Averages formula, you can calculate the average rate of return your company must generate to keep your investors (or just yourself) happy.

Here’s the formula:

=SUMPRODUCT(X:X,X:X)/SUM(X:X)

Take a look at the GIF on how to set this up.

IF-THEN statements in Excel can help you automatically check data using predetermined criteria. For example, say you want to evaluate the performance of your employees based on the total amount of revenue they drive. You could use an IF-THEN statement to say, if a sales associate drives less than $2,000 in a quarter, then they need additional training.

Here’s the formula:

=IF(logical_test, [value_if_true], [value_if_false])

Using COUNTIF, you can have Excel return the number of cells that meet certain criteria or have a certain value. For example, you could use COUNTIF to evaluate the number of products sold across different categories.

Here’s the formula:

=COUNTIF(range, criteria).

Correlation measures the relationship between two variables. It’s especially important when you’re handling multiple variables and want to know how they impact an outcome. For example, say you want to know the correlation between the number of hours of training given to a sales associate and the amount of revenue they drive.

To do this, you would use the following formula:

=CORREL([range_#1, range_#2). The closer your correlation is to 1 or -1, the stronger the relationship.

Excel will typically make formulas relative so that if you copy and paste it from one cell to another, it’ll adjust its components. However, sometimes you’ll need to make a relative formula absolute.

For example, say you have two separate commissions based on whether or not a sales associate reaches a certain target. Neither the target nor the commission rate will change, so the formula might look like this:

=IF(sales_amount >=$D$1, sales_amount * commission_rate_1, sales_amount * commission_rate_2)

By using dollar signs, Excel will keep these values the same as you drag and drop the formula.

Sometimes, you might want to merge two separate cells into one. For example, you might have separated customers by their first and last name and want to create a row that combines these into one full name.

You can do this easily using the following format, where A1 is “First Name” and B2 is “Last Name”:

=A1&” “&B2

You can customize what goes between the quotation marks depending on what you want to include between the two cells.

Basic Excel IF formulas can help you automate certain tasks, which can save you time. For example, say you want to note which customers get a discount based on how your sales team has categorized them. In other words, if the customer is engaged, then they will get a certain offer.

Here’s the formula:

=IF(logical_test, value_if_true, [value_if_false])

Think of PivotTable as useful tools for quickly analyzing your Excel data. It can show you a number of insights, such as trends or patterns in your data. It’s most useful when you’re working on:

→ Finances: They can provide you expense summaries, profit margins, and more.
→ Sales: They can help you determine top-performing sales associates, best-selling products, and more
→ Marketing: They can analyze key campaign metrics, such as conversion rates, click-through rates, and more

Here’s how to create them.

1. Highlight your data
2. Go the Insert Tab and select PivotTable
3. Check relevant rows and data you want to include

Chances are, you’ll want to customize the look of your PivotTable. To do this, simply:

1. Click anywhere on your PivotTable
2. Select Design followed by More in the PivotTable Styles gallery
3. Pick your preferred style choice

You might need more than summary statistics from your PivotTables. Fortunately, you can by inserting your own set of formulas.

1. Click anywhere on your PivotTable
2. Select PivotTable Analyze
3. Underneath Calculations, click Fields, Items, & Sets
4. Click Calculated Field
5. Under name, write a name (e.g., Sales Commission)
6. Under formula, insert the formula. To include data on the sheets, click Fields followed by Insert Field. (e.g., to calculate commission for a sales representative, you could insert the formula: = Sales * 10%)
7. Finally, click Add

With XLOOKUP, you can search within a range or an array and have Excel return a corresponding value in the same position of another range or array.

Here’s the formula: =XLOOKUP(What you’re looking up, where you want to look (e.g., range), the column number in the range containing the value, 1/TRUE = Approximate Match OR 0/FALSE = Exact Match).

In technical terms, =XLOOKUP(lookup value, table_array, column_number, Approximate match (TRUE) or Exact match (FALSE)).

To summarize, you’ll need:

1. The value you’re looking for (also known as the lookup value)
2. The range where you’ll find the lookup value
3. Within the range, the column number that contains your return value
4. 1 or TRUE to indicate an approximate match (can also leave blank) or 2 or FALSE to indicate an exact match

Referred to as “Get & Transform” in Excel, Power Query enables you to connect to external data and modify it to fit your needs. This includes removing columns, changing data types, merging tables, and other useful modifications.

Power Queries can be helpful when dealing with disconnected sets of data. For example, you might want to analyze your sales performance across multiple channels. That means taking data from online platforms, in-store transactions, and other reports. With Power Query, you can combine and clean this data to create a unified sales report.

Excel breaks down Power Query into four steps: Connect, Transform, Combine, and Load. Let’s go through each one-by-one.

Connect:
1. Start by selecting Data > Get Data > From File. Then, import whichever data file you plan on modifying in Excel

Transform:
2. Use the Power Query Editor, a window dedicated to transforming your imported data. If it’s not there, select Data > Get Data > Get & Transform Data Group

Combine:
3. a. In the Power Query Editor, click Append to create a new query with all rows from a first query and second query.
3. b. Select Intermediate Append to create a new query every time you select Append. Or select Inline Append to append data to your already existing query

OR

4. In the Power Query Editor, click Merge to create a new query from two existing queries.

Load:
5. a. Using the Power Query Editor, select Home > Close > Close and Load

5. b. Select Queries & Connections. Right-click a query and select Load To

Sometimes, visuals can help you better understand data than numbers. Heat maps in Excel can enable you to visualize large data sets as easy-to-interpret graphs. For example, you might want to use a heat map to analyze sales data to determine seasonality for certain products.

To create a standard heat map, first highlight the data you plan to visualize. From the Home tab, select Conditional Formatting and then hover over Color Scales. There, you’ll see several color codes to select from.

Chances are, you’ll likely want to use one with either green at the top (which visualizes higher values as green) or red at the top (which represents higher values as red).

If your company operates in multiple states or countries, you can use Excel to create geographical heat maps. You might want to, for example, see which states bring in the most revenue for your company.

To create a geographical heat map, first select your data alongside their headers. Then, go to Insert and select Maps. After selecting the icon beneath Filled Maps, Excel will output a geographical heat map based on the data selected.

Excel’s What-If analysis tool has multiple uses, including Goal Seek, which can help fill in missing values as long as you know the end result. For example, you might want to know exactly how many units you need to sell to hit a certain year-end target in Q4.

First, set a formula in a certain cell. This might be something as simple as =SUM(). Highlight that cell. Select Data > What-If Analysis > Goal Seek. From there, insert the number you wish to attain and the missing value. Goal Seek will then automatically populate the value needed to reach that goal.

Using Scenario Manager, you can create a table to determine the outcome of different scenarios. For example, your supplier might lower your costs if you purchase a certain amount of units, thus changing your overall profit margin. You could use Scenario Manager to evaluate your potential profit margin depending on the number of units you purchase and sell.

First, from the Data Tab, select What-If > Scenario Manager. Then, click Add. Name the scenario based on what it does (for example, you might name it Small Order if this scenario includes a low number of units sold). Then, insert the Changing Cells (in other words, the cells that will change so you can evaluate the potential outcome). Repeat this until you’ve featured all your scenarios.

Finally, select Summary to create either a summary table or pivot table. Your result cell should feature the formula that includes the changing cells. For example, if you’re creating scenarios based on profit, your result cell should be profit while your changing cells should be items such as number of units sold, price per unit, cost per unit, and so on.

Macros can help you immediately automate workflows you frequently use in Excel. For example, you could create a shortcut that automatically applies a color code based on a row’s values. However, you’ll find that macros can enable you to automate complicated tasks, such as lead scoring, financial analysis, and more.

To illustrate macros, it’s helpful to start with a simple example. Say you always color code revenue driven by your sales team. To do this, first go to the Developer tab and select Record a Macro. There, pick a shortcut you’ll remember so you can repeat the task later. Alternatively, you can leave this blank and access your Macros from the Developer tab later.

Highlight the data you wish to color code. Then, go to Home > Conditional Formatting. You can select the type of color coding format you prefer. In our example, using a heat map might help us better visualize which sales associates performed best and worst. Once you’re done, click the black square in the bottom left corner to stop recording.

Using the same instructions provided above, let’s create a macro that automatically creates a table you can use to record quarterly revenue.

Go to the Developer tab and select Record a Macro. Start building out your table. Click the stop recording button.

Now, whenever you need to build out that table, you’ll no longer have to manually create it. Instead, simply press your shortcut and Excel will do it for you.

Similarly, Excel can automatically help you score leads so you don’t have to manually calculate them. Let’s say we have four factors we use to score leads—a user’s engagement on our Email, Blog, Webinar, and Product Page. In this scenario, let’s assign the following values:

High: 3
Medium: 2
Low: 1

High, in this case, represents an above average level of engagement.

Now, let’s assign weights to our four factors from earlier. Why? Because some engagement might be worth more than others. For example, we might assign Product Page a higher weight value because it signals a lead approaching the buying stage.

With this in mind, here are some weights we can apply to each factor (with the added weights summing to one).

Email: 0.2
Blog: 0.1
Webinar: 0.1
Product Page: 0.5

Finally, let’s start recording our macro. As always, go to the Developer tab and select Record a Macro. After setting up your shortcut, insert the following formula into your Lead Score column:

=Email_Engagement*3+Blog_Engagement*2+Webinar_Engagement*4+Product_Page_Engagement*5

Once complete, press the stop recording button. You can test out the shortcut by inserting more leads with their engagement levels.

If you work in finance, marketing, or any role that requires regularly reviewing data, you likely share this information with your coworkers or senior leadership. Thankfully, with Excel, you can create a macro that automatically makes your data more presentable.

Let’s say you work in finance. Every month, you review your revenue, expenses, and profit on a weekly basis. You keep your information stored in Excel but haven’t gotten around to making it presentable for your manager or other stakeholders.

To record a macro that you can use to beautify your current revenue overview—and every future one you create—use the following steps.

First, go to the Developer tab and select Record a Macro. Then, go through whatever steps you want to beautify the data. You can, for example, merge the top row, make it a different color, bold the font, and so on. You can even highlight data, go to Insert, and select a bar chart. Whatever you choose to do will be saved in the macro so you can use it later. Feel free to play around with the visuals you want to feature before choosing to record.