Powered by KU Information Technology

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.


Review video below to learn how to create accessible Excel files. 


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.


Autofill Down

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.


Flash Fill

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.



Subscribe to RSS - Faculty

Link to KU IT's YouTube  Powered by KU IT 

Training Workshops

KU faculty, staff and students can sign up for courses at no cost at workshops.ku.edu.

Share your feedback

Would you like to see added or updated content on this site? Let us know by completing this quick form.