Named Ranges

range1

Have you ever looked at a formula and thought, “What in the world does this mean! What items make up this crazy formula?” Or have you ever found your self constantly linking back to a cell and thought, there has to be a better way? If so, you are not alone. That is why named ranges exist! After you use named ranges, you’ll wonder how you ever got through life before.

Microsoft sums up perfectly why you should use named ranges: they make your “formulas much easier to understand and maintain”. Here is how they work…

By default, each cell has a name that corresponds with the column letter and the cell number. To see the cell’s name, look at the top left hand corner of your spreadsheet.

ranges2

You can change the name of that particular cell to any name you’d like (although you should be somewhat descriptive). In this case, I’ll rename the cell “Age”. To change the name, click in the area where the name appears, type the name and hit Enter.ranges3

Now whenever you refer to that cell, you will see “Age” appear in the formula bar instead of the cell reference.

ranges4

This also means that you can easily refer back to the cell by typing simply typing the name of range. No more going back and forth between spreadsheets to refer to cells!

The Power of Keyboard Shortcuts

keyboardIf you wanted to quickly insert a row, how would you go about doing that? If you are like most Excel users, you will use your mouse to click on the row, right click, and then select “Insert Row”. What if you wanted to quickly select all items in a column with 203,453 rows? Would you use your mouse to select and scroll alllll the way down?

Stop the madness! Never (at least rarely) use your mouse again! This will save you precious time and will amaze your co-workers. Using keyboard short cuts will make you look like a competitive ballroom dancer as you glide through Excel. You will make your mouse-using co-workers look like a 16 year old boy at prom.

Here is a great list that summarizes some of the most useful shortcuts. At a minimum, you should learn the following:

  • Select entire row = Shift + Spacebar
  • Select entire column = CTRL + Spacebar
  • Add row (after selection = CTRL + ALT + “+”
  • Delete row (after selection) = CTRL + “-“
  • Scroll to bottom of list = CTRL + Down Arrow
  • Select all items in a list = CTRL + Shift + Down Arrow

Once you learn those, try embarking on new and exciting keyboard shortcut adventures. For those who are looking for an African Safari, try learning to navigate the Ribbon using the Alt key (stay tuned for a post on this later).

Using keyboard shortcuts will take some time to learn. At first it will seem like it is taking longer than using the trusted mouse, but once you learn them, you will be shocked by how much time they save. Stay strong!