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.

1.1.jpg 

Figure 1  

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.

1.2.jpg 

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.

1.3.jpg 

Figure 2  

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:

1.4.jpg 

Figure 3  

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:

1.5.jpg 1.6.jpg 

1.7.jpg 

1.10.jpg1.8.jpg1.9.jpg 

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:

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.

1.11.jpg 

Figure 4  

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:

1.12.jpg 

Figure 5  

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.

1.13.jpg 1.14.PNG 

Figure 6  

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:

1.15.PNG 

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:

1.16.jpg 

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:

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:

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:

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:

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:

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:

1.17.jpg 

Figure 7  

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:

1.18.jpg 

Figure 8  

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!

Finally the question mark will launch the Excel Help file. It can also be accessed by pressing the F1 key.

Worksheet Tabs, Status Bar & View Buttons

There is an additional set of tools at the bottom of the Excel screen:

1.19.jpg 

1.20.jpg 

Figure 9  

Worksheet Navigation – This is a series of 4 buttons. The first will scroll to the first worksheet in the workbook, the second will take you to the next sheet to the left, the next will take you to the next sheet on the right, and the last one will take you to the last sheet in the workbook. You can right click it for a snapshot list of all the worksheets in your workbook. Clicking on a worksheet name will automatically activate it:

1.21.jpg 

Figure 10  

Worksheet Tabs – This is a list of each worksheet in your workbook. Clicking on one will activate that worksheet.

Slider Control – You can drag this to the left/right to minimize or expand the horizontal scroll bar. It’s useful when you have a lot of worksheets and you want to see as many as possible.

Horizontal Scroll Bar – Dragging this bar will take you to the end of your data. It goes left & right.

Vertical Scroll Bar (not shown, but it’s on the right side of the worksheet window) – It’s got the same functionality as the Horizontal Scroll Bar, but up and down.

Status Bar – This displays messages to you regarding Excel’s state (Ready, Calculate, etc.). If you right-click it you’ll be presented with a list of options you can choose to display:

1.22.jpg 

Figure 11  

It also has some neat functionality you’ll discover if you select a range of numerical data.

Macro Status – You can record a new macro by clicking on this button. When you do it will change symbols to a square. Clicking it again will stop recording. Again, this isn’t really relevant to this class, but you should know what it does.

View Options – There are three options here:

Zoom Controls – This is where you can increase or decrease the amount of worksheet you see. It has 4 elements:

Navigating the Excel Environment

All of the Ribbon’s Tab controls are mouse activated. However they can also be activated and used from the keyboard. Pressing either of the ALT keys (left and right of the Space bar), or the forward slash key “/ “ (it’s the same key as the Question Mark “?”), will cause the Ribbon to appear with letters beneath each Tab group. Pressing the corresponding key will activate that tab. You’ll note that when that Tab group is then activated there will be a second set of letters beneath each menu control. Pressing the corresponding letter will then activate that menu command. If you are a keyboard oriented user then you’ll soon find this feature to be invaluable (The figure below is an example of 2007).

1.23.jpg 

Figure 12  

There are many ways to navigate within Excel, including the options listed in the previous section, and you can left-click on any cell to activate it; however these actions all require using the mouse. It’s generally much more efficient to get around in Excel by using the keyboard.

Keyboard Shortcuts

Capture.PNG 

Getting Help & Additional Resources

In previous versions of Office, the Helpfiles were only available directly on your PC. This was great, but unfortunately, a lot of IT/IS departments chose not to install the Helpfiles because of size concerns. When Microsoft released Office 2007, Excel and the other Office programs’ internal Helpfiles were somewhat less useful than before, but Microsoft has taken great strides in making the Helpfile offerings much more robust by integrating them with online content from Microsoft. When you select the Helpfile (Question Mark above the Ribbon, or the F1 key), you’ll be presented with an initial dialog like this:

1.25.jpg 

Figure 13  

1.24.jpg 

If you don’t want Excel to look online, or if you don’t have a good Internet connection, you can choose to enable Offline help from your computer. Note that you have to have the Helpfiles installed locally on your computer in order to have this option. If you do select this option, here is the dialog you’ll get:

1.26.jpg 

Figure 14  

Search Locally - This option will return a list of all relevant articles based on the topic you enter. The difficult thing with the Helpfile is actually knowing the terminology for what you want to find. Excel has very specific function names, so sometimes if you don’t know what you’re looking for the Helpfile can’t help you. Fortunately, Microsoft has provided some alternatives to help you narrow your search.

Broad Search Topics – Microsoft has gotten better about anticipating that users won’t always know what they’re looking for, so they added a broad search function, which will bring up featured discussions. Hopefully as you browse through them you’ll get a better idea of the topic for which you’re searching. In addition, those articles with have hyperlinks to any sub-topics mentioned therein, so you don’t have to go find a reference and start a new search for that topic.

Search Status – In both online & offline search modes, the Helpfile dialog will let you know which platform you’re accessing at that time.

Online Search – If you opt to have the Helpfile load online content, you’ll get a dialog like the following figure:

1.27.jpg 

Figure 15  

Search Office Online Articles – Again, the Helpfile can only be as specific as you are with your search terms.

Search with Microsoft Bing – This is a direct search through Microsoft’s new search engine, Bing. While the online search method through the Helpfile will only return articles directly from Microsoft, the Bing search will return information from all relevant Internet sources. Note that Microsoft has gone to great lengths to increase the strength of their help offerings, so this will continue to be a growing resource.

Get Started Articles – Again, the Helpfile gives you some generic help topics from which to choose.

Browse Support – If you absolutely can’t figure out how to find what you need then browse the list of support articles. It might take some time, but with some effort you can generally find a link that will help you get there.

Unit Summary: Getting Started

Review Questions

1. What is a spreadsheet?

a. __________________________________________________

2. Name the five primary components of an Excel file:

a. __________________________________________________

b. __________________________________________________

c. __________________________________________________

d. __________________________________________________

e. __________________________________________________

3. What is the Ribbon?

a. __________________________________________________

4. Name at least two of the Ribbon Tabs:

a. __________________________________________________

b. __________________________________________________

5. Name at least two Groups that belong to those Ribbon Tabs:

a. __________________________________________________

b. __________________________________________________

c. __________________________________________________

d. __________________________________________________

6. Name two ways to move between Excel Worksheets

a. __________________________________________________

b. __________________________________________________

Lesson Assignment

Your first assignment is to open a new Excel workbook and start getting familiarized with the following (there is a Notes section below for you to keep track of your observations):