In this ebook, we take a look at over 20 different tips and tricks that are not normally covered in Excel training.
This is a collection of some of my favourite Excel tips, tricks, and best practices. It will focus on common scenarios and reflects some of the most common questions I get asked.
Excel is my favourite core Office application, and I have been a Microsoft Certified Trainer (MCT) for over ten years now. I’m also a Microsoft Office Specialist Master (MOSM) and hold several other certifications.
If you would like to follow the examples I use in this ebook, you can download my excel file from here: https://s3.amazonaws.com/media.collab365.community/Summits/Excel+and+Power+BI+Summit+2019/Sharon+Fry+example+spreadsheet.xlsx
I hope you enjoy following along and gain some valuable time-saving tips.
Enter a value or formula in multiple cells at once
In this example, I have a simple payroll sheet. Individual hours from time cards have already been entered for the people that use time cards and now everyone else should have a value of 40 hours entered.
To do these all at once, click on the first cell for Andrew’s hours then hold the control key while you click on all of the other blank cells in the Hours column. All of the cells you click on whilst holding down the control key should be highlighted as shown below.
In earlier versions of Excel, if you accidentally selected something that you didn’t want to select, you’d have to start all over again. In the latest version of Excel, you can continue holding Ctrl and click that mistaken selection to deselect it.
When you have all the relevant cells selected, type 40 (which will show in the last selected cell), continue to hold the Ctrl key while pressing Enter, and value 40 should now be entered in all selected cells.
Other ways to hide text
Typically, I prefer not to hide rows and columns because you run the risk of forgetting they are there. You also may end up having them in your way or not realizing something’s happening because it’s in a hidden row or column.
An alternative option to hide information is to amend the color of the text to match the background. For example, cell E3 contains the text “and more!”; however, this is not visible on the actual worksheet as the font color is white.
So now, if I change it to the theme color to match what’s in A3, the text will be visibly displayed.
Complete Entries without moving to a different cell
The next thing we’re going to do is calculate the amount of pay for each employee, this is a simple calculation of the number of hours multiplied by the hourly rate. We’ll start, of course, with our equals sign and then click on our number of hours, type our asterisk for multiplying, and then click on the rate.
Normally when you finish a formula or any other kind of entry in a cell, you move to another cell. Enter Tab, and you will move to the right; hit Enter and you move down.
If you don’t want to move and instead stay on this cell, hold Ctrl while pressing Enter to complete the formula and enter the result without moving.
Fill Cells in a Column without dragging the Autofill handle
We now need to use this formula again for the rest of the employees. One way is to click and drag the bottom right corner of the cell to the rest of the column. However, if you have a set of data that has thousands of rows, that could be a pretty long drag.
When your data is arranged in an obvious manner so that Excel can tell how far the data should go down, all you need to do is double-click on that AutoFill handle (the cursor needs to change into a solid black cross as shown below).
The problem now is that it AutoFilled down into our total cell (use Control + Z to undo.)
Best Practice Tip 1: Use Blank Rows and Columns around your data set
I’ve found it to be a best practice especially now Excel has increased its ability for auto-detecting ranges to keep blank rows and columns around my sets of data. So I would consider the data for my nine employees plus the title row to be a set of data. The total is not part of the data. So what I would do here is insert a blank row above my total (select the row that contains the Total, right-click, and choose Insert.)
Once you’ve inserted rows or columns, you will see a pop-up button called Insert Options, which allows you to format the inserted rows the same as the ones above, the same as the ones below, or clear any formatting from that row. Select the clear formatting option.
If you don’t like to see all of the space between the data and the total, adjust the row height to make it smaller.
Now when you double click to copy the formula down, it stops at the blank row and does not copy the formula into the total.
Additional benefits with Auto-Detect
By adding a row (and clearing any formatting from it), if you select any cell within the data set and use the Ctrl+A shortcut (to “select all”), it can auto-detect my entire range of data.
If you then want to sort or turn on the AutoFilter to the data set, Excel already knows what to include. If you use the Ctrl+A shortcut a second time, it will extend the selection to the entire worksheet.
If you are not in a dataset when using Ctrl+A, Excel will automatically select the entire worksheet. That is the same as clicking that little triangle that’s right above Row 1 and just to the left of column A in the top left corner of your worksheet.
Best Practice Tip 2: Make a note of any complex formula
If you’ve worked hard and struggled to get a formula to be exactly the way you need it to be, it’s a good idea to make a backup by copying the guts of the formula (i.e., the actual calculation not the cell value) as a note into the column title to keep it safe.
To do this, right-click and copy the entire formula, then press Escape to exit the cell’s Edit mode without breaking anything. Then in the label of the column, in this case, “Amount” in cell F8, right-click and from the drop-down menu, you will notice two options, New Comment and New Note.
The Comment feature has been available in Excel for quite some time. In Excel 2019 and Office 365, it has now shifted and is now called Note. In a cell, you can either use a Note or a Comment.
When creating a new Note, a text box opens into which you can paste the formula, and a red triangle is displayed in the top right corner of the cell.
So this means the details of your formula are now stored in a little backup place. As you continue to work on the rest of your spreadsheet, the note is not displayed, nor does it need to be included in any print you may make. It’s just there for safekeeping.
If you choose Comment, you will see that you have the ability to @mention people as well as write a bespoke comment. Comments are highlighted with a purple flag.
Any comments are time-stamped with the user name also being recorded. It is also possible for other users to reply, comments can be deleted or edited and finally, you can resolve or delete the entire thread.
You can find both the Notes and the Comments options in the Review tab of the ribbon.
Typing the name of a known function
If you know the name of the formula or function you want to use, you can start typing equals (=) for the formula and then the name of the function that you want to use directly into the relevant cell.
Excel will provide a list of all possible options that narrow down with each letter of the function you enter. When you get to the one you want hit “Tab” to select it.
To create the sum, you need to select the range of cells that needs to be summed, close my parentheses and Ctrl+Enter. SUM is probably the most frequently used function, so it has its own button and a keyboard shortcut of Alt+= . Excel will shortcut the whole process and even guess what you want summed.
If you’re not familiar with the function names, you can review the options in the Formulas tab or going through the Function Library or clicking the fx button.
Easily make an exact copy of a worksheet
Now that we’ve got this week’s payroll done for the week ending 12/15, we want to make a copy of this sheet to prepare it for next week’s payroll. You may already be familiar with right-clicking on the spreadsheet tab and selecting Move or Copy.
The easy way to create a completely duplicated copy of a worksheet is to start by clicking and dragging on the sheet tab until the little black triangle at the top of the tab moves from the left side to the right side and the mouse pointer has a piece of paper attached to it.
By holding the Ctrl key, a little plus sign is displayed. Let go of the mouse button whilst still holding the control key and Excel will create a copy of that sheet. Rename your new sheet.
Sheet actions are not available in your Undo stack, so if you make a mistake double-click on the tab name and correct the name.
Using a text box in a spreadsheet
So you may have been wondering how I have included the bulleted list of tips in my spreadsheet.
It’s not text entered in the cell; it is actually a text box. When it is selected, up in my ribbon I have the Shape Format tab, and this gives me greater control over what I call bulk text.
Using merged cells
You can achieve the same effect by adding text into cells using the “merge and center” option and aligning the text to the left. Individual formatting within the text is still possible, so you can use bold and italic styling for example. The bullets are not built into Excel they need to be typed with a symbol.
Excel has gotten better at allowing you to edit and format text within a cell which can be amended in the formula bar. Unfortunately, the formatting is not displayed in the formula bar though.
To add multiple lines, create a line break by holding Alt and pressing Enter.
By selecting a word, a Mini Toolbar is provided, giving basic formatting options with any changes being made displayed in the original cell.
The spacing is actually greater between the lines than it is in my text box. You also don’t have all the formatting options that you would have in a text box.
Advantages of using a text box
Creating the same text within a Text box provides many more options; for example, you can align different elements of the text differently and also change the shape format.
Using a text box also allows you to use the WordArt styles. So if I wanted to make this title even fancier, we could do that too.
Using a Text box, or by inserting a shape that you fill with text means you can take advantage of all the different shape format options.
Select a text box, or shape from the Shape menu and simply drag and draw it. Once you’ve drawn it, you can see the Shape Format tab in the ribbon and can select background fills, colors, texture, outlines etc.
You also have complete control over the positioning of the shape whereas when you’ve got merged and centered cells, or merged and centered and left-aligned cells, you are constrained to positioning these with the rows and columns.
A disadvantage of using text boxes is that the inserted text may not be readable by screen readers as it can fall outside the shape, but this won’t happen in a cell.
An alternative to Merge and Centre
Rather than use merge and center to display text, which is ok for titles but can cause problems in a data set or if you want to copy and paste it at a later stage, we have an alternative option in the Alignment dialog box.
Select all of the cells that you want your title to display across, then under the Horizontal alignment option, choose Center Across Selection, and click OK.
The appearance will be exactly like Merge & Center with the text remaining in cell A3.
If you make a column much wider the text will remain centered in that space, but every cell can be accessed separately.
Hiding Cell Gridlines
If you want to remove or hide the cell borders you can set the color of them to white (or whatever color you need to match the cell backgrounds). From the “Borders” button, select “More Borders,” and then choose the relevant color.
To change them back to the default style, choose “None” for the style, then “Inside” and click OK.
Best Practice for Inserting a New Row
One option for entering a new row is to right-click on the row below and select insert new row; however this does not include any relevant formulas or consistent formatting.
To ensure that the new row is included in the total hours worked sum, you need to insert the new row between the endpoints of the formula (between F8 and F16 in this case.)
So my best practice here is to copy the last row and then right-click on it again and choose “Insert Copied Cells”.
This will insert an identical row as shown below.
The new row’s data can then be updated to reflect the new employee. This can be done manually, or to ensure that you don’t forget to update any of the fields, you can find the constant fields.
Select the data in the new row, and from the “Find & Select” option in the Editing group choose “Constants.”
This will select all of the fixed data fields which are highlighted as below.
Then hit Delete, and these constant fields will be cleared, leaving any calculations in place.
You can then add the relevant details for the next employee, and the calculations will automatically update as the new data is entered.
Tips for Working with Bigger Data
Before you can produce anything meaningful from a data source, there are several things you need to do to format it and clean it up. Here are some tips to help.
Often data has no formatting, no column widths, nothing. It’s just been brought in what I call plain or raw text.
Freezing the panes helps by maintaining column and or row headings and descriptions when scrolling through the data. The options are available in the View tab, “Freeze Panes.”
There are 3 options, none of which can be combined together,
- freeze the first column: this allows you always to view column A when scrolling horizontally across the worksheet.
- freeze the top row: this always displays row 1 when scrolling vertically down the spreadsheet
- The freeze panes option keeps all rows and columns visible above and to the left of the cell you have selected. If you select C6 and freeze panes, everything from row 5 and above and columns A and B will remain visible no matter where you scroll to within the spreadsheet.
Format header row
To format the header row we firstly need to select all of the column headings. Click on the first cell in the row, A5 in this instance, then hold Ctrl and Shift together and press the right arrow key on my keyboard. This will jump to the far end of the row headings and select all of the cells in it.
To explain how this works
If you select one cell and use the arrow keys, it moves one cell at a time. If you hold Ctrl when you press the arrow key, it jumps from data to data. In other words, any blank cells will be jumped over. When you hold Shift when using the arrow keys, you extending or retracting the selected area. So that’s why clicking on the first cell, press and hold Ctrl and Shift together, and then the right arrow will then jump to the other end of my header row.
To quickly format these selected cells, from the “Home” tab, “Cell Styles” area, and select a Heading style. You can also create your own styles using the “New Cell Style” option.
Create an index field
Whenever I receive data that doesn’t contain an obvious sequence (not in alphabetical order, numerical or date order for example), I create another column called OrigSort as a reference column in case the data needs to be put back into its original order. This can be styled in line with the rest of the heading row by using the format painter option to copy all styling.
To select the first row, I will usually click on the header and press my down arrow once just to make sure I’ve not accidentally scrolled down halfway down the list and then I’m not starting at the top. So I’ve made a habit of clicking on the column label, pressing my down arrow once, now I know I’m in the first row of the data. Type 1, and AutoFill by double-clicking.
I want a list of sequential values to give me a row order and currently, the AutoFill has input the number 1 in all rows. At the end of the visible area of AutoFill, you will notice a little button called “AutoFill Options.”
If you click on the down arrow and select Fill Series.
The values will be changed into sequential numbers. This can also be achieved by holding Ctrl while you drag the AutoFill handle down the data list (it doesn’t work with a double click option though.)
AutoFit the Column Widths
It’s annoying that I cannot see all of the values in the columns, so it would be helpful to expand the width of the columns. There are several options for expanding the width of columns; the quickest is to place your cursor between the column headings so it changes to a black double-headed arrow and double-click. This will AutoFit the column to the left to be as wide as it needs to be, based on the contents of all of its cells.
You can also select multiple columns and double click to AutoFit them all at once to their own needs.
However, if you have a sheet title or other headings like “Working with Bigger Data” that are wider than the data contents below, columns may expand to be larger than they need to be to display the data.
To ensure the column widths only expand to fit the data in the list…
- select somewhere in the list and use Ctrl+A to select all of the data
- confirm all of the data is selected by holding Ctrl and scrolling the mouse wheel to zoom out to check the highlighted range (zoom back in as before)
- go to the Home tab in the ribbon
- then from the Cells group select the Format button
- finally, select “AutoFit Column Width.”
So since I have cells selected, it will only base the column width on the selected cells.
Wrap header text
If you want some columns to be narrower than their respective headings that it’s a good idea to wrap the header text so it can all still be displayed. Select all column header cells, and from the “Home” tab in the “Alignment” group, click “Wrap Text.” You will also need to resize the heading row to display all of the wrapped text.
Standardising number formats
Currently the Product ID values have a different number of characters or digits.
To display all of them with three digits by including leading zeros click on the Product ID number for your first item and hold Ctrl and Shift, and the down arrow to go to the bottom of the data. To check you have arrived at the last row of the data (still holding Ctrl and Shift) press the down arrow again and you should go all the way to the last row of the worksheet, row 1,048,576, then press the up arrow to go back to the bottom of your data. All of your ProductID numbers should now be selected.
Then go to the Number format group in the Home tab of the ribbon, and select More Number Formats.
We get the dialog box and then select Custom.
This contains several different formats for custom numbers; the sample window displays what your number would look like if you chose that option.
To create your preferred format you can clear the “Type” section and replace it with the format you want. In our case, we want three zeros. The sample box shows that our number will display as 007 so click OK.
So that takes care of our product ID. In a similar way, we can also format the Unit Price values.
However, this time we are going to make use of the Accounting shortcut button.
The Accounting number format lines up decimal places, leaves some extra space on each side of the cell for parentheses used with negative numbers, and you can also change your currency symbols as required.
The next three columns all refer to quantities of the product so can be formatted with the same style. To select all values, hold Shift while selecting the three columns then press Ctrl+Shift+Down arrow to go to the bottom of the data. For these values, I want to use the Comma Style.
Comma Style is, in fact, the Accounting style but just with no currency symbol. Now, these are quantities and we don’t sell partial items, so reduce the decimal or decrease the decimal places with these buttons that are also in the Number group.
The zero values are represented by hyphens, which are also part of the Accounting style.
Ensuring totals include data added later
If your list will grow over time, for example, as we get new products, it would be helpful to include a total inventory number in the headings area of the spreadsheet to save us scrolling down to the bottom of the spreadsheet to find the answer each time. However, simply using a Sum formula to only include the current data will exclude the new data when it is added. To ensure any totals include new rows of data added to the bottom of the data set, ensure that the sum range goes all the way down to the bottom of the entire worksheet (row 1048576).
The total will be automatically displayed with the same number format you used for the items the sum it is totaling.
Now the data is nicely formatted, it’s easy to read and it can be sorted, filtered and summarised in any way that is needed.
Keep your data together
It is generally better to keep all similar data in one large database. By differentiating or separating data into different segments, reports or worksheets, you run the risk of each set of data changing format over time.
In the examples below, orders have been split into different worksheets for different salespeople and they each have different formats that make then difficult to compare or merge into an overall total.
A far better way to organize this would be to keep all of the order data in one spreadsheet and include the name of the salesperson in an additional column.
The entire data set can then be filtered to include only one salesperson’s orders and the resulting information will be presented in a standardized format each time.
So if you start with all data on one sheet, it will make your life a whole lot easier down the road.
Here are some other tips you may find useful.
- If you need to enter a date you can enter MM/DD; Excel will assume the current year.
- The Delete key does not delete anything; it’s simply a shortcut for the command of Clear Contents.
- Keeping data heading text left-aligned means that if you add filters at a later stage, the filter buttons obscure the least amount of text.
- Get used to that Ctrl+Z because that’s how you undo your errors.
- Ctrl+Home is how you always get to the top-left cell in a worksheet. This is not necessarily Cell A1; if you are using frozen panes, it gets you to the “first cell of the worksheet,” so it will go to the top-left cell after the frozen areas.