Learn Excel: An In-Depth, Thorough Tutorial (With GIFs)

By Ann K. Emery for Udemy

Looking for more than a tutorial? Take one of our beginner excel courses online!

Contents


Getting Started


Organizing Your Spreadsheet


Formatting Numbers, Dates, Currency, and Text


Time-Saving Strategies


Getting Started with Formulas


Intermediate and Advanced Techniques

Spreadsheets aren’t just for statisticians, accountants, and economists. These days, so many of us are using Excel for home budgets, work projects, to-do lists, and more. In this guide, we’ll share our favorite techniques for saving your precious time and energy. Excel is a massively powerful tool, and chances are, no matter what you’re working on, there’s a faster and easier way to get it done. With this tutorial, you’ll be on your way from spreadsheet stress to Excel easy street in no time!

Getting Started

Just getting started with Excel? Or, maybe you learned to use Excel a while back and need to refresh your skills? Let’s review the basics first. Then we’ll dive into the intermediate and advanced skills.

Key Excel Terminology

Sometimes Excel novices say that learning to use Excel is like learning a new language. Here are some key Excel terms to get you off to a good start.

Your entire Excel file is called an Excel workbook.

Your workbook will probably include multiple sheets. When you open a new file, you’ll see Sheet1, Sheet2, and Sheet3 included along the bottom of your screen. Later on, we’ll talk about techniques for inserting brand new sheets, re-coloring sheets so that they stand out, password-protecting your sheets, and deleting sheets you no longer need.

Your sheets are organized into tabular Columns and Rows.

Tabs are displayed across the top of your Excel workbook. Each tab contains a variety of beneficial features. For example, the Home tab contains features that let you format text and numbers. The Page Layout tab is where you adjust printer settings, and the Data tab contains a sorting button.

Finally, the Formula Bar is where you’ll get to view formulas that you’ve typed into your spreadsheet.

Similarities between Excel, Word, and PowerPoint

Novices are often pleasantly surprised to learn how many similarities exist between Microsoft products like Excel, Word, and PowerPoint. Whether you need to format text, adjust colors, or insert images, you can apply your existing Word and PowerPoint skills as you begin using Excel.

Basic Text Formatting

Just like with Word and PowerPoint, you’re not stuck with regular ol’ text inside of Excel. You can adjust some or all of your spreadsheet’s contents to bold, italic, or underlined numbers and letters.

Adjusting Colors

Colors are highly customizable in Excel, just like in Word and PowerPoint. Change your font’s color with the Font Color button on the Home tab. Or, fill in your cells with the Fill Color button on the Home tab.

Inserting Images

Want to include your organization’s logo within your spreadsheet? Images are just as easy to use in Excel as in Word and PowerPoint. Head over to the Insert tab and click the Pictures button. Then continue formatting your image -- re-sizing, re-coloring, flipping, etc. -- just like you normally would.

Building Diagrams with SmartArt

I primarily use SmartArt within my PowerPoint slides or Word documents, but sometimes diagrams are helpful in my Excel file, too. Head over to the Insert tab and click the SmartArt button to see all your options. I tweak each diagram’s colors and fonts for a customized look.

Inserting Hyperlinks

It’s easy to include hyperlinks inside your spreadsheet. Just type the link, and when you hit Enter on your keyboard, the text will magically turn itself into a hyperlink. Right-click on the hyperlink and select Edit Hyperlink to display new text for the link, like Link to Udemy text from our example. If you don’t need the hyperlink anymore, simply delete the cell’s text, or just right-click on your link and select Remove Hyperlink.

If you're an excel beginner and work on a Mac, this is perfect for you!

Organizing Your Spreadsheet

When you’re staring at a spreadsheet for hours on end, a little organization can go a long way.

Wrapping Text to Keep Long Sections of Text Within View

Sometimes my cells have words or phrases that are too long to keep within view. One option is to make the columns wider. Another option is to use Excel’s text wrapping feature, which breaks up the letters and words into two, three, four, or more lines so that you can read it all at once.

Highlight or select the lengthy text and click Wrap Text on the Home tab. You can wrap just one row at a time (e.g., just Row 1) or just one column at a time (e.g., Column E). Or, click on the little box between A and 1 in the upper left of the spreadsheet. That little box with the gray triangle will select your entire sheet, allowing you to easily wrap everything at once.

Columns and Rows

Most likely, part of your spreadsheet is going to contain long numbers or long sections of text that can’t squeeze neatly into those teeny tiny rectangular cells. It’s a pain to have your information cut off and out of view. In these scenarios, simply re-size your columns and rows so that your information is arranged exactly how you’d like it.

Wider Columns and Taller Rows

Your first option is to re-size your columns and rows by hand. While there are faster, instant options—you’ll learn about those next—I like to re-size by hand when I only need to re-size one or two my columns or rows, rather than a whole bunch at once.

Simply click on that little line between the letters and drag your columns to the width of your choosing. In this example, watch how I click and drag that little gray line between D and E so that all of the Household Income data can be shown.

Another column resizing trick is to resize all your columns in one fell swoop. Highlight all the columns at the same time, and then click on that little gray line between one set of letters (for example, between the G and the H). All the columns will get wider or shorter at the same time.

My favorite resizing trick is to resize the cells to fit each cell’s unique contents. Just like before, begin by highlighting all the columns at the same time. Then, double-click on one of the gray lines between the letters (for example, between H and I). Column A’s contents are narrower, so Column A will stay narrower. Column F’s contents have more characters, so Column F automatically widens to fit its own contents.

Inserting Extra Columns or Rows

Need an additional column or row? They’re easy to add.

You’ve got two choices. First, let’s pretend you need another column or two between Columns A and B. Right-click on the B, which selects that entire column of information. Then select Insert from the drop-down menu. Excel has created a new column to the left of Column B.

Your second option, which is equally simple, is to use the Insert button on your Home tab. This time let’s pretend you need another column between D and E. Click on the E to highlight that whole column, then head over to the Insert button and select Insert Sheet Columns.

Need additional rows? Use the exact same strategies! Right-click and select Insert, or, use the Insert button on your Home tab.

Deleting Extraneous Columns or Rows

Need to get rid of one of your columns or rows? No problem.

Let’s say you want to delete Columns C and D. Highlight the little C and D letters to highlight their corresponding columns of information, then right-click and select Delete. Or, once you’ve highlighted those columns, head over to the Delete button on your Home tab. The same techniques help you delete your extra rows, too.

Hiding and Unhiding Columns or Rows

If your spreadsheet’s getting pretty massive and you want to temporarily hide some of the information from view, you can do that. In this example, we’ll pretend you don’t need to look at the Age information right now. Right-click on the letter C and select Hide from the drop-down menu. The ages aren’t deleted, they’re just hanging out in the background for a while.

When you’re ready to view that information again, highlight both the letter B and D, then right-click and select Unhide. Now Column C’s back and ready for action again!

The exact same Hiding and Unhiding strategies work with your rows, too.

Freezing Panes So That Some Information Stays in View While Scrolling

Freezing the Top Row

When you’ve got more than 10 or 20 rows of information in your spreadsheet, you have to scroll up and down to see everything. I like to keep my column headers in view even while I’m scrolling down the bottom of the dataset, so I can remember what’s what.

Instructions:

  • Click on the important row that you want to keep in sight (e.g., click on the 1 to the left of the first row to highlight that entire row).
  • Go to the View tab.
  • Click on the Freeze Panes button.
  • Select Freeze Top Row from the drop-down menu.
  • Now, scroll down to the bottom of your dataset. Your top row now magically stays in place so you can read your labels while scrolling!
Freezing the First Column

Similarly, when I’ve got lots of columns in my spreadsheet, I like to keep the left-most column in view while I’m scrolling over to the right in my dataset (since the first column usually contains important details like the name of a person or organization). So, apply the same steps above, but this time highlight the first column.

Freezing Both Rows and Columns

Bonus! The best of both worlds is freezing both your rows and columns.

To freeze both your rows and columns at the same time, click on the cell that’s one row below the row you want to freeze and one column to the right of the column you want to freeze.

In this example, I want both the top row (the survey questions) and the first column (the names) to stay in view as I scroll through my spreadsheet. I need to click on cell B2 because it’s one row below the survey questions and one column to the right of the names.

Then, go to the View tab and select Freeze Panes. In the drop-down menu, select the Freeze Panes option.

Sheets

Inserting New Sheets

My Excel workbooks typically contain three, four, or five different sheets. To create a new sheet, simply click the plus sign beside the sheets on the bottom of your screen.

Deleting Sheets You No Longer Need

Did you create one sheet too many? You can delete sheets at any time by right-clicking on the sheet’s name and choosing Delete from the drop-down menu.

Careful! Once you delete a sheet, it’s gone for good.

Hiding and Unhiding Sheets to Temporarily Remove Them from View

Once in a while, I’ve got too many sheets and my workbook starts to look a little hectic. I’m not ready to delete those sheets permanently; I just like to temporarily hide them to help myself stay focused. Right-click on the sheet’s name and choose Hide from the drop-down menu. When you need to see that sheet again, just right-click and select Unhide.

Password-Protecting Sheets

Want to make sure your coworkers don’t accidentally change something in your spreadsheet? You know it happens! I password-protect some of my sheets to ensure that no one else accidentally removes the content. This technique is especially useful when I’m sharing my spreadsheet with an Excel novice who might not realize that he or she deleted some data by mistake.

Right-click on the sheet you want to password-protect and select Protect Sheet. Excel will ask you to enter a password and to specify which features you want to make available, or unavailable, for that sheet. I typically keep the default settings, which will allow someone to click on cells but not to delete the cell’s content, insert or delete columns, or make any other changes to my spreadsheet.

Re-Naming Sheets

Excel assigns generic, forgettable names to new sheets, like Sheet1, Sheet2, and Sheet3. To keep myself organized, I rename my sheets. I choose simple names that describe the contents of each sheet, like Colors, Images, and Hyperlinks (or, for most of my data projects, as Dataset, Tables, and Charts).

To re-name your sheets, simply right-click on the tabs along the bottom of your spreadsheet and select Rename. Start typing in the new name and hit the Enter key on your keyboard when you’re finished.

Color-Coding Sheets

When I’m sending spreadsheets to colleagues, I want to make sure they spot the most important sheet as soon as they open the file. I’ll turn that crucial sheet bright yellow or bright red to draw extra attention to it.

To color-code a sheet or two, right-click on the sheet’s name along the bottom of your spreadsheet, and select Tab Color from the menu. You’ve got your choice of dozens of colors.

When you no longer need the customized sheet color, simply right-click again, select Tab Color again, and choose the No Color option.

Sorting and Filtering

Have a list of words that need some alphabetizing? Have a list of numbers that you want to arrange from least to greatest or greatest to least? Rather than painstakingly rearranging your rows by hand, let Excel’s Sorting and Filtering features do the heavy lifting for you.

Sorting Data

Most folks are familiar with Excel’s Sorting feature. (And if not, you’re going to love it!) The Sort button is located within your Data tab.

Instructions:

  • Highlight or select the data that you want to re-order.
  • Go to the Data tab.
  • Select the Sort button.
  • In the pop-up window, choose how you want to organize your data, e.g., alphabetically or from greatest to least.

Bonus! You can also create multiple layers of sorting by clicking the Add Level button within that same pop-up window. For example, you might first sort by gender and then by age.

Filters

Have you seen the Excel’s Filters option yet? It’s hiding within plain sight on the Home tab. Filters are one of my all-time favorite features of Excel because, for me, they’re even easier to use than sorting, and they save valuable time as I’m wading through so many numbers.

Instructions:

  • Highlight or select the top row of data (or whichever row has your labels in it - it might be the second or third row if you’ve got any empty space at the top of your spreadsheet).
  • Go to the Home tab.
  • Click on the Sort & Filter button and then select Filter.
  • Tiny boxes with tiny arrows will appear on each of the cells in the top row of your dataset. Congratulations, you’ve got filters!
  • Click on the arrows to sort the data from smallest to largest or largest to smallest, to sort from A to Z or from Z to A. Or, use the checkboxes to filter the data. For example, you might only want to look at males, or only at females. The filtered-out data hasn’t been deleted, and you can make it reappear at any time by simply re-checking the filter’s checkbox.

Bonus! Use your new filters to arrange your dataset by colors. Sometimes I use red, yellow, or green colors to indicate whether items are high, medium, or low priorities for me. I can filter my dataset so that the reds appear on top, followed by the yellows, followed by the greens.

Want a deeper dive into the intermediate functions of excel? Check out how to "Use Excel Like a Pro. Fast."

Formatting: Numbers, Dates, Currency, and Text

Let’s move on to formatting. In Excel, it’s easy to customize how your numbers, dates, currency, and text will appear. Why rely on Excel’s default settings when you can easily format your spreadsheet to fit your exact preferences? We’ll show you how.

Formatting Numbers

There are numerous formatting adjustments you can make to any of the numbers in your Excel file. You can automatically add commas to large numbers, you can automatically round numbers up or down, and you can even automatically fill in cells with the color of your choice.

Adding Commas to Large Numbers

Does your spreadsheet have a bunch of huuuuuge numbers, like 99535767? Sometimes it’s easier to “see” these numbers when they’ve got commas, like 99,535,767 instead of 99535767. Rather than painstakingly adding those commas by hand, use Excel’s built-in Comma Style button.

Rounding Numbers Up or Down

Want to round your numbers up or down? You’ve got two options.

First, you could use the Increase Decimal or Decrease Decimal buttons. The decimal places are still there; they’re just hidden out of view. Try clicking on one of the cells and you’ll see all those millions of decimal places hanging out in the Formula Bar.

Your second option is the =round() function. This function has two pieces. First, tell Excel which cell contains the number that needs some rounding (e.g., A1). Second, tell Excel how many decimal places you want (e.g., 2). Now, when you use the Increase Decimal or Decrease Decimal buttons, you’ll notice that the number has been truncated. Try clicking on one of the cells and only the first two decimal places will show up in your Formula Bar.

Color-Coding Numbers Greater Than a Certain Value

It’s easy to color-code your numbers by hand if you’re only dealing with 5, 10, or 20 numbers. But it’s even easier to automatically color-code your numbers with Excel’s Conditional Formatting feature.

Highlight or select the range of information that you want to color-code. On the Home tab, click on the Conditional Formatting button. You’ll notice a couple drop-downs within drop-downs that contain dozens of color-coding features for you to explore.

Sometimes, I want to see how many numbers fall above a certain value. For example, I might want to see how many people are above age 35. Highlight or select the age values; click on the Conditional Formatting button; select Highlight Cells Rules and then select Greater Than.

As shown in the pop-up window, you’ll get to customize your cut-off value (I changed my cut-off from 47 to 35 in this example). You’ll also get to customize your colors. For instance, you might want everyone older than 35 to show up as red, yellow, or green.

Color-Coding Numbers that Fall Between Two Values

Excel’s Conditional Formatting icon has endless possibilities. In this example, I color-code everyone between the age of 25 and 45.

On your Home tab, simply click Conditional Formatting, Highlight Cells Rules, and Between. The subsequent pop-up window gives you customization options that can be tweaked to fit your specifications.

Color-Coding the Largest 10 Numbers

Or, maybe you’re interested in seeing the top 10 highest ages from your list.

Go to Conditional Formatting, Top/Bottom Rules, and Top 10 Items. You might want those highest items to appear green, red, or yellow. Or customize the colors further by selecting Custom Format from the pop-up menu.

Coding the Top 10% of Numbers

Interested in making the top percentage of items stand out?

Go to Conditional Formatting, Top/Bottom Rules, and Top 10% Items. In the pop-up window, select the exact percentage you’re interested in (10%, 25%, 50%, etc.) and then select the colors of your choosing.

Visualizing Patterns with Data Bars

Data Bars are miniature within-cell bar charts. I primarily use them to quickly explore patterns in my dataset. Sometimes I decide to make an actual bar chart of the data later on. Other times, I’m simply using these tiny charts to help my brain spot the key patterns.

To create Data Bars, highlight or select the range of numbers that you want to visualize. Then, go to the Home tab’s Conditional Formatting icon and select Data Bars. You can create gradient-filled Data Bars or solid-colored Data Bars, and you’ve got a variety of color options available. In the example below, I created blue solid-colored bars.

Notice how the bars will automatically enlarge themselves to fit the size of the cell. In the example below, watch how the bars get wider as I widen the column.

After I insert Data Bars, I often sort my numbers from largest to smallest or smallest to largest as a second layer of behind-the-scenes exploration. To sort your numbers, highlight the range of numbers, go to the Data tab and click the little A → Z button or Z → A button (to arrange numbers from least to greatest or greatest to least, respectively).

Visualizing Patterns with Color Scales

Color Scales are lots of fun. Excel uses Conditional Formatting to create a “heat table” design for your numbers, in which higher numbers get darker colors and lower numbers get lighter colors. This technique helps me spot which numbers are largest or smallest at a glance.

To insert Color Scales, highlight your numbers and then go to the Conditional Formatting button on the Home tab and select the Color Scales option from the menu. As usual, Excel gives you lots of choices. You might select a red/blue color scheme or a green/while color scheme, as I did in this example.

Checking for Duplicate Numbers

Whenever I’m dealing with peoples’ names or ID numbers, I like to make sure each person is only listed once on my spreadsheet. Removing duplicate entries early on in the process ensures that my numbers will be accurate later on.

It would take a lot of time – and mental energy – to scan a long list for double-entries. No matter how hard I was paying attention, I would probably miss one or two. So, let’s let Excel do the hard work for us.

Excel’s Conditional Formatting will change the color of duplicates to make them stand out. For example, you can add a red or yellow fill to those cells. Highlight your list of numbers and go to Conditional Formatting on the Home tab. Select the first option from the list, Highlight Cells Rules. Then, select Duplicate Values. You’ll get a new pop-up window that gives you plenty of color options. In my example, I selected a light green fill with dark green text.

My dataset contained one set of duplicates—person 116 was accidentally listed twice. Once I spotted the error, I deleted the double-entry by highlighting one of those double rows, right-clicking, and selecting Delete.

Removing the Conditional Formatting’s Instant Color-Coding

Want to remove that instant color-coding and get your spreadsheet back to its original state? Conditional Formatting is easy to undo.

Go to Conditional FormattingClear Rules. You can remove your Conditional Formatting from just a section of cells that you’ve already highlighted (Clear Rules from Selected Cells) or from everywhere within your sheet at once (Clear Rules from Entire Sheet).

Formatting Dates

Does your file include any dates? If so, check out these simple formatting tips so that your dates are displayed in the style you want.

Selecting Your Preferred Date Format

If your spreadsheet contains dates, you can select either the Short Date or Long Date format.

Separating Out the Date’s Month, Day, and Year

Sometimes I need to parse out my date: I might only be interested in the specific month, or the specific day, or the specific year—rather than the entire date.

In these situations, we can use Excel’s =month(), =day(), and =year() functions.

To find the date’s month, type =month( and then click on the cell that contains the full date (like A2 in this example). Then, add a closing parenthesis to the end of the function and press the Enter key on your keyboard. Excel will give you a 1 to indicate that the full date’s month is January.

To find the day of the month, type =day( and then click on the cell that contains the full date (again, cell A2). Add a closing parenthesis to complete the function and click Enter. Excel gives you a 1 to indicate that January 1 is the first day of the month.

Finally, to separate out just the year from the full date, type =year( and click on the cell that contains the full date you’re interested in (cell A2). You know the drill: Add another parenthesis to complete the function, press Enter on your keyboard, and Excel will give you a value of 2015.

Figuring Out the Length of Time Between Two Dates

Did you know that Excel stores semi-recent dates as numbers? January 1, 1900 is actually stored as a 1 behind the scenes in Excel which means that January 1, 2015, which comes 42,005 days later, is stored as 42,005.

This cool feature allows you to perform basic addition and subtraction with dates. Let’s pretend you want to figure out how long an employee worked at your organization. You can use subtraction: the Last Day of Employment minus the First Day of Employment equals the Length of Employment.

Auto-Filling Dates

I bet you’ve got better things to do with your time than to type Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sept, Oct, Nov, Dec every time you want to see a list of month abbreviations. Excel to the rescue! Type at least the first three abbreviations (Jan, Feb, Mar). Then, highlight those three cells. Scroll your cursor over the tiny square in the lower left-hand corner of the box surrounding the three highlighted cells and drag the tiny square downwards to auto-fill the remaining month abbreviations.

Would you rather have a list of the full month names, rather than the abbreviations? Type January, February, March into A1, A2, and A3. Highlight those cells and drag them downwards to fill in the remaining nine months.

In the previous examples, we auto-filled months going downwards. You might prefer to have the months or month abbreviations going from left to right across your spreadsheet. In this scenario, type Jan, Feb, Mar into A1, B1, and C1. Highlight those three cells and drag them towards the right to auto-fill the other abbreviations.

Finally, you might want the full month names (not the abbreviations) going across the top of your file. I bet you’ve got the hang of this technique by now! Try typing January, February, March into cells A1, B1, and C1. Then, highlight the cells and drag them over to the right. These small time-saving tricks will add up to big rewards in the long run.

Formatting Currency

Creating a list of your personal expenses? Getting ready to submit that budget for your upcoming grant application? Tracking a project’s revenues compared to expenses? Chances are, you’re probably going to use Excel for currency calculations at some point in your personal or professional life. These easy formatting tips will save you from headaches along the way.

Selecting Your Preferred Format

If your spreadsheet deals with something money-related, you can select either the Currency or Accounting format.

Adjusting Decimal Places

You’ve also got control over how many decimal places are shown. Click on the Increase Decimal or Decrease Decimal buttons. Rather than deleting the extra decimal places by hand, let Excel do the heavy-lifting for you. Notice how Excel automatically rounds numbers for you, too.

Formatting Text

Finding the First Initial from Someone’s Name

There are all sorts of ways to parse out text. In other words, you can have Excel tell you a person’s first initial or last initial.

In this example, we’ll use the =left() function to find the first initial for Beyonce, Barack Obama, and Pablo Picasso. Type =left( and then click on the cell that contains that person’s first name (e.g., cell A2 which contains Beyonce’s first name). Then, type a comma to move on to the second part of the function. Next, Excel wants to know how many characters (letters or numbers) you’re interested in parsing out. Type a 1 to have Excel give you just the first letter.

Your completed function in cell C2 will read: =left(A2,1)

Then, add a closing parenthesis to your function and click on the Enter key on your computer’s keyboard to complete the function. Excel will give you a B because B is the left-most letter of Beyonce’s name.

You’re not limited to just the first letter or number in a cell. You could also type =left(A2,2) into cell D2. Excel will return Be because Be are the first two characters in Beyonce’s name.

Switching All Letters to Lowercase

Sometimes you might need to change all your letters to uppercase or all your letters to lowercase.

First, let’s have Excel automatically turn some of our cells into lowercase letters. Type =lower( and then click on cell A2, which contains Beyonce’s first name. Add a ) to finish the function and then press Enter on your keyboard.

In other words, your full function will say =lower(A2)

Excel will grab the Beyonce text from cell A2 and turn that into beyonce in cell C2.

Switching All Letters to Uppercase

A related text formatting technique is that Excel enables you to switch all your letters into uppercase letters with just a simple function.

Type =upper( and click on cell A2 to select Beyonce’s first name. Then, add another parenthesis to the end of the function to close it off, and press Enter.

Excel will transform Beyonce from A2 into BEYONCE in C2.

Proper Capitalization

The =proper() function might be the most useful of all: It transforms your messy text into a proper noun format, meaning that the first letter is capitalized and the other letters are lowercase.

In a perfect world, I would receive spreadsheets that already have this easy-on-the-eyes text formatting. In reality, most of the spreadsheets that I receive from coworkers or download from the internet have a mix of fully lowercase and fully uppercase names.

Simply type =proper( into cell D2. Then click on the poorly-formatted text you want to transform (like cell C2, which contains Beyonce’s name formatted as BEYONCE). Finally, add a ) to complete the function and press Enter on your keyboard.

Your final function will look like this: =proper(C2)

I bet this trick will save you hours of time over the course of your career!

Color-Coding Cells that Contain a Certain Word or Phrase

As you’ve seen in the previous examples, I often color-code certain cells to draw attention to them. Important details can get lost when you’re staring at a default spreadsheet with a white background and black letters and numbers.

In this example, I created a to-do list and categorized items as being a High, Medium, or Low priority to accomplish. I decided to use stoplight colors, in which high-priority items would be red, medium-priority items would be yellow, and low-priority items would be green.

To add the instant color-coding, highlight or select the cells you’re interested in (in this case, cells B4 through B12). Then, go to the Home tab and click on the Conditional Formatting icon. In the Conditional Formatting menu, select Highlight Cells Rules and Text that Contains. You’ll get a separate pop-up window. Type the text of interest (e.g., High) and select the colors of your choice (like a light red fill with dark red text).

Then, go through those clicks again to color-code the Medium and Low cells.

Time-Saving Strategies

I love Excel but that doesn’t mean I want to spend more time than necessary using it. Instead, I use these time-saving strategies to save my mental energy for tasks that are more important. Here are the top techniques everyone should know.

Crucial Excel Shortcuts

Want to save hours and hours of time in the long run? Excel shortcuts shave off a few seconds each time you use them adding up to valuable time saved in the long run. I use CTRL + C to copy, CTRL + V to paste, and CTRL + Shift + Arrow keys to highlight entire rows or columns at once.

Combining Content from Multiple Cells into One Cell

Sometimes our numbers or letters start out in separate cells but we really need to combine them into a single cell. In the following examples, we’ll pretend your spreadsheet lists out peoples’ first names in one column and their last names in a second column (e.g., Barack in Column A and Obama in Column B). We’ll pretend that what you’re really interested in is building a third column that reads Barack Obama or Obama, Barack.

Excel has not one but two excellent strategies for combining content from multiple cells into a single cell. First, you could use the =concatenate() function. Second, you could use the =& function. No matter which function you choose, you’ll get the same result.

Concatenate

Concatenate is a fancy word that basically means you want to chain together or add together a few different segments.

Example 1: First Last

We’ll begin by transforming Beyonce and Knowles into Beyonce Knowles. First, type =concatenate( into C2 and then click on A2, where Beyonce’s first name is located. Second, add a comma to move on to the next part of the function. Third, click on <B2 where Knowles is located. Add a closing parenthesis and hit the Enter key on your keyboard to complete the function.

Your final function will read =concatenate(A2,B2) and will give you the result BeyonceKnowles.

We’re almost there! BeyonceKnowles is close, but we really want the finished product to say Beyonce Knowles (with a space between her first and last name).

This time, type =concatenate(A2,“ ”,B2) into C2 and then hit Enter. Excel will give you Beyonce Knowles.

Pay special attention to the commas in that function. Now, there are two commas, which separate the three different pieces of the function.

The quotation marks are like placeholders that instruct Excel to add a space in between her first and last name.

Example 2: Last, First

Ready for the intermediate-level version of the Concatenate function? Now let’s combine Beyonce and Knowles into a single cell that reads Knowles, Beyonce.

In cell C2, type =concatenate(B2,“, ”,A2) and you’ll get Knowles, Beyonce.

We can type anything we want between those quotation marks. In this example, I chose to type “, ” which adds a comma and a space between the content from the two other cells we selected.

Then, drag the formula downwards to combine Barack and Obama into Obama, Barack and Pablo and Picasso into Picasso, Pablo.

If this technique isn’t working for you, first, check to make sure you spelled concatenate correctly. Misspellings of that jargon-y word are pretty common. Second, make sure you’ve got commas, quotation marks, and spaces in the exact right places. It just takes a little practice to get the hang of this technique.

&: Using the And Operator

The And Operator, displayed as an ampersand or &, is very similar the Concatenate function.

Example 1: First Last

Let’s try the same thing as before: combining Beyonce and Knowles, which are stored in separate cells, into one cell that reads Beyonce Knowles.

In cell C2, type =A2&B2 and Excel will give you the result BeyonceKnowles.

Now, let’s add a space so that BeyonceKnowles displays as Beyonce Knowles.

This time, type =A2&“ ”&B2 into cell C2. Just like the concatenate function earlier, we can type anything we want into those quotation marks, even if it’s just a single space.

Example 2: Last, First

Ready for the next step? Now, let’s combine Beyonce and Knowles into Knowles, Beyonce using the And Operator.

In cell C2, type =B2&“, ”&A2 and your spreadsheet will result in Knowles, Beyonce.

Transposing (Flipping) Your Rows and Columns with Paste Special

Most likely, your Excel files contain information in a tabular format. In other words, you’ve probably got a few columns going across the page (like 0-20 year olds, 21-40 year olds, and 41-60 year olds) along with a few rows going down the page (like City A, City B, and City C).

Sometimes we type in our data by hand, and in that case, we get to choose exactly how our table is arranged. If I want the age groups to be listed across the top of my page, I type the information into my spreadsheet with that desired layout in mind.

Other times, we receive spreadsheets that our colleagues or classmates have created. Or, we might download datasets from databases or websites. In these situations, we have no control over the table layout. We inherit a spreadsheet that someone else has created.

Once in a while I need to swap, flip, or transpose the rows and columns. For instance, I might need to adjust the layout so that the age categories are going down the page rather than across the page.

Rather than re-type your entire table by hand, use Excel’s Paste Special and Transpose buttons. You’ll save time and, most importantly, avoid the possibility of human error.

Instructions:

  • Highlight or select the table you want to flip
  • Click copy (CTRL + C)
  • On the Home tab, go to Paste Special and select Transpose
  • Click CTRL + V to paste your data into the new area of your choosing (e.g., into A6 below the original table).

Getting Started with Formulas

Figuring Out Which Function to Use

In your Formula Bar, click on the f (or fx) button to be taken to a menu of Excel’s functions. This Insert Function pop-up, as shown below, will give you myriad options to choose from.

Notice how the text down below gives a brief description of the function. For example, the ABS function will give you the absolute value of a number.

You can choose to view all the functions alphabetically, as shown in the previous image, or you can filter by category.

In this example, I selected the Text category. Do you recognize our friend the Concatenate function?

Basic Functions

Average

Let’s pretend you want to calculate the average (a.k.a. mean) score for students. Think back to high school math class: To calculate the average, you add up all the scores and then divide that sum by 5 because there are 5 students in the group. Rather than grabbing your calculator and doing the math yourself, let Excel’s =average() function handle the calculations for you.

Type =average( and then click on the range of scores (so B2 through B6 for the reading scores). Add a closing parenthesis to the end of the function and press Enter on the keyboard.

Your function will say =average(B2:B6) and the result is an average score of 49.2.

Once you calculate the average reading score in Column B, you can also drag that function over to the right to find the average math score, as I demonstrate in the video below.

Median

To calculate the median reading score, type =median(B2:B6) into cell B8 and press Enter. You’ll get a result of 41.

Mode

The mode is the most-occurring number in a set of numbers. To find the mode for this sample dataset, you would type =mode(B2:B6). The most common reading score is 41 and the most common math score is 68.

Standard Deviation

Remember the term standard deviation from your high school math class? The standard deviation basically measures how stretched out your set of numbers is. A higher standard deviation means the numbers really vary, while a small standard deviation means the scores in that group of scores are pretty similar to one another.

To calculate the standard deviation for our sample numbers, you would type =stdev(B2:B6) and get a result of 20.8.

Intermediate and Advanced Techniques

Pivot Tables

Pivot tables are the fastest, easiest way to make sense of your data, and they’re a great way to score your next promotion. Best of all, it’s easier than you think. In these next lessons, I’ll show you how to insert a pivot table and then drag and drop variables to find patterns in your spreadsheet.

Step 1: Insert a Pivot Table

The first step is inserting a pivot table from scratch. Click on the cell in the upper left-hand corner of your tabular data. In this case, we would click on cell A5 because that cell is the upper left-most cell in this table.

Then, go to the Insert tab and click Pivot Table.

Step 2: Rename Your Sheet to Stay Organized

Your pivot table will appear in a new sheet. To keep my workbook clutter-free, I give each sheet a descriptive name. Rename your new pivot table sheet (something easy like “pivot” is fine) by right-clicking on the sheet and clicking on Rename Sheet.

Step 3: Explore the Pivot Table Fields

Now, let’s take a closer look at that pivot table that popped up in your new sheet.

In our original data sheet (named Pivot Table Data in my example), the columns are named Employee, Gender, Age, Industry Experience, and State.

Next, let’s check out the pivot table’s sheet (named Pivot Table in my example). Some of the important pivot table features appear along the right side of my screen. The boxes say Pivot Table Fields, Filters, Columns, Rows, and Values.

The Pivot Table Fields box, in the upper right, contains all the variables that we get to play around with. Notice how each of the columns of data from our Pivot Table Data sheet show up here: Employee, Gender, Age, Industry Experience, and State.

Step 4: Drag and Drop Variables

Now, on to the fun part, dragging and dropping variables! This feature is what makes a pivot table a pivot table.

Let’s start with simple math: Figuring out how many males and how many females are listed in our spreadsheet.

Click on Gender in the Pivot Table Fields list and drag it downwards into the Rows box. The pivot table, located off to the left in the main spreadsheet area, will say Row Labels, Female, Male, and Grand Total. It’s starting to build a table for us, which will eventually contain tallies of males and females.

Then, drag Gender into the Columns box. Watch how the table pivots, or switches from rows to columns accordingly. Now, Female and Male are listed across the top of the table rather than down the side.

Let’s figure out how many males and females are in the dataset. We’ve got the outline of our table but we need to fill in the body of the table with the actual tallies.

Watch below as I drag Employee into the Values box. The pivot table on the left-hand side of my screen will automatically update to show that there are 6 female employees and 4 male employees in my spreadsheet.

On to the really, really fun part: cross tabulations! Cross tabulations, or crosstabs for short, is a fancy way of saying that pivot tables give us the ability to stack multiple variables on top of each other. Figuring out how many males and how many females are in our spreadsheet is a good starting point, but crosstabs lets us dig even deeper into the information.

Watch as I drag the State variable from the Pivot Table Fields box into the Columns box. The Gender categories (Female and Male) are listed along the left side of my table and the State categories (DC, MD, and VA) are listed along the top of my table.

The inner body of the table shows how many people fall into each category. For example, there are 3 females who live in DC, 3 males who live in DC, 1 female who lives in Virginia, 1 male who lives in Virginia, and 2 females who live in Maryland.

The Grand Total section reminds us that we’re talking about 10 people altogether.

Another way to cross tabulate your data is to double-stack two or more variables into a single box.

Before, we had Gender in Rows and State in Columns.

Now, I’ll drag State into the Rows box, right below Gender. Watch how the tallies in the pivot table on the left update themselves accordingly.

Within a single box, like the Rows box, you can also re-order the variables. You can have Gender on the top, or drag State above Gender. There’s no single right answer here; practice dragging and dropping your variables into whichever order is most interesting and useful for you.

Bonus! Filter Out Certain Variables

We’ve explored the Pivot Table Fields, the Columns, the Rows, and the Values.

Now, let’s take a look at the remaining box, Filters. Just like its name implies, this option lets you sift out certain categories so that you can focus exclusively on the information that’s most helpful for you.

Watch as I drag the State variable into the Filters section. State now appears in the first row of my spreadsheet. Do you see the little arrow beside the word All? The arrow reminds us that we’ve just created a filter. When I click on that arrow, a drop-down menu appears. I can choose to only look at people who live in DC, Maryland, and/or Virginia.

Bonus! Grouping

We haven’t explored the employees’ ages yet. The other categories were simple: Female/Male, DC/MD/VA, and so on. But with Age, there are as many different possibilities as there are employees.

First, watch as I drag Age into Rows and Employee into Values. The tallies aren’t very helpful here. My resulting pivot table on the left side of the screen just tells me that there is 1 employee for each of the ages listed. For example, there’s 1 employee who’s 24, 1 employee who’s 28, 1 employee who’s 35, and so on.

We need to condense the 10 different ages into a small number of categories. Let’s pretend that we’re interested in dividing the employees into two groups, the younger half and the older half.

Select or highlight the first 5 ages (the younger half of the employees). Then, right-click and select Group. Those first 5 ages are now clustered together in a brand new category called Group1.

Now let’s do the same thing for the 5 employees who fall into the older half of the bigger group. Highlight or select those 5 ages (from 50 down to 65), right-click, and select Group. We’ve got two groups: Group1 and Group2.

It’s pretty obvious to us what each group represents—the first group contains the younger employees and the second group contains the older employees. But, this grouping might not be so obvious if we email our spreadsheet to a coworker, classmate, or friend.

To stay organized, let’s give each of the groups a descriptive name. Removing the guesswork will keep everything neater in the long run.

Click on Group1 and simply begin typing Younger Half. Then, click on Group2 and type Older Half.

Finally, let’s take a moment to explore the toggle feature of our Younger Half and Older Half groups. Click on the plus and minus signs to expand or collapse the menu.

For more practice on pivot tables, take a course focused exclusively on Pivot Tables!

Vlookup

The vlookup function is my all-time favorite function in Excel, because it helps us merge data from various tables, sheets, and files into a single master table.

Sometimes Excel novices are hesitant to try vlookup because it requires that you fill in four different pieces of information. Learning the Excel lingo here is truly like learning a new language. Stick with it and keep practicing, and you’ll be a fluent vlookup user in no time!

Here’s the information that we’ll need to complete:
=vlookup(lookup_value,table_array,col_index_num,[range_lookup])

In this next series of videos, I’ll walk you through each of the four segments of the vlookup function. I’ve got five people (Ann, Isaac, Tony, Keely, and Dan). I’ve also got two different tables of data (Favorite Color and Favorite Food).

Let’s pretend I want to create a master dataset that contains both the colors and the foods together. In a perfect world, I’d be able to copy and paste the colors and foods together. But in the real world, we’ve typically got different numbers of people in each of the original tables. For example, we’ve got information about Ann, Isaac, Tony, and Dan in our Favorite Color table, but we’ve only got information about Ann, Keely, and Isaac in our Favorite Food table, so a simple copy and paste isn’t possible.

Sure, with just five people, we could fill in this information by hand. But what if our dataset contains information about 50, 500, or even 5,000 different people? Copying and pasting could take all day, and we’d probably make a million mistakes along the way. Vlookup to the rescue!

Step 1: lookup_value

First, let’s fill in the lookup_value, which is the first piece of the vlookup function.

The lookup_value is the cell that contains the person’s name or ID number that we’re interested in. These names or ID numbers are the links that connect all the tables together.

The names or ID numbers must be located in the first column of each table–in the first column of your new combined dataset and in the first column of every single table from which you’re pulling data.

In this example, watch as I type =vlookup( into cell B8. Click on the cell that contains the name or ID number that you want to look up in one of your other tables. Then, insert a comma, which moves us on to the second section of the function.

So far, my function reads: =vlookup(A8,

Step 2: table_array

Second, we have to indicate the table_array, which is the table from which we’re pulling data. In this example, we want to get information from the Favorite Color table into our master table down below. The table_array for the Favorite Color table is A1:B5. In other words, that table begins in cell A1 and ends in cell B5.

My function reads: =vlookup(A8,A1:B5,

Step 3: col_index_num

Third, we have to indicate the col_index_num, or Column Index Number, which is the second column in that Favorite Color table from which we’re pulling in information.

Type in the number of the column you’re interested in. For example, we want to know favorite colors, which are located in the second column of our Favorite Color table, so we type a 2 into the vlookup function. As usual, conclude with a comma to move on to the fourth and final segment of our function.

My function reads: =vlookup(A8,A1:B5,2,

Step 4: range_lookup

Fourth, we need to indicate the range_lookup. We have to type the word true or false into the fourth and final section of our vlookup function.

A true will give us an approximate match and a false will give us the exact information we’re looking for. We obviously want precise information, so type false into the function and end with a closing parenthesis.

My completed function reads: =vlookup(A8,A1:B5,2,false)

We can see that Ann’s favorite color is blue.

A Second Example

Let’s go through a second vlookup example to make sure the four pieces of the function make sense. We’ll continue creating a master table that combines content from both the Favorite Color and Favorite Food tables into one single table.

First, in cell C8, type =vlookup(A8, to set the lookup_value as Ann.

Second, indicate the boundaries of the Favorite Food table that we want to include. My function now reads =vlookup(A8,D1:E4

Third, tell Excel which column of the Favorite Food table to focus on. The foods are listed in the second column of that mini-table, so enter a 2 into the vlookup function. My function says =vlookup(A8,D1:E4,2

Finally, type false into the function and close your parentheses. The completed function says =vlookup(A8,D1:E4,2,false) and tells us that Ann’s favorite food is pizza.

Are you ready to get into the most advanced features of excel? Learn how to master interactive dashboards and VBA macros.

Still want more? Check out our entire selection of excel courses.

Share this resource