Microsoft’s spreadsheet software Excel is one of the most widely used applications in the world.
It has always been innovative and was one of the first applications to use a toolbar. However, to many people, it can seem difficult to use with a high bar to entry.
If you are looking for some Excel tips and tricks to improve your spreadsheet game, then take a look at our quick guide. Find some of the hidden secrets on how to use excel and make it the powerful tool that you need.
Fast Navigation
When you are using Excel, navigating your spreadsheet quickly is extremely useful. Especially so when you have a large, complicated spreadsheet that extends over multiple columns and rows.
One of the best ways to do this is with keyboard shortcuts. Shortcuts will save you the time of moving your mouse to select cells and dragging scroll bars. You can learn countless shortcuts, but here are some of the best for fast navigation around your Excel spreadsheet.
CRTL + Tab will move you quickly between open Excel workbooks.
CRTL + PG UP (Page Up) will move you to the next worksheet inside an active workbook.
CRTL + PG DN (Page Down) will move you back to the previous worksheet inside a workbook.
CRTL + ARROW key will move you to the furthest point of data in that direction. Either up, down, left, or right.
CRTL + HOME moves you to the top-left cell in the worksheet, usually A1.
CRTL + END moves you to the bottom-right cell in the worksheet. This could be anything depending on the capacity of your workbook.
ALT + PG UP/DOWN moves you back and forward one-page length right or left through the worksheet.
Using these shortcuts will make navigating your data much quicker and easier than endless scrolling through rows and rows of data.
Transposing Rows & Columns
Accidentally put your data into a column, and you now want it to be in a row? No problem. Excel has a function for transposing data from one direction to another easily.
It is as easy as cutting and pasting. Select the data that you want to transform from a row to a column and choose Edit > Copy to make a duplicate or Edit > Cut to move it permanently.
Then choose the cell location where you want to put the new data and choose Edit Paste Special > Past Transposed. And that’s it. Excel will transpose the data for you quickly and easily.
Inserting Bullet Points
If you want to add bullet points to a cell, you will need to use Custom Formatting to achieve it. It is simple but requires a little set-up first.
Choose the cell where you would like the first bullet point to start. Then press CRTL + 1 to enter the ‘Format Cell’ dialog box. Here you will need to choose the Custom option under Number. Into the input bar, you can type what you want to appear.
Click OK. Now when you type into the cell, a bullet point will appear before it. Hit enter and a second bullet point will appear in the following cell as well.
Skip the Formulas
Sometimes you may not want to spend time writing out formulas in full. If so, use this quick little trick to save the effort of writing out simple calculations. This is one of the great tips for using Excel effectively.
Select the cell that you want to Add, Subtract, Multiply or Divide by another cell. Press CRTL + C to copy the number.
Then click on the second cell (the second number in the calculation) and choose Edit > Past Special. In the dialog box under operation choose the calculation type you want to perform.
Once you press okay, the number in the second cell will change to the result of the calculation. Simple!
Highlight Blank Cells
If you have a large spreadsheet of data where certain cells have been left blank, you may want to highlight just these cells so that you can change the formatting.
You could spend time trawling through your spreadsheet, holding down CRTL, and doing a multi-select on each cell manually. Or, you could save yourself time and effort by using the special function that does exactly that for you!
Select your data set with either the keyboard or the mouse so that it is highlighted. Then go to Home > Editing > Find and Select > Go to Special. In the dialog box that appears, choose Blanks and click OK.
This will highlight all the blanks in the workbook, and you can now apply a format color to all of them at once.
Combine Worksheets
Sometimes you may want to combine two documents together, especially worksheets that may share the same dataset. Luckily once again, Excel is one step ahead of us and has created the Consolidate function. With this, you can bring all your worksheets together easily.
Add a new worksheet to your workbook and click Data Tab > Data Tools > Consolidate. In the following window, choose the sheets you want to add to the workbook. You can browse and even combine the data.
Once you have chosen what you need, click OK, and Excel will add the data to the new worksheet.
If you want to combine other types of documents outside of Excel, you may want to read about how to combine two pdfs as this is a useful skill as well.
Remove Duplicates
Another quick and easy data cleansing tip is the Remove Duplicates tool. Often when working with large datasets, we can generate a lot of duplicate data across the worksheet.
With the Remove Duplicates tool, you can quickly cleanse this unwanted data from your spreadsheet, leaving it fresh and clean and ready to use.
Select the data you want to cleanse. Choose Data > Data Tools > Remove Duplicates. A window will appear with all the duplicate information. You can choose what you want to remove. Click OK, and that’s it. All gone.
Helpful Excel Tips and Tricks
Hopefully, this quick guide has given you some useful excel tips and tricks that you can use to save time and show off your Excel wizardry. Excel is a potent tool, so keep learning and discover more ways you can master its strengths.
If you found this article useful, then please consider reading another blog on our site.