29 Useful Excel Shortcuts and How Macros and VBA Code Can Automate Your Work

Listen on Spotify.

Do you use Excel for Windows daily? Then you know the power of shortcuts. You might not have discovered the power macros and Visual Basic for Applications (VBA) has to automate and speed up your work. I’m new to it myself and am still amazed by what you can do with macros and VBA code to start working more efficiently. I thought it might be helpful to investigate macros and how you can edit VBA, to expand on my previous post on Excel.

Later, I’ll go through how to build macros and share more about VBA, and how to edit and manipulate simple code in the Visual Basic Editor (VBE) to boost your productivity. First, though, I’ll share some keyboard shortcuts that will help you power through your work.

Let’s get into it.

29 super keyboard shortcuts in Excel (for Windows)

There are hundreds of shortcuts you can use in Excel, and you likely won’t use all of them. Here are some I find most useful.

General shortcuts

• Ctrl + S saves your workbook.
• Ctrl + O opens a workbook.
• Ctrl + W closes your workbook.
• Alt + W opens the View tab.
• Ctrl + T transforms a highlighted data set into a table.
• Ctrl + D is a quick way to copy items down.
• Ctrl + Shift + + inserts a new row above the one selected.
• Ctrl + – deletes a row you select.

Navigational shortcuts

• Ctrl + Home navigates to the top left-hand corner of your data.
• Ctrl + End takes you to the bottom right corner of your data.
• Ctrl + Down Arrow takes you to the last row of the selected column of the data.
• Ctrl + PgUp switches between sheets in a workbook, moving left. To move to the right, use Ctrl + PgDn.
• Ctrl + Arrow to jump between cells containing data, skipping over blank cells.

Shortcuts for selecting data

• Ctrl + Space selects an entire column.
• Shift + Space selects a whole row.
• Ctrl + Shift + Arrow Down will select a whole column of data within a range.
• Ctrl + Shift + Arrow Right will select all the rows within a range.

Action and formatting shortcuts

• Alt + = adds up the values in a column. Remember to click the appropriate cell first.
• Ctrl + E to flash fill. This one’s useful if you want to extract information from elsewhere. For instance, if you have a list of email addresses and want to extract the first and last names from them. Alternatively, you can use the Left, Right and Mid Text functions to extract the information you need.
• Ctrl + Shift + L switches filters on, so you can set them up fast. If you use this shortcut once, filters will be turned on. Use it twice and the filters on your data set will be disabled.
• Ctrl + 1 opens the format cells menu. If you click on a chart or a text box and do the same, Excel will open the appropriate format menus.
• F2 lets you edit a selected cell, useful if you want to change or correct a formula.
• F4 makes references absolute (by changing the style of reference). If you keep pressing the key, Excel will go through all the possible absolute references. When you’re not editing a formula, you can use F4 to repeat your previous action.
• Shift + F2 allows you to add a note or comment to a cell.
• Ctrl + Shift + ; inserts the time for you.
• Ctrl + ; inserts the date for you.
• Ctrl + Shift + ! will apply the number format to a cell, adding a separator and 2 decimal places.
• Alt + F11 opens the VBA editor when working with macros.

Don’t worry if you forget a shortcut. If you press the Alt key, all shortcuts will be displayed on the screen. You can press Esc if you do this by accident.

Now, let’s find out more about macros.

Creating marvellous macros

You can create macros in Excel to automate repetitive tasks. Here’re a few examples of tasks you might want to automate.

• Wrapping and centring headings wider than the data entries below them.
• Adding fill colour to highlight specific cells.
• Making sure the zoom setting on new workbooks and new sheets is set as 150%.

How to record a macro

Let’s use the first repetitive task and go through the steps of building a macro.

We want to wrap and centre the headings of two adjacent cells.

Step 1: Select the relevant cells and click the Record Macro icon on the status bar at the bottom of the spreadsheet. The macro will begin to record. You can also start recording through the View tab on your Ribbon. Give it a name. This one could be named formatting, or format headings, for example.

Step 2: Carefully go through the formatting actions you want to record, without clicking away from the cells you’ve selected in your sheet. For our example, you could do this in the Format Cell window by selecting centre alignment and wrap text. When you’re finished, click the macro icon again to stop recording, or click the macros button in the View tab to stop recording.

Step 3: You can create a button by adding a shape to the top of your spreadsheet, giving it a name and assigning the macro to it. That way, you can run it with just one click. You can also assign keyboard shortcuts to macros. To play it, select the cell you want to apply the formatting to and access the View tab. Then select the macro you’ve just created and click run. If you’ve put a button in your spreadsheet and assigned your macro to it, you can click that to run it. Take care when assigning shortcut keys to macros because they will override existing shortcuts.

Soon, we’ll look at how you can edit a macro and use comments to keep track of your code. Next, let’s find out more about VBA.

The magic of VBA code

VBA stands for Visual Basic for Applications and can have a massive effect on the way you work. It can help boost your productivity even more.

VBA programmes are created, managed, and edited in the Visual Basic Editor (VBE). The simplest way to open the editor is by using the Alt + F11 shortcut. You can also select Visual Basic from the Code group under the Developer tab.

All the macros you record in Excel are translated into VBA code, which is managed and stored in the editor. You can use the editor to change the recorded macros and join them together as needed.

Here’re some things you can use macros and VBA to do:

• Record macros that automate tasks to improve your efficiency when working with spreadsheets. By using macros to do repetitive, low-value tasks, you’ll be able to spend more time doing high-value work that gets results.
• Create custom functions, especially for commonly used calculations and formatting options to streamline the way you work in Excel.
• Build VBA programmes to take the in-built functionality of your spreadsheet to the next level. These VBA programmes can do more complex calculations. You can also create a custom interface. This may be useful if you want to link it with other software, for instance.

If you spot a potential issue with your macro and want to add something to it, you can use the run-through feature, so that you can run the macro line-by-line and find where you need to make changes in the VBA code of your macro. You can use this feature by clicking inside the macro within the editor and pressing F8. Each time you press F8 another line of VBA code runs. The code highlighted in yellow is the code that will run when you press F8. This is a good way to check the code for any errors, too. Just keep hitting F8 to run the macro slowly, until you can identify where things went awry.

It’s time to find out more about editing macros and keeping track of code.

Editing macros and using comments to keep tabs on code 

Let’s say you’ve identified a problem or error in the macro you’ve recorded. Let’s look at how you change the code to correct the error.

You can correct mistakes in the code by typing straight into the macro inside the VBE. Say that you need to change a line of code that selects a fixed range so that the selected range adapts as you add or discard data.

First, you need to record a new macro with a specific function, then replace the code in the other macro. Here’s how.

• Give your new macro a name and begin recording.
• Select the first cell in your data set (usually A1), then use Ctrl + Shift + Arrow Down to select the entire column
• Use Ctrl + Shift + Arrow Right to select the rest of the data in that range. Stop the recording.
• Open the editor and click on Module 2.
• Select the code inside the new macro and copy it.
• Click on Module 1 and find the macro you want to edit. Delete the line of code you want to remove and paste the new code that you just copied from the new macro, using the shortcut Ctrl + V.

If you follow these steps, you will have successfully edited a macro. But dealing with multiple macros and adding lines of code to different ones can get confusing and make the code hard to follow. This means you’re more likely to make mistakes. Let’s see how you can use comments to make things easier.

Using comments to manage code

To add comments to your code, open the VBA editor and go to your macro. Above the first lines of code, you can add a comment describing what that section of code does. First, type an apostrophe, then type a description. This could be ‘Removes all formatting. To check you’ve added the comment correctly, click somewhere else in the macro. The comment you added should go green. You can add comments describing what each section of code does.

Now all you need to do is save the macro-enabled workbook correctly by making sure you change the Save As Type in the dropdown menu to a Macro-Enabled Workbook. This ensures the macros you’ve created aren’t deleted.

Taking the time to add comments to the code will help avoid confusion, reduce the likelihood of errors and help improve your own productivity, as well as that of your colleagues.

Summary

We’ve covered some fantastic shortcuts you can use to speed up your work in Excel and make life easier. I hope you now feel ready to create powerful macros and edit and manage VBA code like a pro, by adding comments to keep track of it all. When you master using macros, you’ll likely see an uptick in your efficiency. Soon, you’ll be able to take your Excel skills to the next level. If you’d like to learn more about macros from an Excel specialist, I recommend checking out the fantastic training at Spreadsheeto.