Shortcut keys useful in Microsoft excel

 Bonus - My Favorite Shortcuts 

Ctrl + S – save the active workbook with its current file name and location.

 Ctrl + Z – undo the last action .

Ctrl + Y - redo the last action (undo an undo!) .

Ctrl + C – copy the selected cells .

Ctrl + X – cut the selected cells.

 Ctrl + V – pasted the copied cells .

Ctrl + B – add or remove bold formatting .

Ctrl + I – add or remove italic formatting.

 Ctrl + D – copy the above cell into the below selected cells.

 Ctrl + R – copy the left cell into the right selected      cells .

Ctrl + F – open the find dialog box .

Ctrl + H – open the find and replace dialog box.

Essential tips and tricks useful in excel

  Microsoft excel

                                                 Learn Excel Visually

The ‘Learn Excel Visually’ Journey Excel is relevant for all aspects of a business and it’s not hard to learn, but even apparently smart people can have trouble mastering Excel. Why is this? Well - Excel provides the tools,but doesn’t tell you how to use them. You can read about the functionality of Excel and try and figure out what to use, but how do you know what to apply and when? The Learn Excel Visually (LEV) journey is here to take you through the essentials of the Excel process; set up your spreadsheet, capture and structure data efficiently, cleanse it, analyse it meaningfully and present it with visual oomph.

                                            

 Quick glossary

Most of you will already understand the basic elements of Excel but I want to use this section to clarify some key Excel terminology to ensure we are on the same page.

Menu = The list of items along the top of the screen; for example, file, insert, page layout etc.… 

Ribbon = Ribbon is like an expanded menu. It depicts all the features of Excel in an easy-to-understand pictorial form. Since Excel has 1000s of features, they are grouped into several ribbons. For example, the ‘insert’ ribbon has functions grouped into, clipboard, font, alignment, number, styles etc.…

Name box = Just underneath the Ribbon you have a white box on the left-hand side – it shows the cell reference (default A1) or if you have specified a name for a cell or range of cells, it will show that name.

Formula bar = Next to the Name box, also underneath the Ribbon. The formula bar shows you the contents of a selected cell, particularly useful if you want to see a calculation within a cell and not just the output of the calculation.

Spreadsheet = A method of spreading information across a sheet of paper - the screen represents a piece of paper with grid lines.

Workbook = the file you create in Excel.

Worksheet = A page within the Workbook. By default, an Excel Workbook contains 3 worksheets; they are the viewed using tabs along the bottom.

Cells = The grid lines make rectangular boxes - known as cells. Referred to as a letter and a number.

Columns = Cells down a spreadsheet are columns - letters. 

Rows = Cells across the spreadsheet are rows - numbers.

Hide  &  Unhide ➪ Hide Columns from the drop-down menu (or you can just press Alt+HOUC)

Name that ranges in Microsoft excel

 

Name that range!

1. Select all the cells in the range that you intend to name. You can use any of the cell selection techniques that you prefer. When selecting the cells for the named range, be sure to include all the cells that you want selected each time you select its range name.

 2. Click the Name box on the Formula bar. Excel automatically highlights the address of the active cell in the selected range.

3. Type the range name in the Name box and then press Enter. As soon as you start typing, Excel replaces the address of the active cell with the range name that you’re assigning. As soon as you press the Enter key, the name appears in the Name box instead of the cell address of the active cell in the range.

Selecting cells with Go to in Microsoft excel

 

Selecting cells with Go To

1. Select the first cell of the range. This becomes the active cell to which the cell range is anchored.

 2. On the Ribbon, click the Find & Select command button in the Editing group on the Home tab and then choose Go To from its drop-down menu or press Ctrl+G or F5. The Go To dialog box opens. Book II Chapter 2 Worksheets Formatting 3. Type the cell address of the last cell in the range in the Reference text box. If this address is already listed in the Go To list box, you can enter this address in the text box by clicking it in the list box.

4. Hold down the Shift key as you click OK or press Enter to close the Go To dialog box. By holding down Shift as you click OK or press Enter, you select the range between the active cell and the cell whose address you specified in the Reference text box.

Formatting Worksheets in Microsoft excel

 

 Formatting Worksheets

✓ Selecting cell ranges and adjusting column widths and row heights

 ✓ Formatting cell ranges as tables

✓ Assigning number formats

✓ Making alignment, font, border, and pattern changes

✓ Using the Format Painter to quickly copy formatting

 ✓ Formatting cell ranges with Cell Styles

✓ Applying conditional formatting

 

Keys usage in Microsoft excel

 

Keys

Enter                =         Moves the cell pointer down one cell in the selection (moves one cell to the right when the selection consists of a single row)

Shift+Enter        =     Moves the cell pointer up one cell in the selection (moves one cell to the left when the selection consists of a single row)

Tab         =       Moves the cell pointer one cell to the right in the selection (moves one cell down when the selection consists of a single column)

 Shift+Tab         =        Moves the cell pointer one cell to the left in the selection (moves one cell up when the selection consists of a single column)

Ctrl+period (.)        =        Moves the cell pointer from corner to corner of the cell selection.

Saving the Data

 One of the most important tasks you ever perform when building your spreadsheet is saving your work! Excel offers three different ways to invoke the Save command:

✦ Select the Save button on the Quick Access toolbar (the one with the disk icon).

✦ Press Ctrl+S or F12.

Planning your workbook in Excel

 

Planning your workbook

✦ Does the layout of the spreadsheet require the use of data tables

✦ Do these data tables and lists need to be laid out on a single worksheet or can they be placed in the same relative position on multiple worksheets of the workbook (like pages of a book)?

✦ Do the data tables in the spreadsheet use the same type of formulas?

✦ Do some of the columns in the data lists in the spreadsheet get their input from formula calculation or do they get their input from other lists (called lookup tables) in the workbook?

 ✦ Will any of the data in the spreadsheet be graphed, and will these charts appear in the same worksheet (referred to as embedded charts), or will they appear on separate worksheets in the workbook (called chart sheets)?

✦ Does any of the data in the spreadsheet come from worksheets in separate workbook files?

 ✦ How often will the data in the spreadsheet be updated or added to?

 ✦ How much data will the spreadsheet ultimately hold?

✦ Will the data in the spreadsheet be shared primarily in printed or online form?