Microsoft Excel 2016: How to

2 years ago and 1316 Views
All ArticlesExcel 2016Office 2016
Print Friendly, PDF & Email

Click here for the brochure.

 

The Basics

Create a New Workbook

Option 1Click file, Click new, select a workbook

  1. Click File
  2. Click New
  3. Double Click workbook

 

Option 2

  • Press Ctrl + N on your keyboard

 

Open a Workbook

Option 1Click file, Click Open, select your file

  1. Click File
  2. Click Open
  3. Select your file if it’s displayed
    • Browse through various drives or online accounts connected
  4. Press Open

 

Option 2

  • Press Ctrl + O on your keyboard

 

To Undo Click Undo

Option 1

  • Click Undo

 

Option 2

  • Press Ctrl + Z on your keyboard

 

RepeatClick redo

Option 1

  • Click Redo

 

Option 2

  • Press Ctrl + Y on the keyboard

 

Close a Workbook

Option 1Click the close button

  • Click the Close button

 

Option 2

  • Click Ctrl + W on the keyboard

 

Get Help

Option 1Type in the Search bar

  • Press F1

 

Option 2

  • Type in Search bar
    • It says Tell Me What you Want to Do

 

Cell AddressesLook here for the cells address

  • Look at the name box for its address

 

 

 

 

Select a Cell

  • Click or use the keyboard arrows

 

Select a Cell Range

Option 1Select multiple Cells

  • Click and hold the corner and drag

 

Option 2

  • Hold down Shift while clicking desired cells

 

Select an Entire Worksheet

Option 1Select all

  • Click Select All button

 

Option 2

  • Click Ctrl + A on the keyboard

 

Minimize the Ribbon

Option 1Select min

  • Click the Minimize Ribbon

 

Option 2

  • Press Ctrl + F1 on the keyboard

 

Change Program SettingsClick File, Click Options, make changes

  1. Click File
  2. Select Options
  3. Make your Changes
  4. Press the OK button when done

 

 

Enable MacrosClick File, Click Options, CLick Customize Ribbon, Click Developer

  1. Click File
  2. Select Options
  3. Click Customize Ribbon
  4. Check the Developer box
  5. Press the OK button when done

 

Use Zoom

Option 1Use the Zoom bar

  • Click and drag on the Zoom bar
  • or Click the  + & – buttons

 

Option 2

Click Ctrl and – or Ctrl and +

 

­

Change Viewsclick view, select an option

  1. Click the View tab
  2. Select a View from the options
    • Normal: Normal view
    • Page Break Preview: Shows the breaks when printing
    • Page Layout: Shows how it will look when printing
    • Custom Views: Save views for quick access later

 

 

Editing

Edit a Cells Contents

Option 1double click the cell and type

  1. Double-click the cell
  2. Start Typing

 

 

 

Option 2Click in the cell, Click in the Formula bar

  1. Select the cell
  2. Click the Formula Bar
  3. Start Typing

 

 

 

Clear a Cells Contents

Option 1

  1. Select the cell
  2. Press Delete

 

Option 2Highlight the cells, Click Clear, Select Clear Contents

  1. Click the Home tab
  2. Click Clear button
  3. Select Clear Contents option

 

 

 

Cut, Copy and Paste Data

  Cut Copy Paste
Option 1

 

 Press Ctrl + X Press Ctrl + C Press Ctrl + V
Option 2

 

 Click the Cut button Click the Copy button Click the Paste button
Option 3

 

 
  1. Right Click
  2. Click Cut
  1. Right Click
  2. Click Copy
  1. Right Click
  2. Click Paste

 

Preview an Item before PrintingClick File, Click Print, look to the left

  1. Click File
  2. Click Print
  3. Change options as needed before printing

 

 

Paste SpecialClick the down arrow below Paste

  1. Use the Paste button list arrow
  2. Select paste special

 

 

 

Insert a Column or RowRight click the rows or columns, select Insert

Option 1

  1. Right-click the column/row label
  2. Select Insert

 

 

Option 2Click Insert. Select Insert, choose Insert Sheet Rows or Columns

  1. Click the Home tab
  2. Click in the row/column you want to remove
  3. Click the Insert button
  4. Select Insert Sheet Rows or Insert Sheet Columns 

 

 

Delete a Column or Row

Option 1Right click the rows or columns, select Delete

  1. Right-click the column/row label
  2. Select Delete

 

 

 

Option 2Click Delete, Select Delete sheet rows or columns

  1. Click the Home tab
  2. Click in the row/column you want to remove
  3. Click the Delete button
  4. Select Delete Sheet Rows or Delete Sheet Columns 

 

 

Insert a CommentRight click, select Insert Comment

  1. Right click where you want the Comment
  2. Select Insert Comment

 

 

Formulas and Functions

Total a Cell Range

Option 1Click Home, Click AutoSum

  1. Click the cell where you want the total
  2. Click the AutoSum button

 

 

 

Option 2quick icon, Totals, Sum

  1. Highlight the cells you want to be totalled
  2. Click the Quick Analisis icon
  3. Select Totals
  4. Pick Sum or other preferred option

 

 

Enter a Fopress = then type equationrmula

  • Type = then the equation you want

 

 

 

 

Insert a FunctionInsert tab, Function button

  1. Select the cell
  2. Press Insert a Function

 

 

Reference a Cell in a Formula

Option 1type to reference

  • Just type in the cell reference(ex =B5)

 

 

 

 

Option 2click to reference

  • Click the cell to reference

 

 

 

 

 

Create an Absolute Cell ReferenceAbsolute Cell Reference

  • Absolute references do not change when copied or filled.
  • Can use an absolute reference to keep a row and/or column constant.

Option 1

  • Precede the references with $

 

Option 2

  • Press F4 
    • ex. A1 to $A1 to $A$1

 

Formatting

Format Textadjust Font

  • Use the Font options
    • Colour, Size etc.

 

 

 

Format Valuesadjust Number format

  1. Use the Number group
    • Change the display to Time, Currency etc.
  2. Click More for more options

 

 

Copy Formatting with the Format PainterFormat Painter

  1. Select cells you want to copy the format of
  2. Click the Format Painter
  3. Click the cell to change

 

 

Format a Cell Range as a TableFormat as Table

  1. Click Home tab
  2. Highlight the cells for the table
  3. Click Format as Table
  4. Choose the desired design

 

 

Apply Conditional FormattingConditional formatting

  1. Select the cells to edit
  2. Click the Home tab
  3. Click the Conditional Formatting button
  4. Select one of the options
    • Highlight Cells Rules
    • Top/Bottom Rules
    • Data Bars
    • Color Scales
    • Icon Sets

 

Adjust Column Width or Row Height

Option 1click and drag

  • Click and Drag accordingly
    • You can see the sizing while dragging it

 

Option 2

  • Double click it for it to auto-adjust

 

 Option 3Format, Cell size

  1. Click the Home tab
  2. Click the Format button
  3. Select a Sizing Option
    • Row Height
    • AutoFit Row Height
    • Column Width
    • AutoFit Column Width

 

Workbook Management

Insert a New WorksheetClick the New sheet button

  • Click the Insert Worksheet button

 

 

 

Delete a WorksheetRight click the sheet, Select Delete

  1. Right click the tab
  2. Press Delete

 

 

 

Rename a Worksheet

Option 1Right click the sheet name, click rename

  1. Right click the tab
  2. Press Rename
  3. Type in the name

 

 

Option 2 double click the name

  1. Double click the name
  2. Type in the new name

 

 

 

Change a Worksheets Tab colorRight click, select Tab Color, choose a color

  1. Right click the tab
  2. Select Tab Color
  3. Select a preferred color

 

 

Move or Copy a Worksheet

Option 1Click and drag

  • Click and drag the tab

 

Option 2

  • Press Ctrl at then click to copy it

 

Freeze PanesClick view tab, Freeze pane button, select option

  1. Click the View tab
  2. Click Freeze Panes button
  3. Select your option
    • Unfreeze Panes
    • Freeze Top Row
    • Freeze First Column

 

Adjust Page Margins, Orientation, Size and BreaksClick page layout, use the page Setup group

  1. Click Page Layout tab
  2. Use the Page Setup grouping
  3. Click more for more options

 

 

Protect or Share a WorkbookClick review tab, use Changes groupings

  1. Click Review tab
  2. Use the protect options
    • Protect Sheet
    • Protect Workbook
    • Share Workbook
    • Protect and Share
    • Allow Users to Edit Ranges
    • Track Changes

 

Recover AutoSaved VersionsLook under Manage Workbook

  1. Click File
  2. Look under the Manage Workbook section
  3. Select an AutoSaved version

 

 

ChartsUse chart groupings

  1. Click the Insert tab
  2. Pick a chart from the grouping
  3. Click More for more options

 

 

 

Keyboard Shortcuts

Keys Task Keys Task
Ctrl + O Open a workbook Shift + Enter Up One Cell
Ctrl + N Create New Ctrl + Home To Cell A1
Ctrl + S Save F5 Go to Dialog box
Ctrl + P Preview and print Ctrl + X Cut
Ctrl + W Close a Workbook Ctrl + C Copy
F1 Help Ctrl + V Paste
F7 Spelling check Ctrl + Z Undo
F9 Calculate Worksheets Ctrl + Y Redo
F4 Create Reference Ctrl + F Find
Tab Right One Cell Ctrl + A Select All
Shift + Tab Left One Cell F2 Edit Selected Cell
Enter Down Once Cell Delete Clear Contents

Click here for the brochure.