Our 5 favorite Excel shortcuts and tricks
Micorsoft Excel’s shortcuts are not only big time-savers, but they’re pretty easy to master. Here are some of our favorites. Print these out and post them to next to your desk for the next time you’re in a deep data dive or large Excel file that you want to get through faster, and smarter.
1. Create maps.
Did you know that you can create maps from geographically recognizable data in Excel using standard country/region, state/province, county or postal code? It works best with states and countries/regions. Standard abbreviations such as states in the U.S.A. or provinces in Canada can be used in place of the full versions. Highlight the location data and the value data you wish to show. Try a 3-D map, too. This allows you to show values as 3-D columns rising out of a more geographically detailed map. Assembling one is a bit more involved, but worth it if your data needs this kind of display.
2. Clean-up dates.
Do you get import files with funky dates? For example, are month, day and year separated by a period (.) or only spaces? It might take some maneuvering, but you can probably disassemble, then reassemble it with text functions. So, if you were given 12.31.2019 in cell A1 and needed 12/31/19, you could disassemble it with =LEFT(A1,2) to get the month, =MID(A1,4,2) to get the day and =RIGHT(A1,4) to get the year. Now, using the DATE function, reassemble it. You could do it all in one formula like this: =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2)). The syntax of DATE is year, month, day.
3. Flash fill.
Ctrl+E – Flash Fill is an amazing tool in Excel 2013 and later. Type the first entry, press Tab and the rest fill in. Oops! Press Enter instead? Ctrl+E lets you take advantage of Flash Fill even if you forgot to hit Tab.
4. Formula bar tricks.
Shift+F3 – Did you accidentally hide your formula bar? Do you normally use the f to access the Insert function dialog? Try Shift+F3 to get to it, instead.
Ctrl+Shift+F – Ribbon gone missing? Need to change the font of text in a cell? Try Ctrl+Shift+F to access the format cells dialog box.
5. Autopopulate dates and time
Need to put today’s date in a cell? Try the Ctrl+; shortcut. Whatever date your computer says it is, is the date that will be entered into the cell. Always need the cell to convey the current date? Try the TODAY() function. This will create a constant that doesn’t change.
Ctrl+: – Someone finally made it easy to enter the current time into a cell. The Ctrl+: shortcut (which can also be interpreted as Ctrl+Shift+;) will enter the time in the correct format.
Ctrl+;,Spc,Ctrl+: – To enter the current date and time as a constant, in edit mode, use the Ctrl+; shortcut, followed by a tap on the spacebar, followed by the Ctrl+Shift+; shortcut. To always have the current date and time, use the NOW() function. Be sure to format the cell to show both date and time. Otherwise you may only see time.
