• 5-minute read
• 20th December 2019

7 Simple Tips for Using Excel Like a Pro

Businesses everywhere use Microsoft’s Excel. However, this software can seem intimidating if you’re new to spreadsheets. So, to make life simpler, we’ve come up with seven tips and tools that even beginners can use!

1. Show Formulas in Excel

If you’re working on a spreadsheet someone else created, it can be hard to see where a cell contains a formula compared to just words or figures.

However, you can go to Formulas > Formula Auditing > Show Formulas on the ribbon. This will instantly show all formulas in the spreadsheet for review.

2. Freeze Rows and Columns

When working with a large dataset, you may find that you lose sight of column and row headers as you scroll further down.

However, Excel offers a way to avoid this problem:

• Select the row or column you want to keep visible
• Go to View > Window > Freeze Panes
• Click Freeze Panes to freeze the selected row or column

You can then click Unfreeze Panes to remove this formatting.

3. Insert Multiple Rows or Columns

To insert more than one row or column at once, you need to:

• Place the cursor where you would like to insert columns
• Select the number of rows/columns you want to insert
• Right click and select Insert from the menu

This will add the same number of rows/columns in the selected place. For instance, if you wanted to add three new columns between the existing columns A and B in a spreadsheet, you would:

• Select columns B, C, and D
• Right click and select Insert

This would create three blank columns in their place. And the columns previously labelled B, C, and D would then shift three spaces to the right and become columns E, F, and G instead.

4. Row and Column Selection Shortcuts

If you click a column letter or row number, you will select the entire column or row. But you can get more control over the cells selected as follows:

• Click a cell in the row or column you want to select
• Hold down Ctrl + Shift
• Use the arrow keys to select the data in that direction

This will select all cells in the selected direction within the row/column until it hits a blank cell. So, for instance, if you had 20 rows of data in a worksheet but you only wanted to select the last 15 entries in column A, you could click in A6, hold down Ctrl + Shift, then hit the down arrow.

5. The Format Painter in Excel

Formatting in Excel can be a fiddly process, especially if you’re storing different types of data in different cells. But the format painter tool can save you a lot of work on this count.

It allows you to quickly copy and paste formatting between cells. Simply:

• Click the cell with the formatting you want
• Go to Home > Clipboard > Format Painter on the ribbon
• Select the cell or cells that you want to format

This does not change the data in the target cell, only the formatting. So, for instance, if you format one cell to use a specific date format, you could then copy and paste this formatting to other cells or a range of cells.

6. Fill Out Data Patterns in an Instant

Need to fill out a sequence of values in a spreadsheet? Or apply the same formula in all the cells in a row or column? Then let Excel do the hard work.

For example, to number cells sequentially from 1-10, all you need to do is:

• Type “1” in the first cell of the sequence
• Click the little square dot in the corner of the cell
• Drag the selection down to cover the next nine cells

If this simply copies the “1” in each cell, click “Autofill” and select “Fill Series.”

You can also do this for more complicated patterns. For instance, to fill a column with every third day in March, you could do the following:

• Type the first date (e.g., 3/1/2019) in the first cell
• Type the second date (e.g., 3/3/2019) in the second cell
• Select both cells and click the little square dot
• Drag the selection down to fill out the following cells

All you need to do is add the first two entries, then Excel will do the rest.

7. Entering the Same Data in Multiple Cells

How about if you want to add the same data to multiple cells?

If the cells are sequential, you can use the same trick as above (but selecting the “Copy Cells” option in the Autofill menu).

But you can also enter the same data in non-sequential cells. To do this:

• Click the first cell to which you want to add data
• Hold down Ctrl and select the other cells
• Enter the data into the final cell you select
• Hold down Ctrl and hit Enter

The information you typed will then appear in all the selected cells.