Microsoft Excel Basics

Sometimes, Excel seems too good to be true. Need to combine information in multiple cells? Excel can do it. Need to copy formatting across an array of cells? Excel can do that, too.

In fact, if you ever encounter a situation where you need to manually update or calculate your data, you’re probably missing out on a shortcut.

Before spending hours and hours counting cells or copying and pasting data, look for a quick fix in Excel — you’ll likely find one.

In the spirit of working more efficiently and avoiding tedious, manual work, let’s start this Excel deep dive with the basics. Once you have these functions down, you’ll be ready to tackle the advanced Excel lessons head on.

Inserting Rows or Columns

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 pre-existing 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.

Autofill

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.

Tired of struggling with spreadsheets? These free Microsoft Excel templates can help.

Get the Templates

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.

Filters

When you're looking at very large data sets (as marketers often do), 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. 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." Click 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 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.

Sort

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.

Remove Duplicates

Larger datasets 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 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" (under Tools). 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.

Paste Special

Often, you’ll want to transform 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.

Instead, let Excel do the work for you. Go ahead and highlight the column or row you want to transpose. Right click and select “Copy.” Next, select the cells in your spreadsheet where you want your 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 one function I find myself coming back to time and time again. 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 (we’ll get there…) into a chart you can format and graph.

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 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 in 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, semicolon, 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."

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.

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