Learn Excel with the Quality Scorecard

Learn Excel

With the Quality Scorecard

Ronald Goulden, MBA, PMP

Smashwords Edition

Copyright © 2011 Ronald Goulden, MBA, PMP

ALL RIGHTS RESERVED

This ebook is licensed for your personal enjoyment only. This ebook may not be re-sold or given away to other people. If you would like to share this book with another person, please purchase an additional copy for each recipient. If you’re reading this book and did not purchase it, or it was not purchased for your use only, then please return to your favorite ebook retailer and purchase your own copy. Thank you for respecting the hard work of this author.

Cover design by Ronald Goulden

Microsoft® Excel is a registered trademark of the Microsoft Corporation.

Table of Contents

Getting Started

Titles and Headers

Borders and Formatting

Formula

Graphing

Testing the Scorecard

Trapping Errors

Final Thoughts

Inserting a Picture

Inserting a Smart Symbol

Quality by Design

Additional Works

Introduction

There are a lot of books teaching Microsoft® Excel. This is yet another such book though one with a bit of a twist. The process used by this book will start with a blank worksheet and develop it into a sophisticated Project Management tool called the ‘Quality Score Card’.

Do not be alarmed if you are not a Project Manager, the Score Card can be used in other areas, limited only by your imagination. At the very least, this exercise will illustrate the capabilities of Excel and teach some valuable techniques in the process.

The examples in this book are based on Excel 2003 and tested with Excel 2007.

Excel 2007 notes will be italicized and in Franklin Gothic font.

There is no attempt to address the truly unique capabilities of each version of Excel. The goal is to provide fundamental Excel tips and techniques.

Since the functions used in these exercises are pretty standard, they should apply to any spreadsheet application, with slight variations in form and location.

Each chapter builds on previous work so that by the end of the book, you will have a fully functional (and modifiable) Quality Score Card. However, the concepts you learn here will be valuable in any context.

The final chapter discusses the concept of ‘Quality by Design’, which is the foundation of the Quality Score Card.

This is the first of a series of what I call ‘Build-a-Tool’ modules that allow the user to learn Excel techniques while developing fully functional Project Management tools (which may be applicable for other disciplines.)

These modules may ultimately be combined into a single integrated spreadsheet that adheres to the concept of ‘enter once, use many’ to avoid repetitive and error-prone data entry.

Conventions used in this book

Chapter 1

Getting Started

The first step we need to do is start Excel. Excel can be started.

From the Microsoft Office Toolbar.

From a shortcut on your desktop.

By selecting the Start button located at the bottom left hand side of your screen and selecting Programs then Microsoft Excel.

When you start Excel, it should open a blank workbook with two or three blank spreadsheets. If it does not,

[To display the File menus in Excel 2007, {LEFT-CLICK} the Windows logo in the upper left corner.]

{LEFT-CLICK} the File menu option.

{LEFT-CLICK} New.

{LEFT-CLICK} Blank Workbook.

Note: In some versions of Excel, it may be necessary to {DOUBLE-CLICK} Blank Workbook.

Figure 1-1 Excel Menu, Tool, and Formula Bars

At first glance, an Excel worksheet can be intimidating. However, keep in mind that much of the functionality displayed on the Menu bar and the Tool bars will not be used.

Depending upon your version of Excel, Figure 1-1 may look different. Do not be concerned, the functionality will be the same.

Menu Bar

With that in mind, we can divide the tools (the Menu bar and the Tool bars) into their relevant components for discussion. The Menu bar provides common functions normally seen with other Office applications (File, Edit, View, Insert, etc.).

Figure 1-2 Menu Bar

Bear in mind that the Menu bar for your version of Excel may not match exactly what is displayed in this example, depending upon version, configurations and add-ins.

Tool Bars

Figure 1-3 Tool Bars

Again, what you see on your version of Excel may be different from this example, depending upon your version of Excel and what Tools you have enabled.

Formula Bar

Figure 1-4 Formula Bar

The Formula bar allows you to enter text, numbers, and formulae into specific cells on the spreadsheet. There are two parts of the Formula bar, the Function button (fx) and the formula entry to the right of the Function button

Many buttons in Excel look like pictures, or icons. These are clickable and work exactly like any other on-screen button.

The working area of an Excel Worksheet is made up of cells.

Cells

Figure 1-5 Excel Cells (A1)

The cells of a spreadsheet are where the work is performed. While the design may appear confusing, it is really quite simple to navigate a spreadsheet.

Rows are indicated by numbers along the left side of the spreadsheet. Columns are represented by letters along the top of the spreadsheet. With this system, any single cell can be referenced as a simple Column/Row designation (Letter/Number). The default cell is always cell A1, which is the top row/leftmost column.

In Figure 1-6, cell C4 is being referenced.

Figure 1-6 Excel Cells - C4

Note that there are three pieces of information to indicate your location in the spreadsheet.

First, the cell itself is surrounded by a dark, outlining box.

Second, the left portion of the Formula bar says C4. (In the previous Figure, it was A1.)

Third, row number 4 along the left is shaded, as is column C across the top.

Tabs

What most people call a spreadsheet is actually an Excel workbook, which is nothing more than a series of spreadsheet pages (or worksheets) combined in a single document, or file.