Advertisement
  1. Business
  2. Microsoft Excel
Business

How to Use Excel: 12 Techniques for Power Users

by
Difficulty:IntermediateLength:LongLanguages:

Excel is great, but trying to figure it out how to use Excel on your own can get you only so far because it isn’t intuitive. But if you use the techniques and tips in this tutorial, you’ll be able to get your work done faster and without a lot of stress. 

Learn how to apply Excel formulas, calculations, filtering, data manipulation, workflow efficiencies, and more. Here are a dozen Excel techniques and features you need to know.

Screencast

Before you begin, go ahead and download the free Excel file worksheet. There are multiple sheets included in the file, one to practice each of the twelve Excel techniques. Watch the complete tutorial screencast above or work through the step-by-step written version below.

1. The Basics of Writing a Formula

Every formula must start with an equal sign. If you don’t have an equal sign, Excel won’t do the calculation; it will just display what you typed. It’s Excel’s way of saying, “Hey, calculate this.” Think of the result of your formula as what should be on the left side of the equal sign if you were using pencil and paper.

And where do you write a formula? Write it in the cell where you want the answer to appear. Refer to the 'simple' sheet in the free Excel file for this exercise.

Example: add the values of B2, B3 and B4 and put the total in B6

In B6, enter this formula:

=B2 + B3 + B4

Simple formula
Simple formula

Then press Enter to get the result. If you don’t press Enter (or click the small check mark just above column A), nothing will happen. 

Simple Formula Entered
Simple formula entered

Subtraction, multiplication, division and exponents work the same way. For the most part, use the symbols you’d normally use or refer to this chart:

OperationSymbol
Addition+
Subtraction-
Multiplication*
Division/
Exponent^ (example: B5^2 is the value of B5 squared)
Greater than>
Less than<
Greater than or equal to>=
Less than or equal to<=
Join&

2. For More Complex Calculations, use Functions

If you want to do a calculation that’s a little more involved than a simple formula, insert a function into your formula. Excel has about 400 of them, so you’ll probably find some that you need. There are functions for business, statistics, finance, date and time, text, document information and more.

The syntax of all functions is:

=NAME OF FUNCTION(......)

You always start with an equal sign if the function is at the beginning of a formula, then the name of the function, then a set of parentheses. There’s usually something in the parentheses, but not always.

Example: Find the total and the average of a column of numbers. Refer to the 'functions' sheet in the free Excel file for this exercise.

In B10, enter the function:

=SUM(B2:B8)

In B11, enter the function:

=AVERAGE(B2:B8)

Sum and Average
Sum and average

Press Enter to get the results. 

Sum and average result
Sum and average result

3. For Fast, Intelligent Copy & Paste, Use AutoFill

Let’s say you write a formula at the top of a column and want to re-use the formula down the column (or you have a formula at the beginning of a row and want to re-use the formula across the row). Instead of manually copying and pasting, use the AutoFill feature to make quick work out of it and have the formula adjust itself automatically. You can also use AutoFill to enter months or days of the week automatically.

To use AutoFill, look for the tiny dot in the lower-right corner of the current cell. (If you have several cells selected, they share a common dot.) When you put the mouse pointer on the dot, the pointer turns into a crosshair. Then drag the crosshair down the column or across the row.

Example: Do simple multiplication in D2, then AutoFill down to D8. Refer to the 'autofill' sheet in the free Excel file for this exercise.

Write the first formula as you normally would.

Autofill formula
Autofill formula

Enter the formula. If necessary, click back on D2. Roll the mouse pointer over the heavy dot in the cell’s lower-right corner, and the mouse pointer will become a crosshair. 

Crosshair
Mouse becomes a crosshair

Either drag the crosshair down column D, or just double-click. The column will have the correct formulas and correct results down the column. 

Double-click to select the column
Double-click to select the column

4. Filtering and Sorting Data

You can use Excel as a small database, sorting rows of data alphabetically or numerically, and filtering just the data you want. It works best when columns have headers, like First Name, Last Name, and so on. 

The most important thing to remember is don’t select a column before sorting. That will sort the column independently of the rest of the data, and that’s probably not what you want. When you click in a column, Excel is smart enough to know what you’re doing.

When it comes to filtering, Excel has great automatic tools.

Example: Sort a list by last name, city or other column, in ascending or descending order. Refer to the 'filter and sort' sheet in the free Excel file for this exercise.

Simple sort
Simple Sort in Excel

Click any cell in the Last Name column. On the Home tab of the Ribbon, click Sort and Filter, then choose A to Z or Z to A. Try this with any of the other columns.

You may have noticed that you can do filtering from the same drop-down menu. 

Example: Now Filter the list to show addresses only from Alaska and California.

Click anywhere inside the data area and from the Sort and Filter menu, choose Filter.

Simple Filter
Simple Filter

As soon as you do, you’ll notice the column headers get drop-down arrows. Now: 

  • Click the drop-down arrow in the State column.
  • Then click the Select All box to clear all the state checkboxes.
  • Re-select just AK and CA, then click OK.
Filter box
Filter Box

The list now shows only the rows for those two states. If you want, sort the states A to Z. That will group all the Alaska rows above the California rows (highlighted in the screen shot below).

If you want to clear the filter, click the drop-down arrow on the State column again and choose Clear Filter.

Clear Filter
Clear Filter

You can remove the drop-down arrows the same way you got them: click the Sort and Filter menu and choose Filter, again.

5. Removing Duplicate Rows

If you have a lot of data, especially if it’s imported from somewhere such as a web form, you might have duplicate rows. Excel does a great job of removing the duplicates and allows you to decide how similar rows need to be for them to be considered duplicates.

Example: Use address information to determine which rows are duplicates. Refer to the 'remove duplicates' sheet in the free Excel file for this exercise.

When we scroll down this example file, we see there are three identical rows where the name is Samantha Carter: the data in all the columns are identical, so we know it’s the same person listed three times. There are also two rows where the name is Sharon Valeri, but all the other info is different, so we know these are two people with the same name. 

Select both Sam and Sharon
Select both Sam and Sharon 

Make sure to click somewhere in the data area. Then in the Data tab of the ribbon, click Remove Duplicates.

Remove Duplicates dialog box
Remove Duplicates dialog box

Leave all the checkboxes selected. This way, Excel will consider rows to be duplicates only if all their columns are identical.

Click OK. It tells us that two duplicates were removed, which is what we expected. When we scroll down, we see two of the three Samantha Carter records were removed, and both the Sharon Valeri records remain.

Duplicates removed
Duplicates removed

6. Separating Data in One Column Into Multiple Columns

If you have a column of data that you need to split into multiple columns, use the Text to Columns command. Just be aware that it isn’t perfect because it can’t read your mind, so you might have to do some manual cleanup.

Example: Split a column containing full names into separate columns for first and last names. Refer to the 'splitting' sheet in the free Excel file for this exercise.

Here we have similar data as in previous examples. Note that three people in the list have three names, so we will need two empty columns.

First, insert two blank columns to the right of column A:

Place the mouse pointer on the header of column B, so it becomes a downward-pointing arrowDrag to the right, so columns B and C are selected.

Selecting Excel columns
Selecting Excel columns

Right-click and select Insert from the popup menu. Since you had two columns selected, you’ll now have two blank ones.

Select all the names in column A. (Quick way: click the first one in A3, then press Ctrl + Shift + Down arrow. Then scroll back to the top.)

On the Data tab, select Text to Columns. Choose Delimited, then click Next.

Text to Tabs columns
Text to Tabs columns

Choose Space as the only delimiter. Treating consecutive delimiters as one will be selected by default, so leave that. Scroll down the list to see the names that will span into three columns.

Text to Tabs columns span
Text to Tabs columns span

Click Next, then click FinishColumn A will now have just first names, column B will have last names, and column C will have three names that spilled over. 

Split names
Split names

You can manually edit these three names, then delete column C, which should be empty.

7. Joining Multiple Columns Into a Single Column

Excel does a good job of going the other way, too: if you have multiple columns and want to join them into one. There are a couple of ways of doing this, but I’ll show you the simplest, which is to use a formula. Refer to the 'joining' sheet in the free Excel file for this exercise.

Like you did above, insert a new column after column B. Position the mouse pointer on the header of column C so the mouse pointer becomes a downward pointing arrow

Joining
Insert a new column

…then select Insert from the popup menu. Give the new column a title of Full Name.

Click in cell C3 at the top of the new column to do the first formula. To make it more understandable, we’ll do it with a small error, then fix it.

Since we want to join cell contents, not add numbers, the operator to use is an ampersand. So make the formula:

=A3 & B3

Joining formula
Joining formula

But when you enter it, the first and last names are squished together, without a space in between. So delete it. Rewrite the formula again, but with another item included:

=A3 & " " & B3

Putting a space inside the double quotation marks means the formula should literally include a blank space. 

Joining formula with space
Joining formula with space

Enter it, and AutoFill down to the bottom.

AutoFill down
AutoFill down

8. Quick Formatting of Numbers and Worksheets

Here are some quick ways of formatting numbers (dollars, percent, etc.) and worksheets (colors, fonts). 

Refer to the 'formatting' sheet in the free Excel file for this exercise.

The numbers in this worksheet should have commas to separate thousands, but don’t need decimals. You might want to apply dollar signs to the first row, the Total row and the Total column. Also, the last column is formatted as decimals but should look like percentages.

Select the top row of numbers through the Total column.

Hold down the Ctrl key and select across the Total row at the bottom (the top and bottom rows should now both be selected).

Formatting selection
Formatting selection

On the ribbon, click the Dollar sign button or press Ctrl + Shift + $. That applies dollar signs, commas and decimals. (We’ll remove the decimals in a minute.)

Now select all the numbers in between and click the Comma style button or press Ctrl + Shift + !. That applies commas and decimals.

To remove all the decimals, select all the numbers in the sheet except the Percent column at the end and click the Decrease Decimal button twice.

Formatting decrease decimal
Excel formatting - decrease decimals

Now let’s apply percentages.

Select all the numbers down the % of Total column, then click the % sign or press Ctrl + Shift + %.

If you want, click the Increase Decimal button once or twice.

Applying percentages
Applying percentages

To format the sheet, there are three ways of doing it:

  • Select the row and column headers and individually apply colors, fonts, etc.
  • Select the row and column headers and apply built-in styles.
  • Format the whole sheet as a table and let Excel apply the design.

For speed, here are the second and third methods.

Select all the column headers, then on the ribbon, click Cell Styles. Choose one.

Formatting cell styles
Excel column formatting cell styles

Do the same for the row headers.

Excel header formatting cell styles
Excel header formatting cell styles

Undo a couple of times. 

Now click Format as Table and choose a design.

Click OK in the dialog box that comes up. Notice that the columns have drop-down arrows so you can filter them.

9. Manipulating Columns, Rows, Page Breaks and Worksheets

Changing Row & Column Size

If a column is too narrow, you might have text that’s cut off or numbers that display as pound signs. If a line is too short, you might not be able to read it. Refer to the 'rows & columns' sheet in the free Excel file for this exercise.

Adjusting column width and row height works basically the same way: put the mouse pointer on the right or bottom edge of the row or column so the mouse pointer becomes a 2-headed arrow, then drag until the row or column is the size you want. Or you can double-click to get the row or column sized automatically. (This works the same way in Windows File Explorer in details view.)

Windows File Explorer column width row height
Windows File Explorer column width row height

Manipulating Page Breaks

There are two ways of creating page breaks:

  • Insert them manually, from the Page Layout tab
  • Using Page Break Preview

To insert a page break manually, select the row or column header where you want the page to break, then from the Page Layout tab of the ribbon, click the Breaks drop-down and select Insert Page Break.

Insert page break
Insert page break

To adjust page breaks, use Page Break Preview

Click the Page Break Preview icon in the lower-right corner, then drag the lines. Dotted lines are automatic page breaks, and if you insert page breaks, those lines will be solid.

Adjust page breaks
Adjust page breaks

Adjusting Worksheets

To insert, delete, move or rename worksheets, use the tabs in the lower-left corner of the screen.

This workbook has just one sheet, called Sheet1. To rename it:

  • Double-click the tab. 
  • Type a new name (like N. America), then press Enter.
Working with sheets
Working with sheets

To insert a new, blank sheet, Click the round Plus sign next to the tab.

To duplicate the sheet:

  • Drag the sheet tab to the right until you see a downward-pointing triangle next to it. 
  • Press Ctrl so you see a small plus sign in the mouse pointer. 
Duplicating a sheet
Click the triangle

Release the mouse first. You’ll see a new sheet with the same name and a “(2)” after it.

Your new sheet is created
Your new sheet created

You can drag the sheets into any position you want. Sheets get dropped where you see the downward-pointing triangle.

10. Keeping Column Headers Visible

When you have a long sheet, it’s easy for a column header to scroll out of view, but there’s a feature to prevent that. When you print a long sheet, you want to make sure the column headers appear at the top of each page—and please, don’t do it by copying and pasting. That will become a nightmare.

Refer to the 'headers' sheet in the free Excel file for this exercise.

Keeping Headers Visible When Scrolling

In this sheet, click on cell A3, which is just below the row of column headers. Then in the View tab of the ribbon, click Freeze Panes. And select Freeze Panes from the drop-down. 

Select Freeze Panes
Select Freeze Panes

Now you can scroll up and down the sheet and still see the headers.

You can unfreeze the panes the same way: click Freeze Panes, then select Unfreeze Panes from the drop-down.

However, this has no effect on printing. To print column headers on every page:

  • Go to the Page Layout tab of the ribbon and click Print Titles
  • Click inside the Rows to Repeat at Top box.
  • On the worksheet, select the headers of rows 1 and 2. That puts $1:$2 in the box.
Select the headers
Select the headers

To see that it works, go to Print Preview: File > Print or press Ctrl + P.

Click the page tabs on bottom to scroll through the pages and see the headers.

Scroll through the headers
Scroll through the headers

11. IF Function: Making Cell Value Depend on a Condition

Excel has several functions that can make decisions of what value to display. These are all based on the IF function. Once you’re familiar with it, it will be easier for you to use the more complex ones.

The IF function has three arguments:

  • What is the condition you’re testing.
  • What the cell should display if the condition is true.
  • What the cell should display if the condition is false.

When a function has several arguments, you separate them with commas. So this is the syntax:

=IF(condition to test, what to return if true, what to return if false)

Refer to the 'functions' sheet in the free Excel file for this exercise.

Example: We have a list of orders placed by people in various states. If an order is from New Jersey, apply a 7% sales tax. If an order is from anywhere else, don’t apply tax.

At the top of the Tax column (H3), enter this formula:

=IF(C3="nj", G3 * .07, "")

This means:

  • The condition to test is: does the cell in the State column contain the value “nj”? (Note this is not case sensitive, and because it’s text, it must be in quotes.)
  • If the condition is true, the cell should have a value of Sale * 7%.
  • If the condition is false, the cell should have no value (empty set of quotes). 
IF Funcation results
IF Function results

AutoFill down to the bottom. Most cells in the Tax column will be blank, and those that aren’t have a sales tax because that row shows NJ in the State column, as shown below.

If sheet Autofill

12. SUMIF and AVERAGEIF: Adding and Averaging Depending on a Condition

Similar to the IF function, the SUMIF and AVERAGEIF functions will find the sum and the average of a range of cells where a certain value or condition exists.

The functions get three arguments:

  • What range do we look in to find the condition?
  • What is the condition or value to look for?
  • In the rows where that value exists, from what range do we take the sum or the average?

The syntax of the two functions are similar—different names, but the same arguments:

=SUMIF(range to look in, value to pick, range to add)

=AVERAGEIF(range to look in, value to pick, range to add)

Refer to the 'SUMIF' sheet in the free Excel file for this exercise.

Example: find the total of all orders to California, and the average order of Kona.

Do a SUMIF First

At the bottom of the Sale column, in G203, enter this formula to find the total of orders to California:

=sumif(C3:C201,"ca",G3:G201)

This means:

  • Look down column C.
  • In column C, cherry-pick the rows that contain “ca”.
  • In the same row where we find “ca”, add the value of column G to the total.
Add the value of column G
Add the value of the column

Do an AVERAGEIF

Now use the same technique to find the average of all sales of Kona.

Go into G204 and enter this formula:

=averageif(D3:D201,"kona",G3:G201)

This means:

  • Look down column D.
  • In column D, cherry-pick the rows that contain “kona”.
  • In the same row where we find “kona”, include the value of column G in the average.
Average the column
Average the column

This should be the result:

Final result
Final result

Using These Excel Techniques

Learning how to use Excel is worth the effort. With these techniques and tips, you can put more of Excel's powerful, time-saving features to use in your own spreadsheets. Practice the lessons above, then work on incorporating these techniques into your workflow. 

Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.