7 Basic Excel Hacks to Change Your Life

If you are ready to take your Excel development seriously then take a look at the Ultimate Excel bundle. It’s the best value way to learn Excel with Skillsology and includes four top courses: Excel, Advanced Excel, Business Analysis and PivotTables.

 

The one thing I tell new or casual users of Excel is that it’s not what you don’t know that will hold you back but rather, what you don’t know that you don’t know. Over the course of my career as an analyst, I have picked up numerous simple little tricks that have changed my life.

 

Often times these were accidental discoveries or something a colleague pointed out as they watched me do it the hard way. Whether it is a keyboard shortcut or some other technique I didn’t even know existed, these things have accumulated to enable me to navigate my way around my spreadsheets far more efficiently as time goes on.

 

Below are 7 basic Excel tricks that will change your life. My personal favorites are the ones that enable me to zip around my spreadsheets quickly. Tricks like toggling between workbooks and moving around (or selecting) a range with keyboard shortcuts rather than scrolling with the mouse. They are all simple enough to learn and begin using right away, and they will improve your efficiency immediately.

 

How to toggle between two or more open workbooks

 

There may often be times when you are working with more than one open spreadsheet file or workbook. It can be cumbersome to click the Excel icon at the bottom of your screen in order to find the different workbook you want to activate and click on it.

The more efficient alternative is to hold the Ctrl key down while you press Tab. As long as you hold Ctrl down you can press Tab as many times as you need to get to the open workbook you want.

How to auto sum an entire column or row without typing anything

 

Another keyboard shortcut any Excel user should know is Alt + =. With a cell either below a column range or to the right of a row range of number values selected, this keyboard shortcut will auto sum the adjacent range of values.

 

How to freeze the top row

 

This technique is very handy if you are working with a spreadsheet or table with a header row. It basically keeps the header row as your top row no matter how far down the sheet you scroll. This same thing can be achieved by navigating to the ‘View’ tab on the ribbon and in the ‘Window’ section selecting ‘Freeze Top Row’ from the ‘Freeze Panes’ drop down.

However, with the entire top row of your worksheet selected, you can bypass all these clicks by using Ctrl + Shift + L. Save your mouse, use the keyboard.

 

How to use Text to Columns to separate strings of data

 

In cases where you have a column of text string data that you need to be separated based on a delimiter like a comma or even a space, Text-to-Columns is a built in tool in Excel to help automate that. Let’s consider a case where we have a text string that we would like to separate each word into its own cell.

excel-pic-1

If we select cell B2 and select ‘Text to Columns’ from the ‘Data Tools’ section of the ‘Data’ tab, the Convert Text to Columns Wizard should appear.

excel-pic-2

Select the ‘Delimited’ radio button and click ‘Next’. On the Step 2 window of the wizard, uncheck ‘Tab’ in the ‘Delimiters’ section and check ‘Space’ since our substrings are separated (delimited) by spaces. Note the ‘Data preview’.

excel-pic-3

Click ‘Next’ and note the option to change column data format and destination range (cell). We can leave the selections as their default options.

excel-pic-4

Click ‘Finish’ and now all the text substrings of our original string are separated into their own columns beginning with B2.

excel-pic-5

How to combine or concatenate cell contents

 

In timely contrast to the previous hack on this list, one of the most life changing tricks any Excel user should know how to do is combine contents of more than one cell into one. This trick also works with literal string values. The easiest way to do this is to use the ampersand, ‘&’. When used between cell references or literal values, the ampersand “joins” them together.  excel-pic-6

 

For each solution, we must begin with an equal sign since this is essentially a formula. Note that we are using the separated text from the previous hack. Then we have four different examples of using the ampersand to join text.

 

In Example 1 on row 3, we have joined each cell reference containing the string text we want to join. However, notice that the final solution is a long string with no spaces. We must add the spaces just like they are string values. See Example 2 on row 4.
For Examples 3 and 4, we have done the same thing but using literal strings instead of cell references. This is to illustrate that you can use this technique with one or the other as well as in any combination of both.

 

How to use the Format Painter

 

If you’d like to copy just the formatting of one cell to another cell or range of cells, select the cell you want to copy the formatting from and then click on ‘Format Painter’ in the ‘Clipboard’ section on the ‘Home’ tab.

excel-pic-7

Then select the cell or range of cells you would like to apply the formatting to and you are done.

excel-pic-8

In our illustration, this method allows you to center the value in the cell, change it to bold font, and change the background fill color to green in one simple technique. We’ve seen countless people grinning from ear to ear on finding this shortcut. Once you use it for the first time, you’ll be smiling, too.

 

How to navigate through your worksheets more efficiently

 

Have you ever endured the pain of scrolling down or across in a worksheet for what seemed like hours to get where you wanted to go? Stop the madness!
Use keyboard shortcuts to get where you want to go faster. The first thing to learn is to navigate to the end (or beginning, depending on the circumstance). This is as simple as pressing the Ctrl key and then pressing the appropriate arrow key for your situation at the same time.

 

To go a step further, you can select an entire range by using this same technique if you also press the Shift key while pressing Ctrl. Note that this applies to contiguous ranges of data. Any cell that is blank will ‘block’ you from navigating further. However, you can continue past any blank cell by pressing the arrow key again if you don’t let up on the Ctrl (and Shift) key.
A bonus tip while on the idea of navigating using this technique is for navigating through multiple worksheets in a workbook. It is just the same as navigating ranges in a worksheet but instead of using arrow keys, the Page Up and Page Down keys will scroll backwards and forwards, respectively, through multiple worksheets in a workbook.

 

Final thoughts

 

There are many more very basic tricks that you can learn to use in Excel to be a more efficient user. This short list of 7 is just the start. Hopefully, the simplicity of these tips serves to enhance their impact and encourage further discovery into the long list of other tricks that can change your life.

If you are ready to take your Excel development seriously then take a look at the Ultimate Excel bundle. It’s the best value way to learn Excel with Skillsology and includes four top courses: Excel, Advanced Excel, Business Analysis and PivotTables.

[/vc_column_text][/vc_column][/vc_row]