IG017F16A

From CourseWiki

(Redirected from IG017)
Jump to: navigation, search

Microsoft Excel Level II: Designing & Troubleshooting Excel Workbooks

Overview

Introduction

You have  been working with Excel for a while, (more than six months) and you have experienced some or all  of the following symptoms:

  • Large worksheets are difficult to navigate and manage
  • Work that made perfect sense when you created it is difficult to fathom when revisited
  • Sharing your worksheets with others has led to a new role for you... software support technician
  • You are certain that there have to be better ways to do things, but you haven't got time to find them
  • As you get better at working with Excel, you want to expand your Excel vocabulary so you can recognize new opportunities to apply this tool to your work   

Course Description

This course covers planning and designing worksheets, protecting files, working with large and multiple worksheets, named ranges, built-in and user-defined functions, macros, data functions, pivot tables, and integration for Excel with other Microsoft Office products. The course includes templates that can be modified.  Duration: 12 hours

Register for this course

Theoretical Objectives

  • Worksheet Designers and Worksheet Users
  • Large Worksheets
  • Protecting Your Work
  • Formats / Custom Formats
  • Templates
  • Multiple Sheets - 3 D Issues
  • Documentation
  • Functions (anatomy, applications, user defined)
  • Lookup functions
  • Names
  • Goal Seeking and Solver
  • Scenarios
  • Macros
  • Database operations
  • Charting
  • Pivot Tables

Practical Objectives

  • In addition to Theoretical Objectives
  • Create models and templates

Prerequisites

Excel Introduction or equivalent practical experience.

Computer Equipment

The sessions are conducted in a computer lab, and you will have a computer provided for your use. You are welcome to bring your own computer to the lab sessions if you prefer to use that. Wireless access is available in the lab.

Please note that at times you will be doing a fair bit of typing and you will want to have a large enough screen and separate physical keyboard so that you can view content and type at the same time.

If it appears that you are having challenges with your own device, we will ask you to use the computer provided for you in the lab.

Storage

Please plan to use your own cloud based storage SkyDrive, DropBox, iCloud (or similar) or a USB storage key.

Instructional Methods

  • Hands-on labs
  • Discussion
  • Lecture

Course Materials

  • Web site
  • Course notes
  • Printed copies of on-screen lecture notes available on request during lectures

No Recording or Pictures in the Lab

Please note: Taking pictures or video during the course sessions is not permitted. All the course materials and lecture notes are available in physical form and on the web.

This web page is a resource for participants in this course, a lab oriented workshop.

This is not intended as a standalone tutorial. There is no narrative here.

To be a part of the discussion that accompanies these materials, please come to the course.

Register for this course


Coming Soon
Courses coming

Follow JimUBC on Twitter for latest course updates
Follow JimUBC on Twitter for latest course updates


Locations


This site


Excel II

Survey

Most Important Question


Most Important Question

Welcome Excel II Screen

Contents

Register for this course

  • Current Session IG017F16A
  • 2 Wednesday: November 16 – 23, 2016
  • 9:00am - 4:00pm
  • Lab C460
  • CA: Mandy Lui
  • Students Registered: 15 *
  • Examples, Samples and Notes on OneDrive

Sample Files

Sample files and examples — OneDrive (download files before opening them)

one big zipfile

Introducing OneDrive



Course Evaluation Survey

Office 2013

Ribbon

Image:Excel 2013 User Interrface 01.png


Image:Excel 2013 Home Tab.png


Image:Excel 2013 Insert Tab.png


Image:Excel 2013 Page Layout Tab.png


Image:Excel 2013 Formulas Tab.png


Image:Excel 2013 Data Tab.png


Image:Excel 2013 Review Tab.png


Image:Excel 2013 View Tab.png


Image:Excel 2013 Developer Tab.png


Image:Excel 2013 Load Test Tab.png


Image:Excel 2013 Team Tab.png


Backstage View

Image:Excel 2013 Backstage View.png

Options

Image:Excel 2013 Options.png


Quick Access Toolbar

You can add a command from any Ribbon by right clicking the command and choosing Add to Quick Access Toolbar

Image:Excel 2013 Customize Quick Access Toolbar 01.png


Customize the Quick Access Toolbar by adding a command that is not available on any of the Ribbons.

Image:Excel 2013 Customize Quick Access Toolbar 02.png

Contextual Tabs

Image:Excel 2013 Contextual Tabs CHART TOOLS.png


Gallery

Image:Excel 2013 Gallery of Styles.png


Excel 2010

Excel 2010 User Interface

Excel 2007

Excel 2007 User Interface

Review Exercise

Accordion

Discussion

Image:Decide Workbook 01.jpg

Weighting

Image:Decide Workbook 02.jpg

Formulas

Image:Decide Workbook 03.jpg
G13: =G12/MAX($G$12:$I$12)

Copy / Paste

Percentages G13:

=G12/MAX($G$12:$I$12)

IF Version 1 "Winner"


=IF(G13=100%,"Winner","")

Finished

Image:Decide Workbook 04.jpg

=IF(G13=100%,"Winner","")

Winner / Loser

Image:Decide Workbook 05.jpg

=IF(G13=100%,"Winner",IF(G13=MIN($G$13:$I$13),"Loser",""))

Conditional

Image:Decide Workbook 06.jpg

Copy / Paste 2

IF Version 1 "Winner"


=IF(G13=100%,"Winner","")

Version 2 (Winners and Losers)


=IF(G13=100%,"Winner",IF(G13=MIN($G$13:$I$13),"Loser",""))

Rank


=RANK(G13,$G$13:$I$13)

Choose


=CHOOSE(G15,"First","Second","Third")


Code Snippets

Tables

Table

Insert Table

Image:ExcelInsertTable01.jpg

Table Inserted

Image:ExcelInsertTable02.jpg

Remove Table

  1. Select a cell in the table
  2. Table Design (contextural tab) | Tools Group | Convert to Range

Image:ExcelConvertToRange.jpg


Subtotals

  1. Remove table (convert to range)
  2. Data Tab
    • Sort data by items by which you want subtotals (e.g. Name or Team Name)
  3. Data Tab | Outline | Subtotal
    Image:ExcelSubtotal01.jpg

Subtotals visible as outline

Image:ExcelSubtotal02.jpg

Outline collapsed showing only subtotals

Image:ExcelSubtotal03.jpg

PivotTables

Here is a quick video of creating a PivotTable based on this file:

File: PivotBowlingScores.xlsx

link to video

Image:ExcelInsertPivotTable01.jpg

Image:ExcelInsertPivotTable02.jpg

Image:ExcelInsertPivotTable03.jpg

Image:ExcelInsertPivotTable04.jpg

Image:ExcelInsertPivotTable05.jpg

Image:ExcelInsertPivotTable06.jpg

Pivot Chart Example

Image:Excel Pivot Chart 01.png

Pivot Cheque Register

Note: To make this example work, we need cells that contain Month and Year

G2:
=TEXT(C2,"mmm")
 
H2:
=TEXT(C2,"yyyy")


Image:Excel Pivot0201.png


Image:Excel Pivot0202.png

Pivot Grouping with Dates

1

Image:PivotDateGroups01.png

2

Image:PivotDateGroups02.png

3

Image:PivotDateGroups03.png

4

Image:PivotDateGroups04.png

5

Image:PivotDateGroups05.png

6

Image:PivotDateGroups06.png

7

Image:PivotDateGroups07.png

8

Image:PivotDateGroups08.png

9

Image:PivotDateGroups09.png

10

Image:PivotDateGroups10.png


Power Pivot Data from Several Tables

The sample file for this is Data2Pivot.xlsx. When you open the file look near the top and click Edit Workbook, and then choose Edit in Excel (not Excel online).


For more see What's new in Power Pivot in Microsoft Excel 2013

Link to this Table

Copy this link to your clipboard

https://fms-nquery.finance.ubc.ca/oldstuff/IHRIS/Departmentcodes.htm

In Excel - choose the Data Tab and Import from Web, then paste the link in to the box.


Or

Table to Import


Questions from Previous Sessions

Copy paste from an Outline

How do I copy only the cells that are visible in an outline?
— Shirley

  • Collapse the data range
  • Select the range
  • Use F5, click 'Special'
  • Choose 'Only visible cells'
  • Copy the data
  • Select your destination for the copy
  • Paste the data

Decimal Places and Rounding

How do I force Excel to do calculations based on the data that is displayed (not as it is stored - to 15 digits)?
— Suzyn

Image:PrecisionAsDisplayed.gif

Precision as displayed

Permanently changes stored values in cells from full precision (15 digits) to whatever format, including decimal places, is displayed.


ISERROR


Advanced Filter

Filter by using advanced criteria


Power Pivot Example

The sample file for this is Data2Pivot.xlsx. When you open the file look near the top and click Edit Workbook, and then choose Edit in Excel (not Excel online).

References


Power Pivot for Excel Tutorial — Microsoft

Getting Started with Visual Basic Applications Edition in Excel 2010 — Microsoft

Office Visual Basic Reference — Microsoft




Questions & Answers Specific to this Session

IG017F16A Notes


Resources

Screen Shots

Office 2003 to Office 2007 Quick notes





|Evaluation = Course Evaluation Survey

Personal tools