Excel Shortcuts Using the Alt key

Time Intelligence in Data Modeling Part 3: Dynamic Calendar in Power Query
February 1, 2021

I guess we all use our fair amount of Excel shortcuts that start with Ctrl. Sometimes we will even use some that use Ctrl and Shift keys. But very rarely do users use shortcuts with the Alt key. But if you don’t, you are missing some great shortcuts. Let’s look at some great things you can do using Alt-based shortcuts in Excel.

IF you find it easier to learn through Videos, here is a link to the Excel Olympics YouTube video explaining all the shortcuts described in this post.

ALT + = (Autosum)

Video of this shortcut in action.

Question: How can we SUM this data quickly, in Excel?

 

Do you know the fx button near the formula bar? That good old click on the formula bar and then scrolling to find SUM. You could also just start typing with the “=” (equal) sign and continue typing SUM afterward to call the function faster. But whatever way you chose, there is a faster way to SUM your data in Excel.

First, let’s select the data we would like to sum up.

 

Note: you can select data without headers as well.

Now simply press ALT + = and there it is.

 

Let’s take a look at the contents of cells (look inside the formula bar)  B15, C15, and D15 to see whether we actually got the SUM function.

We did. But why? If you select just the cells you would like to sum up, Apples column in our example, and press ALT + =, only that column will be summed up with the result written in the first empty cell underneath the data selected.  The same goes for Pears column. If you select the empty column, to the right, (Apples Pears column), you will get a sum of Apples + Pairs. If you select the row below our numbers at the bottom, and the empty column to the right, you will get all the SUMs in one move. Really amazing and definitely worth remembering and using to get faster and more efficient in Excel.

Heads-Up: This shortcut may not work at first. You may need to do some extra work. (read more about it here.)

ALT + F1

This is a quick win for you and also one of my favorite Excel Shortcuts of all time :),  so let’s dive into it.

Video of the Alt + F1 shortcut in Action

First, select the data you want to chart (or just click on our data range ), then just press ALT + F1 and that’s it.

Note: You will get a default Excel chart. Usually, it is a simple column chart. In order to change this default setting, go to Charts group on Insert Ribbon, make a right-click on a particular chart type you would like, right-click  Set as Default.

P.S. If you would like to see your chart on a special Chart Sheet (yes Excel has those ???? ), try F11 instead of ALT + F1. This is sometimes used as you can do certain things on a Chart Sheet in Excel that you can’t do if Chart is an object on a “normal” Excel Sheet.

ALT + ; Select visible cells

View a video of this Shortcut in action.

When in a rush to copy-paste data, you may sometimes forget about all the hidden rows you have. That is the fastest way to making a costly mistake. N  I would like to copy only visible data (the rows that I can see) onto a new sheet.

 

First, let’s look at what not to do. If we simply select this range of data and copy it, this happens. We get all the data, even the hidden rows.

 

But how do we get around this problem? One solution would be to hold CTRL and then select all the rows we need as separate ranges. That would be ok for a small dataset but what, if we had more hidden rows in other parts of our data? That is exactly where ALT + ; comes to the rescue. Select the data you would like to copy with a simple CTRL+A, and then press ALT + ;. You should notice a visible difference, there should be a discrete white line between the data and hidden row numbers. Something like this:

 

Press CTRL + C to copy data, and paste your data (CTRL + V) where you intended to.

NOTE: If you’re using a keyboard language other than English US,  you probably know that a shortcut to change the keyboard input language is ALT + SHIFT. It is crucial that whenever you press ALT + SHIFT in combination with other keys, that you actually complete the series otherwise you could just end up with your keyboard giving you strange symbols instead of the desired ones. So in this instance make sure you hold down ALT + SHIFT before pressing the “,” sign or you will just switch the keyboard.

Holding the ALT key while moving data

Video explanation of this process.

Moving data around sheets is a common task. We usually help ourselves by a simple cut and paste to get the job done. However, there may be a more effective approach. Once you start moving around a selection of data, make sure you hold the ALT key.  This will enable you to switch between Excel WorkSheets while still holding the mouse key and moving data. Afterward, just point your cursor to a new location in a new worksheet where you want to paste the data.

Here is a live demonstration.

 

Note: With the data selected, there are quite a few options open to you:

  1. Simply moving data around serves like cut and copy on a single sheet,
  2. Holding CTRL while moving will copy data (works together with ALT if you want to copy it onto a new worksheet),
  3. Holding SHIFT key will insert data between rows or columns (can also be combined with ALT).

Note: ALT + SHIFT

Switching between keyboard input languages can be a common task (especially for multilingual people). As we mentioned above, ALT+SHIFT will change your keyboard input language and one (myself included) might not even notice that it is changed.

ALT navigation

A video depicting this process shown in Excel.

If you want to go “mouseless” in your Office work, you could help yourself with some ALT navigation.

Quick example of creating a chart in Excel using nothing but the keyboard and starting off with the ALT key.

 

Just make sure you follow the symbol labeling, as visible above press ALT first, then N to activate the Insert tab. Finally, press C1 to create a column chart. The same logic holds for navigating inside other tabs and groups in Excel.

Shift+Alt+Arrow Down/Up

Video of Alt and Arrows in action.

Bonus shortcut, this one particularly useful in Word, Outlook, and PowerPoint. SHIFT + ALT + UP/ DOWN ARROW, applied in Microsoft Office Word, allows you to move the paragraphs around. To do so, put the cursor in front of the desired paragraph (or in any other place in this paragraph) and press the SHIFT + ALT + UP ARROW if you want to move the paragraph up, or SHIFT + ALT + DOWN ARROW, if you want to move the paragraph down. Press the Arrow button as many times as you want your paragraph to be moved up or down. Nice and easy, right? You can also move the blank paragraphs around the documents if you need to. The shortcut is perfect for moving the paragraphs across the relatively small and medium-sized documents.

Before:

 

After:

 

Quick note: Did I write all this text myself? Of course not. In order to create a random text in Word, we use =RAND(X,Y)X stands for the number of paragraphs and Y for the number of sentences in each paragraph.