Masterclass

50 Hacks To Help You Become an Excel Master

Ready to level up your Excel game? Whether you’re a workbook rookie or an Excel wizard, you’ll find tons of new hacks and tips to help you become an even better master at Excel. Plus, we’ve included a GIF for each hack, so you can follow along as you read.

Menu

Get Started

50 Excel Hacks Guided Template

Want to try out these hacks as you work through them? Download a copy of this kit's guided template, which features practice sheets for each hack.

excel-vba

Excel Graph Templates

Looking for ways to make your data more beautiful? Download our easy-to-use graph generator template.

marketing plan excel template 600x300

Here’s what you’ll find:

  • 13 time-saving shortcuts to make Excel a breeze
  • 17 interface essentials to help you feel more comfortable with Excel’s ribbon
  • 20 game-changing functions and formulas, including pivot tables, macros, and more to automate Excel tasks

 

Marketing Analytics & Dashboard Software

Measure the performance of all your marketing campaigns in one place with built-in analytics, reports, and dashboards.

2023_MarketingHub_Analytics2-Aug-18-2024-11-26-44-7878-PM

Time-Saving Shortcuts

ExcelHacks_1CtrlF

ExcelHacks_2CtrlShiftL

ExcelHacks_3F4

ExcelHacks_4AltEqual

ExcelHacks_5CtrlShiftAmpersand

ExcelHacks_6CtrlShiftDollarSign

ExcelHacks_7CtrlShiftPercentage

ExcelHacks_8CtrlUpDownLeftRight

ExcelHacks_9CtrlShiftUpDownLeftRight

ExcelHacks_10CtrlE

ExcelHacks_11CtrlSpacebar

ExcelHacks_12CtrlT

ExcelHacks_13CtrlBracket

Dashboard & Reporting Software

Empower your team with analytics powerful enough for an analyst, but accessible enough for everyone.

2023_MarketingHub_SEO1

Interface Essentials

ExcelHacks_14Developer
Select File > More… > Options > Customize Ribbon. Then, beneath Main Tabs, scroll until you see Developer. Check the box and select OK.

ExcelHacks_15SelectAllCells
Click the gray arrow on the top left of the sheet

ExcelHacks_16ResizeColumns
Hover your mouse over the line between columns. Once you see two arrows facing opposite directions, double click the symbol

ExcelHacks_17AddColumns
Highlight the number of rows or columns desired. Right click and press Insert

ExcelHacks_18FreezePanes
Highlight the row directly below the panes you want to freeze (or, for columns, directly the column directly to the right). Select View > Freeze Panes

ExcelHacks_19DropDownMenu
Highlight the cells you want to add the drop-down menu in. Select Data > Validation. Look at the Allow options, then select List > Drop-Down List. Check the In-Cell Dropdown button and press OK

ExcelHacks_20Sort
Select the data in the column you want to sort. Click Data > Sort to arrange data in alphabetical or numerical order

ExcelHacks_21Filter
Select Data > Filter

ExcelHacks_22RemoveDuplicates

ExcelHacks_23Transpose
Copy the row or column you wish to transpose. Then, Right Click where you want to paste the data, select Paste Special > Transpose.

ExcelHacks_24SplitData
Highlight the column you want to split up. Then, select Data > Text to Columns. Select Delimited to break up columns based on characters such as commas, spaces or tabs or Fixed Width to specify the exact location you want the split to occur. Then, select Next > Finish.

ExcelHacks_25ConditionalFormatting
Select the group of cells you wish to format. Select Home > Conditional Formatting. There, insert your formatting rules and select OK when finished.

ExcelHacks_26Checkboxes
Select the cell you want to add the checkbox to. Then, select Developer > Insert > Checkbox.

ExcelHacks_27Hyperlink
Highlight the cell you wish to add the link to. Go to Insert > Link.

ExcelHacks_28NamingFormulas
Select Formulas > Define Name. Under Refers to, highlight the formula or cells you want to input.

ExcelHacks_29PasswordProtect
Select Review > Protect Sheet. Alternatively, you can select Protect Workbook to protect the entire file.

ExcelHacks_30RecommendedCharts
Highlight your data. Then, select Insert > Recommended Charts.

Advanced Marketing Reporting

Engage customers and drive growth with multi-touch revenue attribution and customer journey analytics reporting tools.

2023_MarketingHub_Analytics-3

Game-Changing Functions and Formulas

ExcelHacks_31WeightedAverage
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.

ExcelHacks_32IFTHEN
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])

ExcelHacks_33COUNTIF
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).

ExcelHacks_34Correlation
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.

ExcelHacks_35FormulaDollarSign
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.

ExcelHacks_36MergeCells
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.

ExcelHacks_37IF
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

ExcelHacks_39PivotTableDesign
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

ExcelHacks_40PivotTableExpansion
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

Sales Reporting &
Performance Management Software

Track your sales team’s progress and performance with easy-to-access and accurate data insights.

2023_SalesHub_Quotes3

ExcelHacks_41XLOOKUP
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

ExcelHacks_43HeatMaps
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).

ExcelHacks_44GeographicalMaps
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.

ExcelHacks_45GoalSeek
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.

ExcelHacks_46ScenarioManager
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.

ExcelHacks_47HeatMapMacro
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.

ExcelHacks_48MacroTable
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.

ExcelHacks_49LeadScoring
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.

ExcelHacks_50DataVisuals
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.

Free CRM Software & Tools
for Your Whole Team

Think CRM software is just about contact management? Think again. HubSpot CRM has free tools for everyone on your team, and it’s 100% free. Here’s how our free CRM solution makes your job easier.

Service Analytics

Uncover key insights with simple, out-of-the-box reports that help you deliver efficient and authentic customer service.