EXCEL STUDY

1. Getting to Know the User Interface

☆

User interface: Ribbon

- To remove the ribbon, double-click on a tab
- To restore the ribbon, double-click on a tab again
- File is a special tab that allows us to open,save, print and create workbooks

User interface: Formula bar

- The formula bar displays the formula for the selected cell, not the output

User interface: Worksheet

- New workbooks have 3 worksheets but you can add as many as you want
- Each worksheet contains 16,384 columns and 1,048,576 rows
- Rows in Excel are marked by numbers
- Columns are labelled as letters, following a convention: A -> Z -> AA -> ZZ -> AAA

Keyboard shortcuts

F2: Jump into formula of selected cell

Keyboard shortcuts in the ribbon

- These shortcuts automatically access commands in the ribbon
- To start, simply press ALT and then type the letter under the desired tab

Inserting Rows and Columns | Online Excel Training | Kubicle

Other keyboard shortcuts

- These shortcuts do not access the ribbon and must be committed to memory
- Some are common to Word and PowerPoint (e.g. CTRL + C to copy)
- Others are specific to Excel (e.g. CTRL + PgDn to move worksheet)

When using Excel, we typically spend our time typing numbers and text, and typically keep two hands on the keyboard almost all of the time.

As a result, reaching for the mouse repeatedly to access commands on the ribbon becomes very time-consuming.

A much better alternative is to use keyboard shortcuts to access these commands.

Although they require some practice at the start, keyboard shortcuts will make you much faster at Excel over time.

There are broadly two types of keyboard shortcuts in Excel: shortcuts that directly access the ribbon commands and then shortcuts that don't.

To access shortcuts in the ribbon, we simply press the Alt key and this displays a series of letters underneath each tab.

Let's press H for the Home tab.

And this immediately provides me with all the shortcut commands for this tab.

So for example, if I wanted to convert the number 12 into the percentage format, I would just press P and this gives me 1200%.

If I press Alt + A instead of Alt + H, you can see that the shortcuts appear for this tab as well.

Ribbon shortcuts are very helpful because all you need to remember is one key: Alt,and Excel will tell you the rest of the keys to press to execute the shortcut.

The second set of keyboard shortcuts which do not access the ribbon are probably used more often, but they do require you to commit them to memory.

Some of these shortcuts you might be familiar with from other programs.

For example, Ctrl + B to bold text, Ctrl +C to copy, and Ctrl + V to paste.

Others, such as Ctrl + Pgdown to move to the next sheet are specific to Excel.

To help you remember these shortcuts, I'll include every new shortcut I use in the lesson in the show notes underneath the video.

Although I try to use shortcuts as much as possible, there are some occasions, for example when I'm selecting a text color, where I find the mouse easier to use.

If you can learn to limit your use of the mouse to these few instances, you'll become much more productive at navigating and manipulating data in Excel.

5. Essential Shortcuts for Cell Selection

Lesson Files

With these lesson files, you can follow along and re-create the solution offered in the lesson. This will help you achieve more effective learning.

  Essential Shortcuts for Cell Selection Before.xlsx

Useful keyboard shortcuts: Cell selection

SHIFT + →: Select cells in the direction of arrow
CTRL + SHIFT + →: Select all cells within data region
CTRL + A: Select all cells in data region
SHIFT + Spacebar: Select entire row
CTRL + Spacebar: Select entire column
Shift + F8: Select multiple data regions
If you have cells selected and then use another selection command, Excel performs this command on every selected cell

Lesson Files

With these lesson files, you can follow along and re-create the solution offered in the lesson. This will help you achieve more effective learning.

  Inserting Rows and Columns Before.xlsx  Inserting Rows and Columns After.xlsx

Useful keyboard shortcuts: inserting rows and columns

ALT + I, R: Insert a row above selected row
ALT+ I, C: Insert a column to the right of the selected column
F4: Repeats previous command

Adding data to a spreadsheet

- Always add rows and columns to datasets, not individual or groups of cells
- This will drastically reduce the potential for errors in your dataset

Useful keyboard shortcuts: deleting rows and columns

SHIFT + Spacebar: Select row
CTRL + Spacebar: Select column
CTRL + -: Delete selected row or column

Moving rows of cells

- Be careful of cut and paste because this can result in overwriting existing data
- Instead use Right-mouse button key + E, which will insert the cut cells above selected cells

Inserting and editing rows and columns are some of the most common commands you'll perform in Excel. So as you can probably guess, we'll be using a number of keyboard shortcuts to perform these tasks. To insert a row in Excel, let's first select a row with Shift + Space Bar, and then we'll press Alt + I and then R to insert a row. This creates a row above the selected row.

Let's try that again by moving to a new row, we'll select it with Shift + Space Bar, and then we'll insert with Alt + I and then R for row.

To insert a column, we'll simply select a column with Ctrl + Space Bar, and then we'll press Alt + I + C to insert a column to the right of the selected column. Let's insert another column by repeating that command. So it's: Alt + I + C.

If you need to insert many rows and columns, there's a very helpful command called F4,which simply repeats the previous action you've made. So given that the previous action I've made has been Alt + I + C and I press F4, it generates a new column. If I continue to press F4, it repeats the Alt + I + C command and generates new columns. I'll just undo these new columns quickly by pressing Ctrl + Z. The F4 button will repeatany command in Excel, but it's particularly useful when inserting multiple rows and columns. You might be wondering why I'm focusing so much on inserting rows and columns, and not inserting cells. Well, it turns out that inserting rows and columns is the safest way of adding new data to a spreadsheet. Adding individual cells or even groups of cells can be very dangerous and create errors in your data sets or models. So to be on the safe side, always add rows or columns to spreadsheets, not cells. We now have quite a few blank rows on our spreadsheet, so let's remove them. Let's first select a blank row with Shift + Space Bar and then we'll press Ctrl + - to delete. I'll do that one more time, by navigating to a blank row, selecting it, and then pressing Ctrl + - to delete.

Deleting columns is actually the exact same command. So let's select a column with Ctrl + Space Bar, and then we'll press Ctrl + - to delete.

and do that one more time, Ctrl + - to delete the column. Now that we know how to insert and delete rows and columns, let's figure out how to move them. Let's say I want to move the bottom four rows down three additional rows. Let's navigate down to them using Ctrl + down arrow, let's select the bottom row with Shift + Space Bar, and then holding Shift, use the up arrow to select the remaining rows. Then press Ctrl + Xto cut these cells and move the cursor to the top left-hand corner of where you'd like to paste. Then press Ctrl + V to paste and the cells are moved down three rows. Let's now try something a little more challenging.

Let's move the bottom four rows up to the very top above row 1. We'll start by cutting this content with Ctrl + X and then navigating up to the top row, I'll then paste with Ctrl + V, and unfortunately, this doesn't work.

It actually overwrites the first four rows. Not exactly what we want. So let's undo with Ctrl + Z, and instead we'll use a different command. We'll use right mouse button + E, press Enter and this inserts the four rows above the first row, which is exactly what we want.

When I say right mouse button, I don't actually press the right side of the mouse, I press this key on the keyboard normally located on the bottom row which serves the exact same function as a right mouse button click. If your keyboard doesn't have this key, you can also use Shift + F10 to perform a right mouse button click. Hopefully, the last example showed you how moving rows of data can be tricky and how easy it is to lose data when completing this task. To be on the safe side, be sure to use the right mouse button and E when moving cells, not pasting with Ctrl +V. To finish up this lesson, I'm going to summarize most of the commands we learned today by removing all the blank rows and columns in this data set and rearranging the rows in the correct order. Let's start by removing the columns to the left of the data set. So I'll select withCtrl + Space Bar and then I'll press Ctrl + - to delete. I will press Ctrl + - again and one more time. Let's now scroll up to the top and I'll remove this blank row, Shift + Space Bar to select, and then Ctrl + - , and Ctrl + - one more time. I'll now remove the blank row between 12 and 13, select it with Shift + Space Bar, and Ctrl + - to delete. And to finish up, let's move the bottom four rows back to their original position. Let's scroll up to the top, let's select them, Shift + Space Bar and then Shift + down arrow.

Let's cut with Ctrl + X, scroll down to the bottom with Ctrl + down arrow, and then right mouse button + E and press Enter to insert. Now we have a clean data set, devoid of blank rows and columns, with all of the rows in the correct order. As an exercise, try to clean up the data set in the “before” file below this video, as I have done here. Also, try to stick with the keyboard shortcuts as much as possible, however tempting it may be to use the mouse.