Excel - Tips & Tricks
The following tips and tricks reference functionality in Excel 2016, however most should be applicable to earlier versions of Excel for Mac and PC.
Select all & automatically resize all at columns at once
You might know how to select all by using the Ctrl + A shortcut, but you can also select the corner where the row headings and column headings intersect to select the entire worksheet. Once you’ve selected the entire worksheet using the method above, you can automatically resize all column widths to fit the longest cell in the column by double clicking in between any of the columns.
Add more than one new row or column
You may know that you can create a new row or column by selecting a column or row and then right clicking and choosing Insert. However, if you want to insert multiple rows and columns, you can simply highlight the number of rows or columns that you want to insert. Then, right click and choose Insert. New rows will be inserted above the row or to the left of the column you first selected.
When you want to copy a formula or cell to all the cells below, you can hover in the lower right corner of the cell to get the Autofill Handle. It looks like a tiny black cross.
Once you see the autofill handle, you can drag it to the bottom right corner of the last cell you want the cell copied to. Or, you can double click, and the cell will be copied down to the first empty row.
If you have Excel 2013, you can take advantage of Flash Fill to fill a data column based on a pattern. Excel 2013 will present Flash Fill when it recognizes a pattern and works best when your data has some consistency. You can only use Flash Fill with a column adjacent to the ones that you are references. To use Flash Fill, begin filling in a column that references another column working from the first cell then moving downward. Once you enter a few of the cells and Excel recognizes the patter, you'll see the rest of your data listed below in a grey font. Once you see the grey Flashfill data, you can press Enter on the keyboard to accept.
If you miss the grey Flash Fill options, you can use the autofill option to fill down. Then, you can select the autofill options and choose Flash Fill.
Combine text with &
In addition to using the concatenate function to combine cells, you can use the ampersand character to combine cells with text.
Jump to the last cell or select a column or row of data
When you click the Ctrl key plus any arrow button on the keyboard, you can jump to the edge of the sheet in different directions. If you want to jump to the bottom line of the data, just try to click Ctrl + the down arrow. Using this method and adding the Shift key, you can select entire rows or columns. For example, if you want to select a column of data, select the first cell and then click Ctrl + Shift + the down arrow.
View average, count, minimum value, maximum value, and the sum by simply selecting
Most users know how to check the data status in the bottom of an Excel sheet, like Average and Sum Value. However, do you know you can move the pointer to the bottom tab and right click to get more status, as shown below? Right click in that area and you will be able to see more summary options.
Create a Shortcut Menu using the Quick Access Menu
In any Microsoft Office 2013 program, you can right click on any tool in the toolbar to add it to the Quick Access Menu at the top of your screen. The benefit of adding tools that you often use to this menu is that you automatically create a shortcut menu. To select the tools in the menu, you can click the Alt key and then press the number corresponding with the order of the tools at the top.
Transpose data from a row to a column
You would use this feature if you want to transpose data to get a better display; however, retyping all data would be the last thing you would need to do if you know how to use the Transpose function in Paste. Here’s how: copy the area you want to transpose, move the pointer to another blank location. Go to Home > Paste > Transpose, please note that this function won’t activate until you copy the data first.