0
0
Sometimes, Excel seems too good to be true. All I have to do is enter a formula, and pretty much anything I’d ever need to do manually can be done automatically.
Need to merge two sheets with similar data? Excel can do it.
Need to do simple math? Excel can do it.
Need to combine information in multiple cells? Excel can do it.
In this post, I’ll go over the best tips, tricks, and shortcuts you can use right now to take your Excel game to the next level. No advanced Excel knowledge required.
What is Excel?
Microsoft Excel is powerful data visualization and analysis software, which uses spreadsheets to store, organize, and track data sets with formulas and functions. Excel is used by marketers, accountants, data analysts, and other professionals. It’s part of the Microsoft Office suite of products. Alternatives include Google Sheets and Numbers.
Find more Excel alternatives here.
Excel is primarily used for creating financial documents because of its strong computational powers. You’ll often find the software in accounting offices and teams because it allows accountants to automatically see sums, averages, and totals. With Excel, they can easily make sense of their business’ data.
While Excel is primarily known as an accounting tool, professionals in any field can use its features and formulas — especially marketers — because it can be used for tracking any type of data. It removes the need to spend hours and hours counting cells or copying and pasting performance numbers. Excel typically has a shortcut or quick fix that speeds up the process.
You can also download Excel templates below for all of your marketing needs.
After you download the templates, it’s time to start using the software. Let’s cover the basics first.
Excel Basics
If you’re just starting out with Excel, there are a few basic commands that we suggest you become familiar with. These are things like:
Creating a new spreadsheet from scratch.
Executing basic computations like adding, subtracting, multiplying, and dividing.
Writing and formatting column text and titles.
Using Excel’s auto-fill features.
Adding or deleting single columns, rows, and spreadsheets. (Below, we’ll get into how to add things like multiple columns and rows.)
Keeping column and row titles visible as you scroll past them in a spreadsheet, so that you know what data you’re filling as you move further down the document.
Sorting your data in alphabetical order.
Let’s explore a few of these more in-depth.
For instance, why does auto-fill matter?
If you have any basic Excel knowledge, it’s likely you already know this quick trick. But to cover our bases, allow me to show you the glory of autofill. This lets you quickly fill adjacent cells with several types of data, including values, series, and formulas.
There are multiple ways to deploy this feature, but the fill handle is among the easiest. Select the cells you want to be the source, locate the fill handle in the lower-right corner of the cell, and either drag the fill handle to cover cells you want to fill or just double click:
Similarly, sorting is an important feature you’ll want to know when organizing your data in Excel.
Sometimes you may have a list of data that has no organization whatsoever. Maybe you exported a list of your marketing contacts or blog posts. Whatever the case may be, Excel’s sort feature will help you alphabetize any list.
Click on the data in the column you want to sort. Then click on the “Data” tab in your toolbar and look for the “Sort” option on the left. If the “A” is on top of the “Z,” you can just click on that button once. If the “Z” is on top of the “A,” click on the button twice. When the “A” is on top of the “Z,” that means your list will be sorted in alphabetical order. However, when the “Z” is on top of the “A,” that means your list will be sorted in reverse alphabetical order.
Let’s explore more of the basics of Excel (along with advanced features) next.
How to Use Excel
To use Excel, you only need to input the data into the rows and columns. And then you’ll use formulas and functions to turn that data into insights.
We‘re going to go over the best formulas and functions you need to know. But first, let’s take a look at the types of documents you can create using the software. That way, you have an overarching understanding of how you can use Excel in your day-to-day.
Documents You Can Create in Excel
Not sure how you can actually use Excel in your team? Here is a list of documents you can create:
Income Statements: You can use an Excel spreadsheet to track a company’s sales activity and financial health.
Balance Sheets: Balance sheets are among the most common types of documents you can create with Excel. It allows you to get a holistic view of a company’s financial standing.
Calendar: You can easily create a spreadsheet monthly calendar to track events or other date-sensitive information.
Here are some documents you can create specifically for marketers.
Marketing Budgets: Excel is a strong budget-keeping tool. You can create and track marketing budgets, as well as spend, using Excel. If you don’t want to create a document from scratch, download our marketing budget templates for free.
Marketing Reports: If you don’t use a marketing tool such as Marketing Hub, you might find yourself in need of a dashboard with all of your reports. Excel is an excellent tool to create marketing reports. Download free Excel marketing reporting templates here.
Editorial Calendars: You can create editorial calendars in Excel. The tab format makes it extremely easy to track your content creation efforts for custom time ranges. Download a free editorial content calendar template here.
Traffic and Leads Calculator: Because of its strong computational powers, Excel is an excellent tool to create all sorts of calculators — including one for tracking leads and traffic. Click here to download a free premade lead goal calculator.
This is only a small sampling of the types of marketing and business documents you can create in Excel. We’ve created an extensive list of Excel templates you can use right now for marketing, invoicing, project management, budgeting, and more.
In the spirit of working more efficiently and avoiding tedious, manual work, here are a few Excel formulas and functions you’ll need to know.
Excel Formulas
It’s easy to get overwhelmed by the wide range of Excel formulas that you can use to make sense out of your data. If you’re just getting started using Excel, you can rely on the following formulas to carry out some complex functions — without adding to the complexity of your learning path.
Equal sign: Before creating any formula, you’ll need to write an equal sign (=) in the cell where you want the result to appear.
Addition: To add the values of two or more cells, use the + sign. Example: =C5+D3.
Subtraction: To subtract the values of two or more cells, use the – sign. Example: =C5-D3.
Multiplication: To multiply the values of two or more cells, use the * sign. Example: =C5*D3.
Division: To divide the values of two or more cells, use the / sign. Example: =C5/D3.
Putting all of these together, you can create a formula that adds, subtracts, multiplies, and divides all in one cell. Example: =(C5-D3)/((A5+B6)*3).
For more complex formulas, you’ll need to use parentheses around the expressions to avoid accidentally using the PEMDAS order of operations. Keep in mind that you can use plain numbers in your formulas.
Excel Functions
Excel functions automate some of the tasks you would use in a typical formula. For instance, instead of using the + sign to add up a range of cells, you’d use the SUM function. Let’s look at a few more functions that will help automate calculations and tasks.
SUM: The SUM function automatically adds up a range of cells or numbers. To complete a sum, you would input the starting cell and the final cell with a colon in between. Here’s what that looks like: SUM(Cell1:Cell2). Example: =SUM(C5:C30).
AVERAGE: The AVERAGE function averages out the values of a range of cells. The syntax is the same as the SUM function: AVERAGE(Cell1:Cell2). Example: =AVERAGE(C5:C30).
IF: The IF function allows you to return values based on a logical test. The syntax is as follows: IF(logical_test, value_if_true, [value_if_false]). Example: =IF(A2>B2,“Over Budget”,“OK”).
VLOOKUP: The VLOOKUP function helps you search for anything on your sheet’s rows. The syntax is: VLOOKUP(lookup value, table array, column number, Approximate match (TRUE) or Exact match (FALSE)). Example: =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).
INDEX: The INDEX function returns a value from within a range. The syntax is as follows: INDEX(array, row_num, [column_num]).
MATCH: The MATCH function looks for a certain item in a range of cells and returns the position of that item. It can be used in tandem with the INDEX function. The syntax is: MATCH(lookup_value, lookup_array, [match_type]).
COUNTIF: The COUNTIF function returns the number of cells that meet a certain criteria or have a certain value. The syntax is: COUNTIF(range, criteria). Example: =COUNTIF(A2:A5,“London”).
Okay, ready to get into the nitty-gritty? Let‘s get to it. (And to all the Harry Potter fans out there … you’re welcome in advance.)
Note: Some of the GIFs and visuals are from a previous version of Excel. When applicable, the copy has been updated to provide instruction for users of both newer and older Excel versions.
1. Use Pivot tables to recognize and make sense of data.
Pivot tables are used to reorganize data in a spreadsheet. They won‘t change the data that you have, but they can sum up values and compare different information in your spreadsheet, depending on what you’d like them to do.
Let‘s take a look at an example. Let’s say I want to take a look at how many people are in each house at Hogwarts. You may be thinking that I don’t have too much data, but for longer data sets, this will come in handy.
To create the Pivot Table, I go to Data > Pivot Table. If you’re using the most recent version of Excel, you’d go to Insert > Pivot Table. Excel will automatically populate your Pivot Table, but you can always change around the order of the data. Then, you have four options to choose from.
Report Filter: This allows you to only look at certain rows in your dataset. For example, if I wanted to create a filter by house, I could choose to only include students in Gryffindor instead of all students.
Column Labels: These would be your headers in the dataset.
Row Labels: These could be your rows in the dataset. Both Row and Column labels can contain data from your columns (e.g. First Name can be dragged to either the Row or Column label — it just depends on how you want to see the data.)
Value: This section allows you to look at your data differently. Instead of just pulling in any numeric value, you can sum, count, average, max, min, count numbers, or do a few other manipulations with your data. In fact, by default, when you drag a field to Value, it always does a count.
Since I want to count the number of students in each house, I’ll go to the Pivot table builder and drag the House column to both the Row Labels and the Values. This will sum up the number of students associated with each house.
2. Add more than one row or column.
As you play around with your data, you might find you‘re constantly needing to add more rows and columns. Sometimes, you may even need to add hundreds of rows. Doing this one-by-one would be super tedious. Luckily, there’s always an easier way.
To add multiple rows or columns in a spreadsheet, highlight the same number of preexisting rows or columns that you want to add. Then, right-click and select “Insert.”
In the example below, I want to add an additional three rows. By highlighting three rows and then clicking insert, I’m able to add an additional three blank rows into my spreadsheet quickly and easily.
3. Use filters to simplify your data.
When you‘re looking at very large data sets, you don’t usually need to be looking at every single row at the same time. Sometimes, you only want to look at data that fit into certain criteria.
That’s where filters come in.
Filters allow you to pare down your data to only look at certain rows at one time. In Excel, a filter can be added to each column in your data — and from there, you can then choose which cells you want to view at once.
Let‘s take a look at the example below. Add a filter by clicking the Data tab and selecting “Filter.” Clicking the arrow next to the column headers and you’ll be able to choose whether you want your data to be organized in ascending or descending order, as well as which specific rows you want to show.
In my Harry Potter example, let’s say I only want to see the students in Gryffindor. By selecting the Gryffindor filter, the other rows disappear.
Pro Tip: Copy and paste the values in the spreadsheet when a Filter is on to do additional analysis in another spreadsheet.
4. Remove duplicate data points or sets.
Larger data sets tend to have duplicate content. You may have a list of multiple contacts in a company and only want to see the number of companies you have. In situations like this, removing the duplicates comes in quite handy.
To remove your duplicates, highlight the row or column that you want to remove duplicates of. Then, go to the Data tab and select “Remove Duplicates” (which is under the Tools subheader in the older version of Excel). A pop-up will appear to confirm which data you want to work with. Select “Remove Duplicates,” and you’re good to go.
You can also use this feature to remove an entire row based on a duplicate column value. So if you have three rows with Harry Potter’s information and you only need to see one, then you can select the whole dataset and then remove duplicates based on email. Your resulting list will have only unique names without any duplicates.
5. Transpose rows into columns.
When you have rows of data in your spreadsheet, you might decide you actually want to transform the items in one of those rows into columns (or vice versa). It would take a lot of time to copy and paste each individual header — but what the transpose feature allows you to do is simply move your row data into columns, or the other way around.
Start by highlighting the column that you want to transpose into rows. Right-click it, and then select “Copy.” Next, select the cells on your spreadsheet where you want your first row or column to begin. Right-click on the cell, and then select “Paste Special.” A module will appear — at the bottom, you’ll see an option to transpose. Check that box and select OK. Your column will now be transferred to a row or vice-versa.
On newer versions of Excel, a drop-down will appear instead of a pop-up.
6. Split up text information between columns.
What if you want to split out information that‘s in one cell into two different cells? For example, maybe you want to pull out someone’s company name through their email address. Or perhaps you want to separate someone’s full name into a first and last name for your email marketing templates.
Thanks to Excel, both are possible. First, highlight the column that you want to split up. Next, go to the Data tab and select “Text to Columns.” A module will appear with additional information.
First, you need to select either “Delimited” or “Fixed Width.”
“Delimited” means you want to break up the column based on characters such as commas, spaces, or tabs.
“Fixed Width” means you want to select the exact location on all the columns that you want the split to occur.
In the example case below, let’s select “Delimited” so we can separate the full name into first name and last name.
Then, it‘s time to choose the Delimiters. This could be a tab, semi-colon, comma, space, or something else. (“Something else” could be the “@” sign used in an email address, for example.) In our example, let’s choose the space. Excel will then show you a preview of what your new columns will look like.
When you‘re happy with the preview, press “Next.” This page will allow you to select Advanced Formats if you choose to. When you’re done, click “Finish.”
7. Use formulas for simple calculations.
In addition to doing pretty complex calculations, Excel can help you do simple arithmetic like adding, subtracting, multiplying, or dividing any of your data.
To add, use the + sign.
To subtract, use the – sign.
To multiply, use the * sign.
To divide, use the / sign.
You can also use parentheses to ensure certain calculations are done first. In the example below (10+10*10), the second and third 10 were multiplied together before adding the additional 10. However, if we made it (10+10)*10, the first and second 10 would be added together first.
8. Get the average of numbers in your cells.
If you want the average of a set of numbers, you can use the formula =AVERAGE(Cell1:Cell2). If you want to sum up a column of numbers, you can use the formula =SUM(Cell1:Cell2).
9. Use conditional formatting to make cells automatically change color based on data.
Conditional formatting allows you to change a cell’s color based on the information within the cell. For example, if you want to flag certain numbers that are above average or in the top 10% of the data in your spreadsheet, you can do that. If you want to color code commonalities between different rows in Excel, you can do that. This will help you quickly see information that is important to you.
To get started, highlight the group of cells you want to use conditional formatting on. Then, choose “Conditional Formatting” from the Home menu and select your logic from the dropdown. (You can also create your own rule if you want something different.) A window will pop up that prompts you to provide more information about your formatting rule. Select “OK” when you’re done, and you should see your results automatically appear.
10. Use the IF Excel formula to automate certain Excel functions.
Sometimes, we don’t want to count the number of times a value appears. Instead, we want to input different information into a cell if there is a corresponding cell with that information.
For example, in the situation below, I want to award ten points to everyone who belongs in the Gryffindor house. Instead of manually typing in 10‘s next to each Gryffindor student’s name, I can use the IF Excel formula to say that if the student is in Gryffindor, then they should get ten points.
The formula is: IF(logical_test, value_if_true, [value_if_false])
Example Shown Below: =IF(D2=“Gryffindor”,“10”,“0”)
In general terms, the formula would be IF(Logical Test, value of true, value of false). Let’s dig into each of these variables.
Logical_Test: The logical test is the “IF” part of the statement. In this case, the logic is D2=“Gryffindor” because we want to make sure that the cell corresponding with the student says “Gryffindor.” Make sure to put Gryffindor in quotation marks here.
Value_if_True: This is what we want the cell to show if the value is true. In this case, we want the cell to show “10” to indicate that the student was awarded the 10 points. Only use quotation marks if you want the result to be text instead of a number.
Value_if_False: This is what we want the cell to show if the value is false. In this case, for any student not in Gryffindor, we want the cell to show “0”. Only use quotation marks if you want the result to be text instead of a number.
Note: In the example above, I awarded 10 points to everyone in Gryffindor. If I later wanted to sum the total number of points, I wouldn‘t be able to because the 10’s are in quotes, thus making them text and not a number that Excel can sum.
The real power of the IF function comes when you string multiple IF statements together, or nest them. This allows you to set multiple conditions, get more specific results, and ultimately organize your data into more manageable chunks.
Ranges are one way to segment your data for better analysis. For example, you can categorize data into values that are less than 10, 11 to 50, or 51 to 100. Here’s how that looks in practice:
=IF(B3<11,“10 or less”,IF(B3<51,“11 to 50”,IF(B3<100,“51 to 100”)))
It can take some trial-and-error, but once you have the hang of it, IF formulas will become your new Excel best friend.
11. Use dollar signs to keep one cell’s formula the same regardless of where it moves.
Have you ever seen a dollar sign in an Excel formula? When used in a formula, it isn’t representing an American dollar; instead, it makes sure that the exact column and row are held the same even if you copy the same formula in adjacent rows.
You see, a cell reference — when you refer to cell A5 from cell C5, for example — is relative by default. In that case, you‘re actually referring to a cell that’s five columns to the left (C minus A) and in the same row (5). This is called a relative formula. When you copy a relative formula from one cell to another, it‘ll adjust the values in the formula based on where it’s moved. But sometimes, we want those values to stay the same no matter whether they’re moved around or not — and we can do that by turning the formula into an absolute formula.
To change the relative formula (=A5+C5) into an absolute formula, we’d precede the row and column values by dollar signs, like this: (=$A$5+$C$5). (Learn more on Microsoft Office’s support page here.)
12. Use the VLOOKUP function to pull data from one area of a sheet to another.
Have you ever had two sets of data on two different spreadsheets that you want to combine into a single spreadsheet?
For example, you might have a list of people‘s names next to their email addresses in one spreadsheet, and a list of those same people’s email addresses next to their company names in the other — but you want the names, email addresses, and company names of those people to appear in one place.
I have to combine data sets like this a lot — and when I do, the VLOOKUP is my go-to formula.
Before you use the formula, though, be absolutely sure that you have at least one column that appears identically in both places. Scour your data sets to make sure the column of data you’re using to combine your information is exactly the same, including no extra spaces.
The formula: =VLOOKUP(lookup value, table array, column number, Approximate match (TRUE) or Exact match (FALSE))
The formula with variables from our example below: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)
In this formula, there are several variables. The following is true when you want to combine information in Sheet 1 and Sheet 2 onto Sheet 1.
Lookup Value: This is the identical value you have in both spreadsheets. Choose the first value in your first spreadsheet. In the example that follows, this means the first email address on the list, or cell 2 (C2).
Table Array: The table array is the range of columns on Sheet 2 you‘re going to pull your data from, including the column of data identical to your lookup value (in our example, email addresses) in Sheet 1 as well as the column of data you’re trying to copy to Sheet 1. In our example, this is “Sheet2!A:B.” “A” means Column A in Sheet 2, which is the column in Sheet 2 where the data identical to our lookup value (email) in Sheet 1 is listed. The “B” means Column B, which contains the information that’s only available in Sheet 2 that you want to translate to Sheet 1.
Column Number: This tells Excel which column the new data you want to copy to Sheet 1 is located in. In our example, this would be the column that “House” is located in. “House” is the second column in our range of columns (table array), so our column number is 2. [Note: Your range can be more than two columns. For example, if there are three columns on Sheet 2 — Email, Age, and House — and you still want to bring House onto Sheet 1, you can still use a VLOOKUP. You just need to change the “2” to a “3” so it pulls back the value in the third column: =VLOOKUP(C2:Sheet2!A:C,3,false).]
Approximate Match (TRUE) or Exact Match (FALSE): Use FALSE to ensure you pull in only exact value matches. If you use TRUE, the function will pull in approximate matches.
In the example below, Sheet 1 and Sheet 2 contain lists describing different information about the same people, and the common thread between the two is their email addresses. Let’s say we want to combine both datasets so that all the house information from Sheet 2 translates over to Sheet 1.
So when we type in the formula =VLOOKUP(C2,Sheet2!A:B,2,FALSE), we bring all the house data into Sheet 1.
Keep in mind that VLOOKUP will only pull back values from the second sheet that are to the right of the column containing your identical data. This can lead to some limitations, which is why some people prefer to use the INDEX and MATCH functions instead.
13. Use INDEX and MATCH formulas to pull data from horizontal columns.
Like VLOOKUP, the INDEX and MATCH functions pull in data from another dataset into one central location. Here are the main differences:
VLOOKUP is a much simpler formula. If you’re working with large data sets that would require thousands of lookups, using the INDEX and MATCH function will significantly decrease load time in Excel.
The INDEX and MATCH formulas work right-to-left, whereas VLOOKUP formulas only work as a left-to-right lookup. In other words, if you need to do a lookup that has a lookup column to the right of the results column, then you’d have to rearrange those columns in order to do a VLOOKUP. This can be tedious with large datasets and/or lead to errors.
So if I want to combine information in Sheet 1 and Sheet 2 onto Sheet 1, but the column values in Sheets 1 and 2 aren‘t the same, then to do a VLOOKUP, I would need to switch around my columns. In this case, I’d choose to do an INDEX and MATCH instead.
Let‘s look at an example. Let’s say Sheet 1 contains a list of people‘s names and their Hogwarts email addresses, and Sheet 2 contains a list of people’s email addresses and the Patronus that each student has. (For the non-Harry Potter fans out there, every witch or wizard has an animal guardian called a “Patronus” associated with him or her.) The information that lives in both sheets is the column containing email addresses, but this email address column is in different column numbers on each sheet. I‘d use the INDEX and MATCH formulas instead of VLOOKUP so I wouldn’t have to switch any columns around.
So what‘s the formula, then? The formula is actually the MATCH formula nested inside the INDEX formula. You’ll see I differentiated the MATCH formula using a different color here.
The formula: =INDEX(table array, MATCH formula)
This becomes: =INDEX(table array, MATCH (lookup_value, lookup_array))
The formula with variables from our example below: =INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0)))
Here are the variables:
Table Array: The range of columns on Sheet 2 containing the new data you want to bring over to Sheet 1. In our example, “A” means Column A, which contains the “Patronus” information for each person.
Lookup Value: This is the column in Sheet 1 that contains identical values in both spreadsheets. In the example that follows, this means the “email” column on Sheet 1, which is Column C. So: Sheet1!C:C.
Lookup Array: This is the column in Sheet 2 that contains identical values in both spreadsheets. In the example that follows, this refers to the “email” column on Sheet 2, which happens to also be Column C. So: Sheet2!C:C.
Once you have your variables straight, type in the INDEX and MATCH formulas in the top-most cell of the blank Patronus column on Sheet 1, where you want the combined information to live.
14. Use the COUNTIF function to make Excel count words or numbers in any range of cells.
Instead of manually counting how often a certain value or number appears, let Excel do the work for you. With the COUNTIF function, Excel can count the number of times a word or number appears in any range of cells.
For example, let’s say I want to count the number of times the word “Gryffindor” appears in my data set.
The formula: =COUNTIF(range, criteria)
The formula with variables from our example below: =COUNTIF(D:D,“Gryffindor”)
In this formula, there are several variables:
Range: The range that we want the formula to cover. In this case, since we’re only focusing on one column, we use “D:D” to indicate that the first and last column are both D. If I were looking at columns C and D, I would use “C:D.”
Criteria: Whatever number or piece of text you want Excel to count. Only use quotation marks if you want the result to be text instead of a number. In our example, the criteria is “Gryffindor.”
Simply typing in the COUNTIF formula in any cell and pressing “Enter” will show me how many times the word “Gryffindor” appears in the dataset.
15. Combine cells using &.
Databases tend to split out data to make it as exact as possible. For example, instead of having a column that shows a person‘s full name, a database might have the data as a first name and then a last name in separate columns. Or, it may have a person’s location separated by city, state, and zip code. In Excel, you can combine cells with different data into one cell by using the “&” sign in your function.
The formula with variables from our example below: =A2&“ ”&B2
Let‘s go through the formula together using an example. Pretend we want to combine first names and last names into full names in a single column. To do this, we’d first put our cursor in the blank cell where we want the full name to appear. Next, we’d highlight one cell that contains a first name, type in an “&” sign, and then highlight a cell with the corresponding last name.
But you‘re not finished — if all you type in is =A2&B2, then there will not be a space between the person’s first name and last name. To add that necessary space, use the function =A2&“ ”&B2. The quotation marks around the space tell Excel to put a space in between the first and last name.
To make this true for multiple rows, simply drag the corner of that first cell downward as shown in the example.
16. Add checkboxes.
If you‘re using an Excel sheet to track customer data and want to oversee something that isn’t quantifiable, you could insert checkboxes into a column.
For example, if you‘re using an Excel sheet to manage your sales prospects and want to track whether you called them in the last quarter, you could have a “Called this quarter?” column and check off the cells in it when you’ve called the respective client.
Here’s how to do it.
Highlight a cell you’d like to add checkboxes to in your spreadsheet. Then, click DEVELOPER. Then, under FORM CONTROLS, click the checkbox or the selection circle highlighted in the image below.
Once the box appears in the cell, copy it, highlight the cells you also want it to appear in, and then paste it.
17. Hyperlink a cell to a website.
If you‘re using your sheet to track social media or website metrics, it can be helpful to have a reference column with the links each row is tracking. If you add a URL directly into Excel, it should automatically be clickable. But, if you have to hyperlink words, such as a page title or the headline of a post you’re tracking, here’s how.
Highlight the words you want to hyperlink, then press Shift K. From there a box will pop up allowing you to place the hyperlink URL. Copy and paste the URL into this box and hit or click Enter.
If the key shortcut isn’t working for any reason, you can also do this manually by highlighting the cell and clicking Insert > Hyperlink.
18. Add drop-down menus.
Sometimes, you‘ll be using your spreadsheet to track processes or other qualitative things. Rather than writing words into your sheet repetitively, such as “Yes”, “No”, “Customer Stage”, “Sales Lead”, or “Prospect”, you can use dropdown menus to quickly mark descriptive things about your contacts or whatever you’re tracking.
Here’s how to add drop-downs to your cells.
Highlight the cells you want the drop-downs to be in, then click the Data menu in the top navigation and press Validation.
From there, you’ll see a Data Validation Settings box open. Look at the Allow options, then click Lists and select Drop-down List. Check the In-Cell dropdown button, then press OK.
19. Use the format painter.
As you’ve probably noticed, Excel has a lot of features to make crunching numbers and analyzing your data quick and easy. But if you ever spent some time formatting a sheet to your liking, you know it can get a bit tedious.
Don’t waste time repeating the same formatting commands over and over again. Use the format painter to easily copy the formatting from one area of the worksheet to another. To do so, choose the cell you’d like to replicate, then select the format painter option (paintbrush icon) from the top toolbar.
20. Create tables with data.
Converting your data into a table not only makes it visually appealing but also provides improved data management and analysis capabilities.
To get started, you’ll need to select the range of cells that you want to convert into a table. Then, go to the Home tab in the Excel ribbon. In the Styles group, click on the Format as Table button — it looks like a grid of cells. Then, choose a table style from the available options, or customize a table if desired.
In the Create Table dialog box, make sure the range you selected is correct. If Excel did not automatically detect the range correctly, you can adjust it manually. If your table has headers (column names), ensure that the “My table has headers” option is checked. This allows Excel to treat the first row as the header row.
Once everything is ready, click the OK button, and Excel will convert your selected data into a table.
After your data is converted into a table, you’ll notice some additional features and functionalities become available:
The table is automatically assigned a name, such as “Table1” or “Table2,” which you can modify if needed.
Filter drop-down arrows appear in the header row, allowing you to filter data within the table easily.
The table is formatted with alternating row colors, making it visually appealing.
Total rows are automatically added at the bottom of each column, allowing you to perform calculations like sum, average, etc., for the data in that column.
21. Use tables to conduct a what-if analysis.
In addition to making your data more organized, tables can also help you conduct what-if analyses. This allows you to test various combinations of input values and observe the resulting outcomes.
A what-if analysis can be beneficial when it comes to decision making, planning, forecasting, financial modeling, sensitivity analysis, resource planning, and more.
To get started, you’ll need to set up your worksheet with the necessary formulas and variables you want to analyze. Then, determine the input values that you want to vary. Typically, you will choose one or two input variables.
Select the cell where you want to display the results of your what-if analysis. Then, go to the Data tab in the Excel ribbon and click on the What-If Analysis button. From the dropdown menu, select Data Table.
In the Table Input dialog box, enter the input values that you want to test for each variable. If you have one variable, enter the different input values in a column or row. If you have two variables, enter the combinations in a table format.
Select the cells in the table area that correspond to the formula cell you want to analyze. This is the cell that will display the results for each combination of input values.
Click OK to generate the data table. Excel will calculate the formula for each combination of input values and display the results in the selected cells. The data table acts as a grid, showing the various scenarios and their corresponding outcomes.
Once your table is created, you can use it to identify trends, patterns, or specific values of interest. Play around with the input values and see how it may affect the final results.
22. Make formulas easier to comprehend with named ranges.
Instead of referring to a range of cells by its coordinates (e.g., A1:B10), you can assign a name to it. This makes formulas more readable and easier to manage.
To get started, select the cell or range of cells that you want to name. Go to the Formulas tab in the Excel ribbon and click on the Define Name button in the Defined Names group. Alternatively, you can use the keyboard shortcut Alt + M + N + D.
In the New Name dialog box, enter a name for the selected cell or range in the Name field. Make sure the name is descriptive and easy to remember. By default, Excel assigns the selected cell or range’s reference to the Refers to field in the dialog box. If needed, you can modify the reference to include additional cells or adjust the range.
Click the OK button to save the named range. Once you’ve named a range, you can use it in your formulas by simply typing the name instead of the cell reference. For example, if you named cell A1 as “Revenue,” you could use =Revenue instead of =A1 in your formulas.
Using named ranges offers several benefits:
Improved formula readability: Named ranges make formulas easier to understand and navigate, especially in complex calculations or large datasets.
Flexibility for range adjustments: If your dataset changes, you can easily modify the range assigned to a named range without updating each formula that references it.
Enhanced collaboration: Named ranges make it easier to collaborate with others, as they can understand the purpose of a named range and use it in their own calculations.
Simplified data analysis: When using named ranges, you can create more intuitive data analysis by referring to named ranges in functions like SUM, AVERAGE, COUNTIF, etc.
To manage named ranges, you can go to the Formulas tab, click on the Name Manager button in the Defined Names group. The Name Manager offers functionalities to modify, delete, or review existing named ranges.
23. Group data to improve organization.
Grouping data in Excel provides a way to organize, analyze, and present information more effectively, making it easier to identify patterns, trends, and insights within your data. For instance, if you have a list of leads generated, you can group the data by month to create a monthly performance report.
Grouping data especially makes it easier to navigate and work with large data sets. It helps in organization and reduces clutter by collapsing the groups that are not immediately needed.
To group data in Excel, select the range of cells or columns that you want to group. Make sure the data is sorted properly, if needed.
On the Data tab in the Excel ribbon, click on the Group button. It is usually found in the Outline or Data Tools group.
You can specify the grouping levels by choosing options like Rows or Columns. For example, if you want to group data by month, you can select Months. You can also set additional options such as Summary rows below detail or Collapse the outline to the summary levels. These options affect how the grouped data is displayed.
Once you have the options you want selected, click on the OK button, and Excel will group the selected data based on your settings.
After your data is grouped, you will see a plus (+) or minus (-) button next to the grouped rows or columns. Clicking on the plus button expands the group to show the individual records, and clicking on the minus button collapses the group to hide the details.
24. Use Find & Select to streamline formatting.
Why format and clean up your spreadsheet manually when you can do it in just a few clicks? Using the Find & Select tool can help you maintain accuracy and consistency in your documents.
To get started, open the Excel worksheet that contains the data you want to search. Press the Ctrl + F keys on your keyboard or go to the Home tab and click on the Find & Select drop-down menu. Then, select Find from the menu. The Find and Replace dialog box will open.
In the Find field, enter the specific data you want to find. Optionally, you can narrow down your search to specific cells, rows, columns, or formulas by choosing the appropriate options in the dialog box.
Click on the Find next button to search for the first occurrence of the data. Excel will highlight the cell containing the data.
To replace the found data with new information, click on the Replace button in the dialog box. This will replace the highlighted occurrence with the data you enter in the Replace field.
To replace all occurrences of the data at once, click on the Replace All button. Once you have finished finding and replacing, you can close the dialog box.
Note: Be cautious when using the Replace All feature, as it replaces all occurrences without confirmation. It is always a good practice to review each replacement carefully before using the Replace All option.
25. Protect your work.
Protecting your work in Excel is essential for data security, maintaining data integrity, preserving intellectual property, and complying with legal or regulatory requirements. It allows you to have control over who can access and modify your work, minimizing risks and maintaining the quality and confidentiality of your data.
Here are a couple ways you can protect your work:
Protect a Worksheet
Open your Excel worksheet and navigate to the Review tab.
Click on the Manage Protection button in the Protection group.
A Manage Protection dialog box will appear. There, you can select whether or not you want to protect the sheet. Set a password if desired and choose the options you want to apply, such as preventing users from making changes to cells, formatting, inserting/deleting columns or rows, etc.
Protecting a Workbook
Open your Excel workbook and navigate to the File tab.
Click on Info and select Protect Workbook from the options.
Choose Encrypt with Password and enter a password if desired.
Click OK to protect the workbook.
Taking these extra steps ensures your work is protected. Just make sure to keep your passwords safe and secure.
26. Create custom number formats.
To display data in unique ways, use custom number formats. Doing this can help with data presentation, data clarity, consistency, localization, and masking sensitive data.
To get started, select the cell or range of cells that you want to format. Right-click on the selected cells and choose Number Format from the context menu. Then, find the Category list and select Custom.
In the Type field, you can enter a custom number format code to define your desired format. Here are some examples of custom number formats:
To display numbers with a specific number of decimal places, use the 0 or # symbol to represent a digit, and a zero or hashtag without a decimal point to represent optional digits. For example, 0.00 will display two decimal places, 0.### will display up to three decimal places, and ### will display no decimal places.
To display a specific text or character alongside numbers, use the @ symbol. For example, $0 will display a dollar sign before the number.
To display percentages, use the % symbol. For example, 0% will display the number as a percentage.
To create custom date or time formats, use codes such as dd for day, mm for month, yy for two-digit year, hh for hours, mm for minutes, and ss for seconds. For example, dd/mm/yyyy will display the date in the format of day/month/year.
As you enter your custom number format in the Type field, you will see a Sample section that shows a preview of how the format will be applied. Click OK to apply the custom number format to the selected cells.
27. Customize the Excel ribbon.
Although the Excel ribbon already contains various tools that are used to execute common functions and commands, you can customize it to fit your specific needs and preferences.
This can help streamline your workflow and make commonly used commands more easily accessible. It also allows you to remove unnecessary elements that you don’t use, making it easier to navigate and find the tools you need.
To make customizations, start by right clicking on an empty area of the ribbon and select Customize the Ribbon. In the Excel Options window that appears, you’ll see two sections. The left section displays the tabs currently visible in the ribbon, while the right section displays the tabs you can add.
To customize the ribbon, you have several options:
To add a new tab, click on New Tab in the right section and give it a name.
To add a group within an existing tab, select the tab in the left section, click New Group in the right section, and name it.
To add commands to a group, select the group in the right section, choose commands from the left section, and click Add. You can also customize the order of the commands using the Up and Down buttons.
You can also remove tabs, groups, or commands from the ribbon. Select the item you want to remove in the left section and click Remove.
To change the order of tabs and groups, select the item in the left section and use the Up and Down buttons to rearrange them.
Click OK in the Excel Options window to save your changes and apply the customized ribbon.
To extend Excel’s functionality even further, you can customize the ribbon with additional applications by clicking on the Add-ins button in the Home tab.
Note: Customizing the ribbon is specific to your Excel installation and won‘t affect other users’ ribbons.
28. Improve visual presentation with text wrapping.
Even though spreadsheets aren’t always the most interesting things to look at, you can still take the time to make them easier to read by wrapping text.
Doing this lets you display multiple lines of text within a single cell. It’s particularly handy when you need to include line breaks or break up paragraphs of information within a cell without increasing the row height.
Select the cell(s) with the text you want to wrap. Navigate to the toolbar at the top of the Excel window and locate the Wrap Text button (an icon with an angled arrow). It is typically found in the Alignment section. Then, click on Wrap Text.
29. Add emojis.
Give your spreadsheets a little personal touch by adding in emojis.
To get started, click on the cell where you want to insert an emoji. Then, open the emoji keyboard. This step may vary based on your operating system.
Windows: Use the keyboard shortcut Win + . or Win + ; to open the emoji keyboard.
macOS: Use the keyboard shortcut Ctrl + Cmd + Space to access the emoji keyboard.
Browse through the available emojis and click on the one you want to insert. The selected emoji should now appear in the selected cell.
Emojis may appear small by default in Excel cells. If you want to make them larger to improve visibility, you can adjust the cell size by dragging the row height and column width accordingly.
You can also copy emojis from external sources on the web or other applications and paste them directly into Excel cells.
Note: The ability to use emojis in Excel depends on the version of Excel and the device you are using. Some older versions or platforms may not support emojis or display them correctly. Therefore, it’s important to ensure compatibility with the Excel version and platform you are working with.
Excel Keyboard Shortcuts
Creating reports in Excel is time-consuming enough. How can we spend less time navigating, formatting, and selecting items in our spreadsheet? Glad you asked. There are a ton of Excel shortcuts out there, including some of our favorites listed below.
Create a New Workbook
PC: Ctrl-N | Mac: Command-N
Select Entire Row
PC: Shift-Space | Mac: Shift-Space
Select Entire Column
PC: Ctrl-Space | Mac: Control-Space
Select Rest of Column
PC: Ctrl-Shift-Down/Up | Mac: Command-Shift-Down/Up
Select Rest of Row
PC: Ctrl-Shift-Right/Left | Mac: Command-Shift-Right/Left
Add Hyperlink
PC: Ctrl-K | Mac: Command-K
Open Format Cells Window
PC: Ctrl-1 | Mac: Command-1
Autosum Selected Cells
PC: Alt-= | Mac: Command-Shift-T
Other Excel Help Resources
How to Make a Chart or Graph in Excel [With Video Tutorial]
Design Tips to Create Beautiful Excel Charts and Graphs
Totally Free Microsoft Excel Templates That Make Marketing Easier
How to Learn Excel Online: Free and Paid Resources for Excel Training
Use Excel to Automate Processes in Your Team
Even if you’re not an accountant, you can still use Excel to automate tasks and processes in your team. With the tips and tricks we shared in this post, you’ll be sure to use Excel to its fullest extent and get the most out of the software to grow your business.
Editor’s Note: This post was originally published in August 2017 but has been updated for comprehensiveness.
