Excel 2010 - Business Basics & Beyond

by Chris Smitty Smith

Overview

Microsoft Excel is one of the most powerful tools a business owner, manager, or new employee has at their disposal, and this guide teaches how to harness business data and put it to use. Using real-world examples of a small business in operation, the book covers topics such as preparing financial statements, how to best display data for maximum impact with formatting tools, data tables, charts and pivot tables, using customer information to create customized letters with mail merge, importing data from programs such as QuickBooks, calculating the costs of doing business with financial formulas, and much more. Helpful screenshots are spread throughout the text, and the book explains how to find ready-made templates online for free.

Product Details

ISBN-13:	9781615473304
Publisher:	Holy Macro! Books
Publication date:	12/01/2012
Sold by:	Barnes & Noble
Format:	eBook
Pages:	279
File size:	48 MB
Note:	This product may take a few minutes to download.

About the Author

Chris Smith is a professional business solutions developer and five-time recipient of the Microsoft MVP award for Excel. He shares his software and programming expertise as a professional speaker at conferences around the United States and as a trainer/lecturer for a wide variety of corporate clients, including the U.S. Department of Treasury, CalTrans, Apple, Verizon, General Electric, and many others. He lives in Boulder Creek, California.

Read an Excerpt

Excel 2010 Business Basics and Beyond

---

By Chris "Smitty" Smith

Holy Macro! Books

Copyright © 2013 Holy Macro! Books
All rights reserved.
ISBN: 978-1-61547-330-4

---

CHAPTER 1

Lesson 1 - What is Excel Anyway?

Microsoft Excel is a spreadsheet application, and has been in use for over 25 years (Application is the proper term for a computer Program). It is one of the most powerful tools in the Microsoft Suite of Office applications. It is estimated to be used by over 500 million people world-wide in business, private and education sectors. Whether you want to use Excel as an integral part of managing your business or just use it for small aspects, like employee scheduling, or maintaining customer lists, this class will prepare you to start using Excel on your own. In addition to the basic elements of Excel that you'll learn, you'll also walk away with tips and tricks that will make your everyday usage more efficient, and there will be lots of links that point you to more resources on the Internet. This is a step-by-step guide to Excel, so we'll walk through everything from how to set up Excel's default settings the way you want them, to how to enter your first formula and create your first chart.

What is a spreadsheet? (Spreadsheet Terminology)

If you've ever seen an accounting ledger, a spreadsheet is essentially a digital version, and Excel is the best and most feature packed spreadsheet application on the market. A spreadsheet is nothing more than a 2-dimensional digital grid of Rows and Columns that are divided by individual Cells, which are capable of housing data and performing calculations. Columns are ordered from left-to-right, and have Column Headers labeled alphabetically from A to XFD; Rows are ordered from top-to-bottom, and have Row Headers labeled numerically from 1 to 1,048,576. Excel 2007/2010 has over 16,000 columns and 1 million+ rows, meaning that there are over 16 billion individual cells on a single spreadsheet in which you can enter data! The Active Cell is the cell in which your cursor is at any given moment. The intersection of the Column & Row headers at the Active Cell makes up the Cell Address. For instance D3, refers to column D, Row 3. The Active Workbook is the one you are working in at the moment; you can have multiple workbooks open at any time, but you can only work in one at a time.

In Excel an individual spreadsheet page is referred to as a Worksheet. (Some people tend to use the term spreadsheet & worksheet interchangeably, but "spreadsheet" refers to the more broad scope of any digital spreadsheet application, and while acceptable, technically isn't accurate). A Worksheet can also be called a "Sheet". A Workbook is a collection of Worksheets (although a Workbook can contain only one Worksheet, it must contain at least one!). Think of a Workbook as a book on your desk, and Worksheets as the individual pages that are between the binding.

There are two distinct layers to any Worksheet, the first is the Worksheet layer, which holds those billions of Cells, then there's an invisible layer above the Worksheet that holds any objects that you insert (Clip Art, Graphics, Charts, etc.) When you insert an object into an Excel worksheet, it doesn't become part of the cells, but instead floats above them, and won't interfere with any values that have been inserted into them.

The only thing you can put into a worksheet cell is text or values, not objects. Note that in Excel 2007 Microsoft introduced some cool new in -cell features that include graphical objects, like Conditional Formatting graphics, Data Bars, and Sparklines in Excel 2010. But these are tools that have been built into the application and should not be confused with objects that reside above the worksheet layer. For instance, you can't copy an image from a website and place it in a cell, it resides above the worksheet.

Ranges – A range is a group of cells. E.G. B3:C7 would refer to the range of cells starting at cell B3, moving down and over to cell C7. Cell ranges are how you refer to areas of a worksheet in formulas.

Selection – Any range of cells you have manually selected. Selected cells will be highlighted:

In this case the range B3:C7 has been selected. Note that cell B3 is listed as the Active Cell, which indicates that's where the selection began. You can select ranges by left-clicking on a cell and dragging. You'll see keyboard shortcuts for selecting ranges later in the lesson.

Marquee – The black box around the active cell or selected range. Note that the Row & Column headers are highlighted to show you where you are. If you only have one cell selected, this will still happen and they'll automatically adjust as you move around.

Dancing Ants – This is a moving Marquee that indicates you have copied a cell or range of cells:

Current Region – Excel knows if you are in a region of contiguous data and will consider that to be the current region. This will come in handy when we get to the navigation section, as keyboard shortcuts will be limited to the current region.

Used Range – Excel automatically recognizes the area in which you've entered data. (This can cause workbook size problems if you have a large range of data and subsequently delete all or a portion of it; just deleting the data won't erase the used range, so Excel will continue to store that in memory. To reduce the size of the Used Range you actually have to delete unused rows and columns, which will be covered later).

What can you do with Excel?

As mentioned, Excel is incredibly powerful, and it is equally diverse with regards to what one can do with it. Here are just a few examples:

• Household

• Electronic Check Register

• Household Budgeting

• Loan Calculators (home, mortgage, etc.)

• Financial Portfolio Tracker

• Manage Fantasy Sports Leagues

• Calendar

• To Do Lists

• Vehicle Maintenance Logs

• Family Tree

• Fitness & Weight Loss

• Tax Planning

• Medical Record Keeping (Blood Pressure, weight, etc.)

• Wedding Planning

• School

• Academic Gradebook

• Calculate Academic Credit Status

• Business

• Invoice

• Employee Scheduling

• Inventory Management

• Expense Reports

• Calculate Product Pricing

• Commission & Compensation Planning

• Complex Financial Analysis

• Balance Sheets

• Profit/Loss Statements

• Sales Analysis

• Budget Tracking

• Forecasting

• Profitability

• Breakeven analysis

• Marketing Planning

• Project Planning/Tracking

• Business Valuation

• Charts

• Create complex diagrams

The Excel Environment – Understanding the Ribbon Interface

What is the Ribbon? The Ribbon is a collection of all of the command and menu elements that are available to you in Excel (and the other primary Office applications). If you're familiar with older versions of Office, the Ribbon has replaced the traditional menu dialogs that were originally designed in 1992.

Why the Ribbon? In 2007 Microsoft completely revamped the look of the primary Office applications when it introduced what is called the Fluent User Interface, more commonly referred to as the Ribbon. This was an attempt to introduce a more cohesive end-user environment to Office applications, and while it was reviled by most experienced Office users, it has proven to be a big hit with newer users. In addition, as Office products have evolved, so have the number of commands available to users (there are hundreds!), so Microsoft had to develop a way to easily expose all of those options graphically, instead of hiding them in more and more layers that the user had to know. In this section we'll start exploring the Ribbon and how to navigate in Workbooks and Worksheets. The next lesson goes into great detail about the Ribbon, so this is just an introduction to help you get familiar with it.

The Ribbon is a collection of Tabs, each of which house a group of menu commands that are all similar in nature. When you first open Excel the Home tab will be activated, and all of its related command Groups are located below. When you activate another tab, the commands specific to that tab will be displayed. The Ribbon consists of the following Tab groups:

• File/Office Button

• Home

• Insert

• Page Layout

• Formulas

• Data

• Review

• View

• Developer

• Add-Ins

• Acrobat (only if Adobe Acrobat is installed)

The final element of the Ribbon is the Dialog Launcher, which is the small button located at the bottom right -hand corner of many Tab Groups. The Dialog Launcher will expand any Tab Group that has too many controls to be efficiently displayed on the Ribbon.

Another addition in Excel 2007 is Galleries, which are pre-defined formats that you can quickly apply to a worksheet. Each Gallery has a drop-down option that will automatically expand the Gallery items related to that Tab Group. Galleries are very similar to Dialog Launchers in that they expose additional menu commands that won't efficiently fit on the Ribbon.

Ribbon Elements

Quick Access Toolbar (QAT) – This is the section that's circled in red in the upper left-hand corner of the example above. This is a toolbar where you can put your favorite menus items to quickly access them. By default it is pre-loaded with Save, Undo & Redo. The down arrow on the right activates another menu with more selections that you can quickly add to the toolbar:

[ILLUSTRATION OMITTED]

There is also a More Commands option, which is where you can further customize the QAT, which we'll discuss in the next lesson with Customizing Excel. Finally, there is a Show Below the Ribbon option that allows you to move the QAT closer to the worksheet. There's no need to do so, it's simply an option.

Tabs File – The File menu is where all of your application level commands are located, like Open, Close, Save, Save As, Send, Print, Workbook Properties, and most importantly your Application Options, where you can change Excel's default behavior to suit your tastes.

Home – The Home tab holds the most commonly used menu items (Text Formatting, Text Alignment, Number Formatting, Cell Styles, Cell Formatting and Editing) housed in the following Groups:

• Clipboard

• Font

• Alignment

• Number

• Styles

• Cells

• Editing

Insert – The Insert tab allows you to insert objects in your worksheet, like Pivot Tables, Charts, Smart Art, Clip Art, etc. It consists of the following Groups:

• Tables

• Illustrations

• Charts

• Sparklines

• Filter

• Links

• Text

• Symbols

Page Layout – The Page Layout tab is where you can apply Themes (pre-defined styles with colors, fonts, and effects) to an entire document, Page Setup options for printing, Sheet options (hide grid-lines, Column & Row headings), as well as tools for ordering objects (alignment, grouping, rotating). Page Layout has the following Groups:

• Themes

• Page Setup

• Scale to Fit

• Sheet Options

• Arrange

Formulas – The Formulas tab is perhaps the most powerful of the Ribbon Tabs, because this is where you can unleash the true power of Excel and its calculation abilities. It groups functions into categories (AutoSum, Insert Function, Recently Used, Financial, Logical, Text, Date & Time, Lookup & Reference, Math & Trig and More Functions). It's comprised of the following groups:

• Function Library

• Defined Names

• Formula Auditing

• Calculation

Data – The Data tab is your gateway to accessing data from external sources, like databases, other Excel workbooks, text files, etc. It also holds several Data Analysis tools, like a Duplicate Removal Wizard and Outlining. It has the following groups:

• Get External Data

• Connections

• Sort & Filter

• Data Tools

• Outline

• Analysis

Review – When you're ready to publish your workbook, this is where you go. The Review tab has all of the editing tools you'll need to make sure that your work is free of grammatical and spelling errors. You can also add comments as visual aids for those receiving your workbook. The Review tab has the following groups:

• Proofing

• Language

• Comments

• Changes

• Share

• View

View – This gives you the flexibility to set all of your viewing options, like if you want to see the worksheet the way it will be printed, display grid-lines or headings, and even view multiple worksheets or workbooks side-by-side.

Developer – When you first open Excel this tab will be disabled (we'll discuss how to enable it in the next lesson when we review how to set up Excel's default options). This class isn't going to delve into programming, but it is worth introducing so that you're aware of it. Within each Office Application resides a powerful application-specific programming language called Visual Basic for Applications (VBA). It is a subset of the Visual Basic program language that many professional programmers use to write applications, and it allows you to harness the power of programming code to automate your tasks in Excel. Any repetitive task you perform in Excel (and other Office Applications) can be automated, so that the program does the work for you. A common example is automatically sending a workbook through Outlook with a press of a button. Fortunately, Microsoft has made it so that you don't have to be a computer programmer to work with VBA, because the Developer tab includes a tool called the Macro Recorder. All you need to do is record a macro, do something in Excel, and when played back, your actions will be repeated exactly as you performed them.

The Developer tab consists of the following groups:

• Code

• Add-Ins

• Controls

• XML

• Modify

Add-Ins – Add-Ins are third party tools (many written by Microsoft) that allow you to access additional functionality that doesn't come native to Excel. Add-ins are generally created with VBA. As a beginner in Excel you might not have any need to use Add-Ins, but there are many add-ins that are created with certain businesses in mind. For instance there is an add-in for carpenters and home builders that can convert cell entries into inch/foot measurements. Similarly, there are enhanced Finance Add-Ins for stock market traders, Statistical Add-Ins for scientists, Metric System converters, and many more. The Add-Ins tab only has one group called Menu Commands. As you add and activate Add-Ins they will be listed in this group.

Acrobat – Adobe Acrobat, which is a powerful application by Adobe Systems for creating PDF documents, automatically installs a Ribbon Tab in Excel and other Office applications. If you don't have Acrobat then it won't appear in your version of Excel.

Additional Options

At the very bottom of the Ribbon you'll see two other dialog windows:

[ILLUSTRATION OMITTED]

The leftmost box is called the Name Box and it will display the address of the cell which is active at the moment. If you happen to have an object like a chart selected, the chart name will be displayed. You can also enter a cell address here to automatically jump to it. E.G. entering "G24" would take you right to cell G24.

The box to the right of it is called the Formula Bar and it will display the value of the active cell.

On the right-hand side of the Ribbon there are several more controls, which are common to all Office applications:

[ILLUSTRATION OMITTED]

The top three controls are for the application itself. The small bar on the left will minimize the application (restore down), the middle will maximize it (restore up), and the Red "X" will Exit.

The lower set of controls is specific to the worksheet and will do the same thing as their application counterparts.

The button to pay the most attention to is the one on the lower left, the Caret ("^"). This will minimize the Ribbon in the event you need additional space to see more of the worksheet. Activating any Ribbon Tab group will temporarily restore that group's Ribbon controls, and will automatically re-hide when you click off of it. This is a good control with which to be familiar as many people have gotten confounded when the Ribbon "disappears", and they don't know how to restore it!

(Continues...)

---

Excerpted from Excel 2010 Business Basics and Beyond by Chris "Smitty" Smith. Copyright © 2013 Holy Macro! Books. Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

---

Table of Contents

Introduction to Excel - Getting Started
Basic File Operations & Setting up Excel the Way You Want It
The Ribbon In-Depth - The Home Tab
The Ribbon - Part II
Entering and Manipulating Data (and the basic rules of good spreadsheet design)
Using Functions & Formulas
Formatting & Printing
Working with Graphics
Charts
Excel Tables & Sub-Totals
Pivot Tables
External Data Sources & Mail Merge