Microsoft Excel know-how is so expected that it hardly warrants a line on a resume anymore. But how well do you really know how to use it?
Marketing is more data-driven than ever before. At any time you could be tracking growth rates, content analysis, or marketing ROI. You may know how to plug in numbers and add up cells in a column in Excel, but that’s not going to get you far when it comes to metrics reporting.
What is Microsoft Excel?
Microsoft Excel is a popular spreadsheet software program for business. It’s used for data entry and management, charts and graphs, and project management. You can format, organize, visualize, and calculate data with this tool.
How to Download Microsoft Excel
It’s easy to download Microsoft Excel. First, check to make sure that your PC or Mac meets Microsoft’s system requirements. Next, sign in and install Microsoft 365.
After you sign in, follow the steps for your account and computer system to download and launch the program.
Microsoft Excel Spreadsheet Basics
Sometimes, Excel seems too good to be true. Need to combine data in multiple cells? Excel can do it. Need to copy formatting across an array of cells? Excel can do that, too.
Let’s start this Excel guide with the basics. Once you have these functions down, you’ll be ready to tackle more pro Excel tips and advanced lessons.
Inserting Rows or Columns
As you work with data, you might find yourself needing to add more rows and columns. Doing this one at a time would be super tedious. Luckily, there’s an easier way.
To add multiple rows or columns in a spreadsheet, highlight the number of pre-existing rows or columns that you want to add. Then, right-click and select “Insert.”
In this example, I add three rows to the top of my spreadsheet.
Autofill lets you quickly fill adjacent cells with several types of data, including values, series, and formulas.
There are many ways to deploy this feature, but the fill handle is among the easiest.
First, choose the cells you want to be the source. Next, find the fill handle in the lower-right corner of the cell. Then either drag the fill handle to cover the cells you want to fill or just double-click.
When you’re looking at large data sets, you usually don’t need to look at every 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 data to only see certain rows at one time. In Excel, you can add a filter to each column in your data. From there, you can choose which cells you want to view.
To add a filter, click the Data tab and select “Filter.” Next, click the arrow next to the column headers. This lets you choose whether you want to organize your data in ascending or descending order, as well as which rows you want to show.
Let’s take a look at the Harry Potter example below. Say you only want to see the students in Gryffindor. By selecting the Gryffindor filter, the other rows disappear.
Pro tip: Start with a filtered view in your original spreadsheet. Then, copy and paste the values to another spreadsheet before you start analyzing.
Sometimes you’ll have a disorganized list of data. This is typical when you’re exporting lists, like marketing contacts or blog posts. Excel’s sort feature can 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. Choosing A-Z means the list will sort in alphabetical order.
- If the “Z” is on top of the “A,” click the button twice. Z-A selection means the list will sort in reverse alphabetical order.
Large datasets tend to have duplicate content. For example, you may have a list of different company contacts, but you only want to see the number of companies you have. In situations like this, removing duplicates comes in handy.
To remove duplicates, highlight the row or column where you noticed duplicate data. Then, go to the Data tab, and select “Remove Duplicates” (under Tools). A pop-up will appear so that you can confirm which data you want to keep. Select “Remove Duplicates,” and you’re good to go.
If you want to see an example, this post offers step-by-step instructions for removing duplicates.
You can also use this feature to remove an entire row based on a duplicate column value. So, say you have three rows of information and you only need to see one, you can select the whole dataset and then remove duplicates. The resulting list will have only unique data without any duplicates.
It’s often helpful to change the items in a row of data into a column (or vice versa). It would take a lot of time to copy and paste each individual header.
Not to mention, you may easily fall into one of the biggest, most unfortunate Excel traps — human error. Read here to check out some of the most common Microsoft Excel errors.
Instead of making one of these errors, let Excel do the work for you. Take a look at this example:
To use this function, highlight the column or row you want to transpose. Then, right-click and select “Copy.”
Next, select the cells where you want the first row or column to begin. Right-click on the cell, and then select “Paste Special.”
When the module appears, choose the option to transpose.
Paste Special is a super useful function. In the module, you can also choose between copying formulas, values, formats, or even column widths. This is especially helpful when it comes to copying the results of your pivot table into a chart.
Text to 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 you want to separate someone’s full name into a first and last name for your email marketing templates.
Thanks to Microsoft Excel, both are possible. First, highlight the column where you want to split up. Next, go to the Data tab and select “Text to Columns.” A module will appear with more 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 in all the columns where you want the split to occur.
Select “Delimited” to separate the full name into first name and last name.
Then, it’s time to choose the delimiters. This could be a tab, semicolon, comma, space, or something else. (For example, “something else” could be the “@” sign used in an email address.) Let’s choose the space for this example. 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.”
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 spreadsheet, 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 copy formatting from one area of the worksheet to another.
To do this, choose the cell you’d like to replicate. Then, select the format painter option (paintbrush icon) from the top toolbar. When you release the mouse, your cell should show the new format.