Excel Tips and Tricks: Use Excel like a Pro

Excel Tips and TricksMicrosoft Excel is perhaps the greatest spreadsheet software that has ever been designed. Excel first truly appeared on the scene way back in 1987, when a version was released for Windows. Excel is a complex and powerful software today, and it gives you a great degree of control over the data in your spreadsheet. Yet, it’s easy for even beginners to learn Excel.

It will be worth your while to explore even the advanced features in Excel. Your work performance will automatically improve, and you’ll free up some time for yourself too. In this article, we’ll show you some tips and tricks to use Excel like a Pro and really cut down on your work time. We’ll be using Excel 2010 – but most of these tricks will work on Excel 2007 and Excel 2013 as well. If you’re a Mac user, most of the tricks given here should work for you too.

Selecting an Entire Row Quickly

If you want to select the contents of an entire row quickly, click on any cell in the row and then press “shift” and “spacebar”. This will highlight the contents of the entire row. This is, of course, much quicker than using your Mouse to drag on cells along the row. Take a look at the image to see how a selected row looks like:

excel screenshot 1

Moving the Contents of a Row or Column to another Location with Just Your Mouse

You can use the mouse to quickly move the contents of a row or a column to another location in your spreadsheet. To do that, first highlight the cells of the row/column you want to move. Then, click on the black border along the outer edge of the cells (your Mouse pointer should look like a plus sign with arrows at the tips) and drag to the right or left. You will see an outline of the cells that you’re moving. Just drop the outline to the location of your choice. You can see the result in the image below:

excel screenshot 2

Quickly Select the Contents of an Entire Spreadsheet

You can use the Extend functionality to quickly select the contents of an entire spreadsheet. First, click on the starting row of your spreadsheet. Then press F8 to activate extend. Then click on the final cell in your spreadsheet. Finally, hit F8 again to cancel. The extend command is very useful if you want to select several cells in a row or in a column too. You can see the result in the screenshot given below:

excel screenshot 3

Finding the Sum of an Entire Column

Excel can quickly calculate the sum of an entire column. Instead of manually entering the formula, or selecting the entire column of cells you need to add, you can just use the keyboard shortcut “alt” and “=”. You just have to select the first empty cell in your column – the one at the end of all the numbers. Then just hit “alt”+”=”.  After that, hit the “Tab” key to make Excel accept the formula. The result will look something like this:

excel screenshot 4

Keeping Headings Visible when You Scroll Your Excel Sheet

If you work with lengthy, complicated excel sheets that you have to scroll down, this trick will help you a lot. You can keep the column headings visible when you scroll below their range. For example, in a normal spreadsheet, if you scroll to row 39, your column headings will disappear. But if you wanted to keep them visible, you can freeze them in place with the freeze panes functionality. You can even freeze any row, or a collection of rows, you want to stay visible as you scroll down your spreadsheet.

To activate the freeze functionality, select a cell in immediately below the row(s) that you want to be frozen. Then, click on view and then click on the freeze panes option (it should be somewhere in the middle). In our image, we have frozen the first five rows of the spreadsheet while we have scrolled down to row 84. You can see that rows 1 to 5 are still visible. After you are done working, you will have to unfreeze the frozen panes:

excel screenshot 5

Quickly Checking Which Formula has been applied to a Cell

Troubleshooting formulae in Excel spreadsheets is something that most of us spend a lot of time doing. To quickly see what formula has been applied to a cell, select that cell and then hit “ctrl” and “`”. This is the acute accent key that is found next to the number 1 on your keyboard (above which the “~” key is present). The result will be something like this (our spreadsheet has lots of formulae applied on the cells). To get out of viewing the formulae, just type hit the same keys again:

excel screenshot 6

Adjusting the Width of a Column/ Multiple Columns

Sometimes a column has data you can’t see properly- that is, the data stretches outside the width of the column and is hidden. In this case, you can quickly adjust the width of your column (or multiple columns, with your mouse. To do that, first select the column you want. Then, take the pointer to the edge of the column until the plus sign with the arrows appears. Drag to the right, until you get your desired width. If you want to expand multiple columns, first select a column, then drag to the right until all your columns are selected. Then go the edge of the farthest column, until the pointer with the cross appears, and then expand to the right. The result will appear something like this:

excel screenshot 7

Quickly Insert Current Date and Time

You can quickly insert the current date and time with a couple of keyboard shortcuts. This feature is really handy, and something that you’ll probably be using a lot. To insert the current date, hit the “ctrl” and “;” key. That’s the semicolon key. The date will be inserted in the DD-MM-YYYY format. To insert the current time, you need to press “ctrl”, “;” and “shift” at the same time. The time will be displayed in the HH:MM format (with am and pm). Check out the screenshot to see what it looks like:

excel screenshot 8

To Hide and Unhide Columns/Rows

You can get Excel to hide and unhide columns (and rows). Why on earth would you want to do that? If you work with large spreadsheets, you’ll know the information present can be mindboggling. Sometimes there are so many numbers and words, they just doesn’t make any sense.You can just hide the columns, with the data you don’t need at the moment, and concentrate the columns that you do need.

To hide a single column, select any cell in the column and then press “ctrl” and “0”. If you want to hide a single row, select any cell in that row and then press “ctrl” and “9”. If you want to unhide a row (rows) that you have hidden before, select the range and press “ctrl”, “shift” and “9”. If you want to unhide a column (columns) that you have hidden before, select the range and press “ctrl”, “shift” and “0”. In the image below, we have hidden the column C. If you look closely, there is a light coloring between columns B and D (in between the letters), which indicates a column has been hidden between them:

excel screenshot 9

Quickly Getting Excel to Finish a Pattern you started

Suppose you wanted to quickly write down the names of the months in your column. Instead of typing out all twelve of them, you can just type the first two and get Excel to complete your pattern for you. Excel’s ability to finish the pattern you started isn’t limited to just months, or days of the week. Excel can recognize the pattern in your numbers and replicate it!For example, if you wanted to fill out all the even numbers between 1 and 100 in your column, you don’t have to type them by hand- just type out the first few entries and Excel will do the rest for you.

First, write down the first few entries. Let’s say we want to enter all the even numbers between 1 and 100. So, enter 2, 4 and 6 in three simultaneous cells in a column. Then, select the first cell, drag and drop on the third cell, until all three cells are highlighted. Finally, take your pointer to the bottom-right corner of your selected cells and, when it turns into a black plus sign, drag and drop downwards (for 50 rows, in our example). You can see the result in the image below:

excel screenshot 10

Quickly Formatting Multiple Cells

Suppose you had a cell that you had made attractive – changed the font of the data or changed the background color of the cell, for example. Now, you want to apply exactly the same formatting to the other cells in your worksheet. So how do you do that quickly, without reapplying the formatting each and every time? You can use the Format Painter button, of course!The Format Painter button is a very handy button that you’ll find in other Microsoft software offerings too (like MS Office and PowerPoint).

To use the Format Painter button, first select the cell you’ve formatted. Then double click on the Format Painter button. You will find it in the Home tab (right under the Home heading). The pointer will change in response. Now just click on the cells you want to apply the formatting to and you’re done. In our image, we’ve changed the cell background to a light green, changed the text color and then copied the formatting to two other cells:

excel screenshot 11

Quickly Delete a Column

You probably spend a lot of time deleting columns in Excel. If you’re new to Excel, you probably will get used to deleting columns! You can delete a column (or a row) by right clicking on a cell in the column and then choosing the delete option. However, this can get really tedious if you have a lot of columns to delete. You can use the keyboard shortcut “ctrl” and “-“ to delete a column you have selected. You can also quickly add a column by pressing “ctrl”, “shift” and “=”.

Of course, it’s easier to use right click and delete if you want to delete multiple columns (or rows). If you want to delete more than 10 rows (or columns), you can first delete 10 rows and then use the F4 key to repeat your previous action. If you want to delete 50 rows, you can press F4 five times.

Macros and Excel

Macros are one of the most important features in Excel. You can use Macros to perform the same task again and again. Programming a macro is easy. You can find Macros under the view option in Excel. Click on record macro. Then put in the name of the macro and assign it a shortcut key, if you want. Then perform the necessary steps (typing data, for example) and then click on stop recording. That’s all there is to it. You can define macros that work with the workbook you’re currently on, or you can make macros that are present for all workbooks that you have currently open.

If you have experience with programming, you can use Visual Basic for Applications to create macros. It can be a little complex, and you might need to learn the basics of programming before you can do it for yourself. Alternatively, you can also download macros that other users have made. You will find these macros on the internet – just search “macros for excel” on the internet. Downloading premade macros will save you a lot of time in the long run!

Whether you’re learning Excel for personal use, or you want to use Excel for your business, you can take our fast track course to learning Excel. We do hope the tips and tricks we’ve walked you through give you a good feel of Excel best practices and help you on your way to becoming an expert in Excel.