8 Tips To Make You An Excel Expert

Make sure you're working productively in Excel, save time, and impress your team with these simple Excel tricks:

How do I become an expert in Excel?

Whether you want to develop a new skill for work or get ahead in your financial career, Excel is the perfect software to master. Pivot tables, Filters, Charts, and Conditional Formatting are the most common advanced skills. But if you are a beginner, you should take a look at these eight simple Excel tricks. These tips will help you impress your team, increase your productivity in Excel and save a lot of time.

What are the basic Excel skills?

Select all cells in your spreadsheet

This may come in handy if you need to reformat your workbook. Simply click on the square in the upper left-hand corner. Every single cell - even the empty ones - will now be highlighted. If you prefer keyboard shortcuts, holding down Ctrl + A will do the same thing.

Copy an Excel sheet from one workbook to another

It sounds like a complicated job, but it's a lot easier than you think.

Make sure the workbook you would like your worksheet(s) to be moved to is open, select the worksheet(s) you want to move or copy.

Click Home > Cells > Format. Under Organise Sheets click Move or Copy Sheet

In the Before sheet section of the dialogue box that has appeared on your page, click one of the following: 

1. The sheet before the area you want to insert your moved/copied sheets. 
2. Move to the end to insert your moved/copied sheets after the last sheet in the workbook and before the Insert Worksheet tab.

If you want to copy rather than move your worksheet(s), select the Create a copy > check box in the Move or copy dialogue box.

Quickly insert multiple columns/rows

Tables are constantly changing and it just so happens that today you need to insert 3 blank rows between Row 7 and Row 8. Rather than doing them one by one, try out this handy shortcut:

1. Select the 3 rows below Row 8 - including Row 8.
2. Right-click the selected rows and click Insert.

Filter data easily

If your Excel worksheet contains a lot of data it can be hard to find the information you want quickly. Filters are a great way of sorting through to display only the information you need. In order for this to work, you must ensure that you include a header row to identify the name of each of your columns.

1. Go to Data > Filter to give each column its own clickable dropdown menu.
2. Click on one of the drop-down menus to sort the data in a variety of ways. You can choose values or names.
3. Unclick, then Select All, and choose the filter option you want to view.
4. Once you click Ok the dropdown menu will disappear and show you just the data you have selected from your filter options. The other data has not been deleted, it is hidden. 
5. To undo the filter and see all the other data, go to Filter > Select All.

Apply formula to an entire column/rows

Thankfully there is a nifty Excel trick to apply a formula to an entire column/row. If your formula is in B2, click on that cell and then click on the little box that appears in the bottom right-hand corner, dragging it across your desired range of cells. The formula will now populate the range of cells.

Move data from a row to a column and vice-versa

Picture this, you've put all of your data in a series of rows, only to realise that you were actually supposed to put it in columns. Fear not, this switch is simple.

1. Select the data that you would like to reposition - including any headers or labels - and copy it. To make it faster use an Excel shortcut Ctrl+C.
2. Right-click on the cell that you would like to place the data, click Paste Special > Transpose
3. Your data is now flipped. You can now delete your original data.

Continue a series with one click

Why enter data manually when your worksheet can do it for you? An Excel trick called AutoFill will fill your cells with data that follows a pattern.

1. Click on the first cell with the data you would like to continue.
2. Enter a value in the next cell to establish a pattern. For example, if you want the pattern to be 4, 8, 12, 16, then type 4 and 8.
3. Click back on the cell with the starting value and then click on the little box that appears in the bottom right-hand corner. Drag it across the range you would like to fill.

Highlight a group of numbers to see the average and total

This is most probably the easiest and quickest of all of the tips listed above. This will help you, especially if you work with figures and finances.

1. Highlight the group of data that you would like an average for.
2. Look at the box in the lower right-hand corner of the screen to see some basic information, such as the number of cells that are highlighted, the total number of the cells, and the average of the cells' values. Simple.

How long does it take to become an expert in excel?

These simple Excel tricks are just the beginning of your journey to becoming an Excel expert. With a bit of practice, expert advice, and taking one of our short Excel training courses you'll be mastering the spreadsheets in no time.



by Cristina Moraru | 27 Sep 23