Helpful Tips and Shortcuts for Microsoft Excel

Microsoft Excel is used by millions of professionals around the world, it is a spreadsheet program that can be used to organize and manipulate data and information. ROSE IT Services Limited has compiled a few helpful tips and shortcuts to help boost your productivity using this application.

Microsoft Excel Keyboard Shortcuts

Never mind the mouse, Microsoft Excel allows you to perform every command in the program by using just your keyboard. Among the most useful shortcuts for Microsoft Excel are:

1. Ctrl + N – Create a new, blank workbook

2. Ctrl + O – Displays the Open dialog box

3. Ctrl + W – Close active workbook

4. Alt + F4 – Close Excel

5. F12 – Displays the Save As dialog box

6. F4 – Repeat the last command or action

7. Ctrl + ; – Enter current date

8. Ctrl + Shift + : – Enter current time

9. Ctrl + – (minus) – Open the Delete Cells dialog

10. Ctrl + + (plus) – Opens the Insert Cells dialog

Feel free to download our comprehensive List of Excel Keyboard Shortcuts.

Fill Data Automatically in Worksheet Cells

Instead of entering data manually on a worksheet, you can use the Auto Fill feature to fill cells with data that follows a pattern or that is based on data in other cells.

1. Write down the first few entries, for example, let’s say you want to complete all the even numbers between 1 to 100. You’ll write down 2, 4 and 6 in three simultaneous cells in a column.

2. Select the first cell, drag and drop on the third cell, until all three cells are highlighted

3. Finally, take your pointer to the bottom-right corner of your selected cells and, when it turns into a black plus sign, drag and drop downwards (for 50 rows, in our example)

Keep in mind, this feature is not limited to numbers only, it can fill patterns for months, days of the week and formulas as well.

Microsoft Excel’s Key Tips

If you’re new to the ribbon, the ribbon comes with new shortcuts called Key Tips. To make the Key Tips appear, press Alt. For example:

To display a tab on the ribbon, press the key for the tab: for example, press the letter N for the Insert tab or M for the Formulas tab.

Selecting an Entire Row Quickly

In order to select the contents of an entire row quickly:

1. Click on any cell in the row you wish to select

2. Press Shift + Spacebar

Moving the Contents of a Row or Column

You can use your mouse to quickly move the contents of a row or column to another location in your spreadsheet:

1. Highlight the cells of rows or columns you want to move

2. Click on the black border along the outer edge of the cells (your Mouse pointer should look like a plus sign with arrows at the tips)

3. Drag contents to the location of your choice

Select the Contents of an Entire Spreadsheet

Using Excel’s Extend functionality allows you to quickly select the contents of an entire spreadsheet, here’s how:

1. Click on the cell of your starting row in your spreadsheet

2. Press F8 to activate the extend command

3. Click on the final cell in your spreadsheet

4. Hit F8 again to cancel the extend command

Finding the Sum of an Entire Column

Instead of manually entering the formula, or selecting the entire column of cells you need to add, try this:

1. Select the first empty cell in your column; at the end of all the numbers Press Alt + =

2. Hit Tab or Enter to accept the formula

Keep Headings Visible when you Scroll

If you work with lengthy, complicated excel sheets that you have to scroll down, this trick will help you a lot. The Freeze Panes functionality allows you to keep either rows or columns visible as you scroll, try this:

1. Select a cell immediately below the row you want to freeze

2. Select the View tab

3. Go to the Freeze Panes menu option

4. Select Freeze Panes option

After you are done working, you can simply unfreeze the frozen panes by going to the Freeze Panes menu option and selecting Unfreeze Panes.

Troubleshoot Formulae in your Spreadsheet

To quickly see the formulas that have been applied to a specific spreadsheet, simply do this:

1. Select Ctrl + ` (this is the acute accent found before the number 1 on our keyboard)

2. Toggle between this key combination to turn this feature on and off.