15 Excel Formulas, Keyboard Shortcuts & Tricks That'll Save You Lots of Time For most marketers, trying to organize and analyze spreadsheets in Microsoft Excel can feel like walking into a brick wall repeatedly if you'e unfamiliar with Excel formulas. You're manually replicating columns and scribbling down long-form math on a scrap of paper, all while thinking to yourself, "There has to be a better way to do this." Truth be told, there is — you just don't know it yet. Excel can be tricky that way. On the one hand, it's an exceptionally powerful tool for reporting and analyzing marketing data. It can even help you visualize data with charts and pivot tables. On the other, without the proper training, it's easy to feel like it's working against you. For starters, there are more than a dozen critical formulas Excel can automatically run for you so you're not combing through hundreds of cells with a calculator on your desk. What are excel formulas? Excel formulas help you identify relationships between values in the cells of your spreadsheet, perform mathematical calculations using those values, and return the resulting value in the cell of your choice. Formulas you can automatically perform include sum, subtraction, percentage, division, average, and even dates/times. We'll go over all of these, and many more, in this blog post. How to Insert Formulas in Excel You might wonder what the "Formulas" tab on the top navigation...

Calculating Correlation in Excel: Your How-To Guide Microsoft Excel lets you do more than simply create spreadsheets — you can also use the software to calculate key functions, such as the relationship between two variables. Known as the correlation coefficient, this metric is useful for measuring the impact of one operation on another to inform business operations. Not confident in your Excel skills? No problem. Here’s how to calculate — and understand — the correlation coefficient in Excel. A correlation coefficient of +1 indicates a “perfect positive correlation”, which means that as variable X increases, variable Y increases at the same rate. A correlation value of -1, meanwhile, is a “perfect negative correlation”, which means that as variable X increases, variable Y decreases at the same rate. Correlation analysis may also return results anywhere between -1 and +1, which indicates that variables change at similar but not identical rates. Correlation values can help businesses evaluate the impact of specific actions on other actions. For example, companies may find that as spending on social media marketing increases, so does customer engagement, indicating that more spending might make sense. Or they may find that specific advertising campaigns result in a correlated decrease of customer engagement, in turn suggesting the need for a reevaluation of current efforts. The discovery that variables do not correlate can also be valuable; while common sense might suggest that a new function or feature in your product would correlate with increased engagement, it might have no...

How to Use the Weighted Average Formula in Excel When you're calculating the average for a set of values, you're generally working with values that have the same weight and importance.   But what happens if some values weigh more than others? This is where the weighted average formula comes in. In this article, we will break down how to use this formula in Excel, plus provide some examples. How to Calculate Weighted Average in Excel To calculate the weighted average in Excel, you must use the SUMPRODUCT and SUM functions using the following formula: =SUMPRODUCT(X:X,X:X)/SUM(X:X) This formula works by multiplying each value by its weight and combining the values. Then, you divide the SUMPRODUCT but the sum of the weights for your weighted average. Still confused? Let's go over the steps in the next section. Using SUMPRODUCT to Calculate Weighted Average in Excel 1. Enter your data into a spreadsheet then add a column containing the weight for each data point. 2. Type =SUMPRODUCT to start the formula and enter the values. 3. Click enter to get your results. How to Find Weighted Moving Averages in Excel A weighted moving average is a technique used to keep the time period of the average the same as you add new data or give more weight to certain time periods. This can allow you to identify trends and patterns more easily. For instance, say you have the number of views your website got in...

How To Calculate CAGR in Excel Compound Annual Growth Rate, CAGR, is your rate of return for an investment over a specific period. Calculating CAGR by hand is a rather involved process, so below we’ll go over how you can quickly calculate CAGR in Excel. CAGR Excel Formula The formula for calculating CAGR in Excel is: =(End Value/Beginning Value) ^ (1/Number of Years) - 1 The equation uses three different values: End value, which is the amount of money you’ll have after the period has passed. Beginning value, which is the amount of money you began with. Number of years, which is the total number of years that have passed. Below we’ll go over an example of how to calculate CAGR for a five years time frame in Excel using the sample data set shown below: 1. Identify the numbers you’ll use in your equation. Using the sample data set above, The end value is 2143 (in cell B6). The beginning value is 1000 (in cell B2). The number of years is 5 (in cell A6). 2. Input your values into the formula.  Excel offers many shortcuts, so you can simply input the cell numbers that contain each of your values into the equation. Using the sample data set above, the equation would be =(B6/B2) ^ (1/A6) - 1 This is what it looks like in my Excel sheet: Note that the equation changes color to correspond with the...

How To Add a Sparkline in Excel Excel is a great tool for all of your marketing needs. You can create graphs to visualize your data, use formulas to calculate conversion rates, or even create social media calendars. You can also monitor trends in your marketing campaign data and, in this post, we’ll explain how to do so with the sparklines tool. Already know what you need? Jump there with our Table of Contents. How to Add a Sparkline in Excel Create a Column Sparkline in Excel How to Ungroup Sparklines in Excel How to Mark Data Points in Sparkline Charts How to Color Code Excel Sparkline What are sparklines in Excel? Sparklines are charts in individual cells that provide visual representations of trends in your sheet data. For example, if you track month-over-month progress, a sparkline can show you how each month compares to the other. There are three different ‌sparklines you can add to your Excel spreadsheets: line, column, and win-loss. The image below is an example of a line. For this walkthrough, we’ll use a sample data table (shown below) that tracks views, conversions, and leads generated from a marketing campaign‌. Let’s go over how to add your own. How to Add a Sparkline in Excel 1. Select the cells you want represented in your sparkline chart. In this example, I’ve selected all the cells between B2 and I2. 2. In the header toolbar, select Insert,...

How to Insert a Checkbox in Excel in 4 Easy Steps Adding a checkbox to your workbook may sound simple but it can expand the possibilities of what you can do in Excel. From checklists to graphs, there’s so much you can do. However, it starts with the checkbox. Learn everything you need to know about checkboxes below. How to Insert a Checkbox in Excel Add the developer tab to your Ribbon. Navigate to the Developer tab and locate the "Checkbox" option. Select the cell where you want to add the checkbox control then click the checkbox. Right-click the checkbox to edit the text and adjust sizing. To do this on Windows, click File > Options > Customize Ribbon. Then, select the Developer checkbox and click "save." On IOS, click Excel > Preferences > Ribbon & Toolbar > Main Tabs. Then, select the Developer checkbox and save. On Windows, there are a few extra steps to see the checkbox option. Under the Developer tab, click "Insert" and under "Form Controls," click the checkbox icon. Note: Currently, you cannot use checkboxes in the web version of Excel. If you upload a workbook with these controls, you’ll first have to disable them to start editing. How to Format a Checkbox in Excel Open up the format control. Modify the value and cell link, then click "OK." To...

How to Use Excel's INDEX/MATCH Formula With Multiple Criteria Excel is an incredibly powerful software – if you know how to leverage it. With so many functions and formula options, there’s something new to learn every day. The INDEX/MATCH formula can help you find data points quickly without having to manually search for them and risk making mistakes. Let’s dive into how that formula works and review some helpful use cases. Understanding INDEX and MATCH Functions Individually Before you can understand how to use the INDEX and match formula, it’s valuable to know how each function works on its own. That will offer some clarity on how both work together once combined. The INDEX function returns a value or the reference to a value within a table or range based on the rows and columns you specify. Think of this function as a GPS – it helps you find data within a document but first, you need to narrow down the search area using rows and columns. The MATCH function identifies a specific item in a range of cells then returns the relative position of that item in the range or the exact match. For instance, say the range A1:A4 contains the values 15, 28, 49, 90. You want to know how the number "49" is relative to all values within the range. You would write the formula =MATCH(49,A1:A4,0) and it would return the number 3 because it’s the third number in the range. The 0 in the formula...

The Quick & Easy Guide To Freezing Rows in Excel Without freezing rows or columns in your Excel spreadsheet, everything moves when you scroll through the page, as shown in the gif below. This can be frustrating if you can’t always see key data markers that explain what data is what, like column headers or row titles. As with many things on Excel, there are tricks that help you make your spreadsheets easier to read, like the freeze function. In this post, learn how to freeze rows and columns in Excel to ensure that, when you scroll around, you’ll always be able to view the key data points that matter most. How to Freeze a Top Row in Excel The image below is the sample data set I’ll use to run through the explanations in this piece. 1. To freeze the top row in an Excel spreadsheet, navigate to the header toolbar and select View, as shown in the image below. 2. When the View menu options appear, Click Freeze Top Row, outlined in red in the image below.   Once selected, everything in the top row of your Excel spreadsheet (row 1) will be frozen, and you can scroll up and down in your spreadsheet, but the top rows won’t move, as shown in the gif below. How to Freeze a Specific Row in Excel While excel has native functions for freezing the top row of a data set and the first column of a data set, there...

How to Create a Pivot Table in Excel: A Step-by-Step Tutorial The pivot table is one of Microsoft Excel's most powerful — and intimidating — functions. Powerful because it can help you summarize and make sense of large data sets. Intimidating because you're not exactly an Excel expert, and pivot tables have always had a reputation for being complicated. The good news: Learning how to create a pivot table in Excel is much easier than you might've been led to believe. But before we walk you through the process of creating one, let's take a step back and make sure you understand exactly what a pivot table is, and why you might need to use one. In other words, pivot tables extract meaning from that seemingly endless jumble of numbers on your screen. And more specifically, it lets you group your data in different ways so you can draw helpful conclusions more easily. The "pivot" part of a pivot table stems from the fact that you can rotate (or pivot) the data in the table to view it from a different perspective. To be clear, you're not adding to, subtracting from, or otherwise changing your data when you make a pivot. Instead, you're simply reorganizing the data so you can reveal useful information from it. What are pivot tables used for? If you're still feeling a bit confused about what pivot tables actually do, don't worry. This is one of those technologies that are much easier...

What Does it Mean to Use Concatenate in Excel Copy and paste shortcuts are handy until you have hundreds of data points to manually combine and reformat. Fortunately, you can use the CONCATENATE Excel function to save time (and curb carpal tunnel) when joining information from cells, rows, or columns. Meaning "to join together" or "to combine," concatenate is a way to unite the contents of two or more cells into one cell. The function allows you to combine data from columns, cells, ranges, and rows into whatever format you desire, making it easy to quickly join names and addresses or properly display dates and times. There are various ways to set up the CONCATENATE formula in Excel, so we'll walk through the steps required to use this function and achieve your worksheet goals. How to Concatenate in Excel While there are several ways to combine text in Excel worksheets, we'll be focusing on the CONCATENATE function. It combines the contents of two or more cells into one cell without physically changing the shape of the cell and is often used to join pieces of text (called text strings or strings) from individual cells into one cell. The resulting text string is the combination of all strings in your CONCATENATE formula. Here's a look at how to concatenate in Excel: Let's say you have a list of customers whose first and last names are separate. You need everyone's full names to build a retargeting campaign, so you want to join...