Disclaimer: These tips are known to work in Excel 2010. They may or may not work in other versions of Excel.
Excel can be an extremely powerful business tool, if you know how to use it correctly. Take the hassle out of locating commonly used features, and save some time, with these 10 useful Excel shortcuts.
CTRL + Page Down / CTRL
+ Page Up Move Between Worksheets
Both of these commands work the same, by moving you quickly through the workbook tabs at the bottom, but which command you use depends on which direction you want to go.
CTRL + Page Down will move through the tabs from left to right. This is helpful when you are on the first workbook tab, and want to move to others in the series.
CTRL + Page Up will move through the workbook tabs from right to left. So, once you’ve viewed your last workbook tab, you can easily move back to the first tab (or any other tab before the one you’re on) by pressing CTRL + Page Up.
CTRL + SHIFT + & Apply Borders
Pressing CTRL + SHIFT + & applies a thin black border to the selected area. Cells must be pre-selected before using this shortcut for this command to work.
CTRL + SHIFT + _ Un-apply Borders
Similarly, pressing CTRL + SHIFT + _ un-applies the border to the selected area. Again, cells containing the border you wish to remove must be pre-selected before using this shortcut.
CTRL + SHIFT + ~ General Number Formatting
With the applicable cells selected, press CTRL + SHIFT + ~ to format a cell with General Number formatting.
CTRL + SHIFT + $ Currency Formatting
Press CTRL + SHIFT + $ to apply currency formatting to the selected cells. Formatting will include two decimal places.
CTRL + SHIFT + % Percentage Formatting
To apply Percentage formatting, with no decimal places, highlight the applicable cells, then press CTRL + SHIFT + %.
CTRL + SHIFT + # Date Formatting
To apply date formatting (with month and year), highlight the selected cells, then press CTRL + SHIFT + #.
CTRL + SHIFT + @ Time Formatting
Similarly, to apply time formatting, including hours and minutes, AM and PM, select the appropriate cells, then press CTRL + SHIFT +@.
CTRL + ; Enters Current Date
To enter today’s date into a cell, select the cell, then press CTRL +; (semi-colon).
CTRL + SHIFT + : Enters Current Time
Similarly, to enter the current time into a cell, in AM / PM formatting, select the cell, then press CTRL + SHIFT + : (colon).
Do you use Excel to report and analyze large chunks of data? Don’t waste hours figuring out the proper formulas to use to report your data – use PivotTables instead, and extract the data you need in minutes.
PivotTables are an easy way to summarize data, and see how that data relates and corresponds to other gathered data. What’s more, they allow you to easily manage your spreadsheet data simply by dragging and dropping.
Introduced in Excel 2000, PivotTables aren’t new; they’re just not well known. While it was a handy feature even in 2000, the updates that Microsoft has made to PivotTables in Excel 2007 and 2010 make them a more robust business tool today than they were at their inception.
To use PivotTables, setup your data on one worksheet. (A worksheet is one tab within Excel. Example Sheet1, Sheet2….) Headers are very important! Make sure that each column has a header classifying the data. Do not leave a blank space between the header and the first piece of data.
Disclaimer: These instructions are based off of Excel 2010. If you have a different version, you may have to access PivotTables differently.
Once your data is compiled, click on Insert, then click on PivotTable. This will bring up the Create PivotTable screen.
Select the table or range you want to use. To do this, click on the button with the red arrow, to the right of the Table/Range field. It will minimize this area to one long field. Click on Sheet1 (or whichever sheet contains your original data). Then, select the fields you would like to use in your PivotTable. Once selected, click the button with the red down arrow located to the right of your selection.
Next, choose where you want the PivotTable to be placed. Select Existing Worksheet, then select the fields where you would like the PivotTable to be located. You do this the same way you selected your table/range, by clicking the red arrow next to location, selecting Sheet2 (or whichever worksheet you would like to use), highlighting the fields where you would like the PivotTable to appear, and then clicking the red arrow again to return to the Create PivotTable screen.
Your Create PivotTable screen should look something like this. Once it’s setup, click on Okay to create the PivotTable.
You should have a screen that says “PivotTable, to build a report, choose fields from the PivotTable Field List.” To the right, the headers for the selected fields will appear. Choose which fields you want on your report. Then, drag and drop the fields to change the order of what the PivotTable reports.
The PivotTable Field List will allow you to arrange your fields in the order you would like to see them. To change your layout, simply change the field order by dragging and dropping.
While PivotTables look easy, they do have a slight learning curve. But don’t let this
deter you. Once you learn how to access your data using the power of PivotTables, you’ll wonder how you ever got along without them.