Lesson 4 – The Ribbon In-Depth - Part II

In the last lesson two lessons we explored two of the primary Ribbon elements, the File group lesson showed you all of the ways that you can customize the Excel environment so that it’s right for you. The Home group lesson detailed all of the most commonly used Ribbon elements. In this lesson we’ll explore the rest of the Ribbon elements. These elements are segmented by their overall function, like Page Layout and Formulas.

Note: all commands that show you which sequence you would follow to invoke a certain Ribbon element will still be indicated with “goto Home, Format as Table”, however the main Ribbon name won’t be included. It’s implied that if you’re in the Insert section of this lesson that the sequence would begin on the Insert tab. E.G., Table.

Insert

This group contains elements that primarily deal with objects you’ll insert on in and onto the worksheet (Pivot Tables, Charts, Pictures, Smart Art, etc.).

Pivot Tables

• • Pivot Tables are one of your most powerful data analysis tools, and as such they will be discussed in greater detail on their own later on in the course. They allow you to quickly get different looks at your data in a manner that would otherwise only be available through a database. Using the data you’ll see in Tables example, Pivot Tables allow you to take transactional data (like customer orders) from single entries and turn them into complex and dynamic summarizations like this:

 

Figure 123  

Tables

• • Tables are a feature that was introduced in Excel 2007. Originally based on what was called “Lists” in earlier versions, Tables are a great way to automatically format and extend data ranges and formulas as you add data. Most importantly, Tables allow you to work with the data in the table independent of any other data on the worksheet. Of note is that Tables introduce a new function nomenclature that refers to individual column elements in a manner that is nothing like the general worksheet functions that you’ll be working with later in the course.
• • Tables let you change data that looks like this:

 

Figure 124  

• • Do this, in just 2 mouse clicks!

 

Figure 125  

• • All you do is make sure that the active cell is within the data range (it doesn’t matter where), and goto, Table, then select the Table style you like from the Gallery. Excel will automatically convert your data range into a Table.

 

• • What’s neat about Tables is that if you were to add data in row 15 in the above example, Excel would automatically extend the formatting to the new row as soon as you made an entry in column A. What you can’t see is that behind the scenes, Excel has defined the entire table area as a specific range, so it knows exactly where it starts and ends. This comes into play when you add functions to the table, as Excel also automatically extends the table range. In this example if you were to goto cell J2 and invoke the AutoSum wizard you would see Excel build this function:

 

Figure 126  

• • As soon as you confirm the Wizard’s solution (Enter) the table would automatically reformat itself, and extend the new Sum function to the entire column range:

 

Figure 127  

• • This is just an example of what can be done, but the function wizard can only be so smart, and in this case it included the entire numeric range in the Sum function: =SUM(Table1[@[Quantity]:[Profit]]), which is written in the Table function nomenclature. Obviously you wouldn’t want the Quantity included in the sum, and you would subtract Cost of Goods Sold from Profit (=[@Revenue]-[@COGS]).

Illustrations

• • This group contains all of the design objects you can place on a worksheet:
• • Pictures – Insert a picture from a location of your choice. It has to be a picture format that Office can read, like .jpeg, .gif, .bmp, etc.
• • Clipart – Insert an image from the Office Clipart gallery (note that this requires you to have installed the Clipart gallery when you installed Office. Otherwise you need to select the “Find more at Office.com” option at the bottom of the dialog). You also have a selection for the type of Clip Art you want to insert:

 

Figure 128  

• • Shapes – You can select from a huge selection of shapes that you can resize, format (you can even add text to some of them). These are also referred to as “Drawing Objects”. Select the shape you want to insert (notice that as soon as you hover your cursor over the worksheet it turns into a cross), then left-click on the worksheet wherever you want to place the shape and drag it to the vertical/horizontal size that you want (don’t worry, it can be adjusted at any time), then release the button.

 

Figure 129  

• • As soon as you draw a shape you’ll see the Ribbon change to show the Drawing Tools Ribbon Group:

 

Figure 130  

• • The Drawing tools group contains the following elements:
• • Insert Shapes – This is somewhat redundant, since you just inserted a shape, and if you click off of the shape the Ribbon will revert back to the insert group, but it does contain the Edit Shape tool, which can be useful if you want to manipulate a drawn shape more than what Excel gave you.
• • Shape Styles – This is where Microsoft’s graphic artists have done an outstanding job of giving you some fantastic options. There are too many ways to customize these objects to cover here, but you’re encouraged to draw some and see what kind of formats you can come up with (if you want to add text to a shape, just right-click it and select the Edit Text option):

 

Figure 131  

• • Word Art Styles – These options only apply to text, although you can apply them to text you’ve embedded in a shape via the Edit Text option. There is also a Word Art command on the Ribbon, which we’ll get to momentarily. Again, there are too many potential combinations to possibly cover here, so you’re encouraged to work on some of your own.

 

Figure 132  

• • Arrange – Arrange options are relatively straightforward:

 

Figure 133  

• • Bring Forward/Send Backward – If you place objects on top of each other you can use these options to determine which order they’re in. For instance you might draw a callout on a chart, but Excel decides to place it beneath the chart instead of on top. With the callout selected you would just click Bring to Front from the drop-down. Forward/Back are median steps that only apply if you have three or more shapes.
• • Selection Pane – This just lists all of the shapes you have on the worksheet and allows you to order them in the list. It’s not likely that you’ll ever need to use this:
• • Align – This, on the other hand, you’ll use frequently if you have 2 of more shapes on the worksheet. It allows you to easily position shapes in relation to each other without having to move them around manually. In the previous shape examples illustration the three shapes on the left were Aligned Center and Distributed Horizontally, which is a lot easier (not to mention faster) than trying to position them by yourself.
• • Group – Once you’ve got your shapes where you want in relation to each other, it’s a good idea to select all of them (you can use Shift + Left-Click to select multiple shapes, then select Group. This will keep the objects together.

 

• • Size – Shows you the Height and Width of the shape. It’s good reference and if you need to make sure that multiple shapes are the same size, it’s often easier to adjust them all here, rather than try to get them the same size with their handles.

 

Figure 134  

• • SmartArt – This option exposes more pre-designed graphic elements that Microsoft added with Excel 2007. What you used to have to create in another application, like Adobe Illustrator or Microsoft Visio, you can now draw directly in Excel.

 

Figure 135  

• • Here is an example of a Basic Cycle

 

Figure 136  

• • When you first draw a SmartArt graphic you’ll see the Text entry flyout to the left of the graphic. Once you’re done entering your data and click off of the graphic, the flyout will disappear. As with other detailed properties in Excel, SmartArt will activate its own Ribbon sub-group:

 

Figure 137  

• • You’ll see that you can get pretty fancy with the Layout & Style options that you have at your fingertips. And another nice thing is that Live Preview will kick in as you hover over different Layouts and Styles, so you don’t need to commit to a selection before seeing how it’s going to look on your worksheet.

 

Figure 138  

• • Screenshot- This allows you to insert a screenshot of any open application that has not been minimized to the Taskbar. Here is a Screenshot from this lesson. Is this all that practical in Excel? Not really, but it does come in handy in Word, especially if you’re trying to document a process flow for a spreadsheet, or create a user’s guide.

 

Figure 139  

Charts

• • Charts are where you convert your numerical data into graphical representations that give you at-a-glance information about what your detail figures say. There are six primary chart types (Column, Line, Pie, Bar, Area and Scatter), as well as several other non-standard types (Stock, Surface, Doughnut, Bubble and Radar).

 

Figure 140  

• • To draw a chart, just put your cursor somewhere inside of the data set that you want charted, then select the chart type you want. *There are entire books devoted to charting, so it will take some time with your own data to determine which chart is best to suit your needs. In many cases, you’ll use different chart types to display the same data, just send a different message.

 

Figure 141  

 

• • Column Charts – The most common are 2-D & 3-D. Sometimes a Cylinder chart is appropriate, but rarely. Cone & Pyramid shapes should never be used, because they don’t always accurately represent your data. Column charts display your data in vertical columns. Column charts are especially good at displaying multiple data points for several groups, such as Revenue & Profit by company.

 

Figure 142  

• • Line Charts – Probably as common as Column charts, Line charts will display your data in a series of horizontal lines. Line charts are generally used to show data over a given period, like weeks or months.

 

Figure 143  

• • Pie Charts – While these are also relatively common, their use isn’t recommended because they can inaccurately represent data from a visual perspective, especially 3-D pie charts due to the way that they render when drawn, where the bottom/front part of the chart can look bigger than it actually is because of pixel density.

 

Figure 144  

• • Bar Charts – These are nothing more than column charts turned on their side.

 

Figure 145  

• • Area Charts – These allow you to compare like data over a given timeline. They generally obscure more information than they show, and should only be used if there are enough differences between the data sets to differentiate them well.

 

Figure 146  

• • Scatter Charts – These are good for comparing pairs of data.

 

Figure 147  

Sparklines

• • This is a neat addition that the Microsoft Excel team has had in development for a long time, and it is a truly remarkable achievement. In essence Sparklines allow you to insert mini charts in a cell that represents the data you’ve selected (it doesn’t have to be adjacent to the data, but it’s a generally a good idea, so that you maintain a visual reference to it). Here are some Sparkline examples using the same chart data from above:

 

Figure 148  

• • Sparklines are great for Dashboards and summary reports, where a large chart (or a series of them) might be overwhelming.
• • To insert a Sparkline, just select the data range, then the Sparkline type (you can choose from Line, Column or Win/Loss) – Note that Sparklines can only be placed in a single cell per data range, they can’t span multiple rows or columns:

 

Figure 149  

• • As soon as you place the Sparkline on your sheet, the Sparkline Design Tools Ribbon tab will be activated.
• • Here you can edit the Data points, change the Sparkline type, Show certain data points, as well as select a Style for the Sparkline. Once again, you have too many options to detail here, but feel free to experiment with the examples in the Lesson 4 workbook, or create your own. Here’s an example of Sparklines with High & Low points marked.

 

Figure 150  

Filter

• • The Filter group only contains one control: Slicer. A Slicer is a control that allows you to query certain Pivot Table elements. We’ll be discussing this in more detail in the Pivot Tables lesson.

Links

• • This group also contains only one element: Hyperlinks. Hyperlinks allow you to insert a clickable navigation text link that will take you to a website, a different location in your workbook/worksheet, or even open another program.

 

Figure 151  

If by design you want to have hyperlinks in a workbook, they can be somewhat handy. For instance you can create a Table of Contents that lists all worksheets and include links to them. But they can also be irritating, especially as Excel’s default nature is to insert a hyperlink for any text that includes the @ symbol in the text string. You’ll notice this right away if you try to enter an e-mail address in a cell. When adding a hyperlink, you can add an input mask (see the Text to display dialog), which will allow you to add a hyperlink without what might be an otherwise confusing or long address (e.g. you can put “Link to Budget Documents” vs.
http://www.somewebsite.com/documentfolder/financials/monthend/january.html).

Text

• • This contains the following elements: Text Box, Header & Footer, WordArt, Signature Line and Object.
• • Text Box – This is a free form, floating text box that resides above the worksheet. The “sticky Notes” you see throughout this course have been created with the Word version of Text Boxes. There’s generally very little use for these in Excel unless you’re trying to mark a distribution document with a release note, like “DRAFT” or “CONFIDENTIAL”. As with any other object, Text Boxes won’t interfere with the underlying cells or their performance.

 

Figure 152  

• • Once you click the TextBox control the cursor will turn into an inverted cross. Once you see that just left-click and drag across the sheet until you have the box the approximate size you want, then insert your text. Once you’re done with that you can apply any formatting you want to it.

 

• • Header & Footer – This is the same as the Header & Footer options you’ll find in Page Setup, but it lets you input them directly on the worksheet, so you can see a “preview” version of what they’ll look like. It can save a step or two from inputting this data through the Page Setup dialog, where you’ll have to goto Page Layout, Page Setup, Header/Footer (make your additions), Print Preview to see the result. By the same token, all this does is expose the Header & Footer, but not the other Page Setup elements, so what you gain on one side, you lose on the other, because you still need to go through the Page Setup process in order to prepare your worksheet for printing. In addition, what you enter with this method is only viewable through the Page Setup view; you won’t see it in Normal view.
• • Word Art – This was covered briefly in the Illustrations section, and gave you an example of what you can do with some of the new graphic controls Microsoft has created. WordArt can be great for introductory worksheets and on Dashboards (ample room permitting), although you won’t find it widely used in financial applications. When you select the WordArt option you’ll be presented with a series of styles:

 

Figure 153  

• • Once you make your selection, Excel will automatically insert a WordArt text box for you. All you need to do is start typing your own text. As soon as the WordArt text box has been drawn you’ll see the Drawing Tools Format tab appear, where you can format both the text and the text box itself.
• • Signature Line – This was Microsoft’s attempt at adding digital signature security to your documents, but it’s very unlikely that you’ll ever use this feature to its full capacity as it requires a third-party authentication service, and they’re not cheap. It will however allow you to add a signature line to a document faster than if you could draw it yourself. You can dismiss the first notification:

 

Figure 154  

• • Then continue to the Signature dialog and enter your relevant signature information:

 

Figure 155  

• • Object – This dialog allows you to insert an embedded object from another application, like Word. You generally won’t use this in Excel, but you will often insert Excel objects into Word documents, like Monthly Reports.

 

Figure 156  

• • The primary exception here is the Organization Chart tool, which can be a very handy tool for documenting your organizational structure.

 

Figure 157  

Symbols

• • Equation – If you deal with mathematics, then this is a great tool and is a far cry from the day when complicated equations really couldn’t be done in Excel. When you click on the Equation command you’ll see a list of detailed equation options.
• • Selecting one will place it on the sheet and activate the Equation Editing tab. Writing equations could be a topic in and of itself, but for the purposes of this course we’re not going to go over it, other than to show you that it’s there.

 

 

Figure 158  

• • Symbols – You’ll probably find more day-to-day functionality on the Symbols menu, which gives you different symbols for every font you have installed on your system, as well as a series of Special Characters, like Trademark ™ & Copyright ©. Again, there are too many options to possibly cover here, but feel free to explore.

 

Figure 159  

 

Page Layout

This is where you can apply themes to an entire workbook, as well as preparing your worksheet for printing and distribution. The Page Layout tab consists of the following groups:

 

Figure 160  

• • Themes – Themes are a series of pre-defined formats that you can apply to an entire workbook. In the Theme menu to the right you’ll see that the default Theme is “Office”. If you were to change it and go to format a data range as a Table you’d see the new Theme applied there. Microsoft can only give you so many themes out of the thousands of potential possibilities, but you can create your own theme, or adjust existing themes. You can change the Colors, Fonts and Effects (which deal with SmartArt), which you’ll see in the next three examples.

 

Figure 161  

• • Page Setup – This is where you set up your worksheets so that they print in the format you want. The Page Setup group consists of the following commands:
• • Margins – Displays the most common margins as well as the one you used most recently if it’s not one of the defaults. The Customize Margins will bring up the Excel 2003 Page Setup dialog, the same as the dialog launcher on the Ribbon’s Page Setup group.

 

Figure 162  

• • Orientation – This allows you to quickly toggle between Portrait and Landscape orientation. As you can see in the Margin selections, the example worksheet is in Portrait orientation, which is the default. Had the worksheet been in Landscape orientation, the Margin images would have reflected that.
• • Size – This lets you choose from the most common paper sizes. The default is 8.5” x 11”.

 

Figure 163  

• • Print Area – This is something that often trips up even the most experienced users. Let’s say you have a worksheet, but you only want to print part of the worksheet and not reveal certain parts of it? Excel automatically adds the entire used range into the print area, so if you try printing without checking this first you’ll realize pretty quickly. The first step is to Clear the Print Area, then select the area you do want to print and select the Set Print Area option, which will then leave out what you don’t want to print.
• • Breaks – This allows you to select a Row or Column and set a Page Break. Unfortunately, this isn’t a very useful feature and is much easier handled in Page Break Preview, where you can just drag breaks where you want them.
• • Background – This allows you to take an image and insert it into the background of your worksheet, and is the only thing you can actually put behind the worksheet object. It’s very useful if you need to add something like a company logo as a watermark, or want to have an intro page, but you need to be careful with the image you select as it can get very overwhelming and make your worksheet all but impossible to work with if you’re not careful. The image also gets tiled (repeats) across the entire worksheet, so it’s not a display in one spot thing, it’s all or none. You also only get one image, not multiple, so if you want to layer a company logo or photo in the background and add a “DRAFT” or “COMPANY CONFIDENTIAL” label as well, you’d need to prepare that in a photo editing program (Paint.Net is a good one and it’s free), then import the finished image.
• • Note the difference in the two Background examples with and without Gridlines displayed.

 

Figure 164  

• • Print Titles – Again, this launches the Excel 2003 Page Setup dialog. Print Titles gives you the ability to fix columns and rows so that they repeat on each worksheet. We’ll discuss all of these print options in the Page Setup lesson.
• • Dialog Launcher – This will call the Excel 2003 Page Setup dialog, which encompasses all of the Page Setup items you’ll need. Unless you’re just quickly changing a setting like Margins or Orientation, you’ll probably find it easier to come to this dialog to set up your worksheet for printing.
• • Scale to Fit – This lets you choose how much of your sheet to print and on how many pages. You can select Automatic, where Excel will choose for you (remember the part about setting the Print Area here), or you can define how many pages Wide by how many pages Tall the sheet should print. The Scale to Fit Dialog Launcher will bring up the Excel 2003 Page Setup dialog. Are you noticing a common theme here? For whatever reason, Microsoft didn’t redesign the Excel 2003 dialogs, but why try to fix what already works so well?
• • Sheet Options – This is a simple dialog that lets you toggle Gridlines and Headings (the Row & Column headers). If you’re distributing a workbook that’s for viewing only, you’ll probably toggle these off, but if you’re expecting some degree of user interaction you’ll leave them on to make it easier for your users to navigate your worksheet(s).
• • Arrange – The Page Layout tab is a somewhat illogical place for this option since it deals with objects, not so much Page Layout. But it’s another case where Microsoft gives you the option to interact with different menu items in different places.

 

Figure 165  

Formulas

This is where you can access all of Excel’s native Formulas (also referred to as Functions). Formulas allow to you calculate values that you enter into cells, like =1+1, or =TODAY() which would return the date in the cell. With regards to data analysis and getting the most from your data, this is where Excel really shines. Even if all you’re ever going to do is use Excel to manage lists (like customer details), you’ll still find at least a few of these handy. Once you start getting used to Formulas, you’ll wonder how you ever got things done without Excel. We’re going to review the Formula Ribbon functionality here, but because there are so many great tools here, Formulas will also be discussed in their own lesson. The Formula tab consists of the following groups:

 

Figure 166  

• • Function Library – This group breaks down Excel formulas into the most commonly used groups.
• • Insert Function – This will launch the Excel 2003 Function dialog, which exposes all of the Function categories you see on the Ribbon. At the top you have the ability to search for a function if you don’t know it. For instance “Look up a value” will give you a list of LOOKUP formulas. This is a useful tool, but only to the extent that you know how to define what you’re looking for, otherwise it can be fairly frustrating. If you know roughly what you want to do you can narrow the list, just like you can on the Ribbon by selecting from one of the defined categories. Once you select a formula, Excel will define its syntax on the bottom, and you can also select the “Help on this function” link at the bottom, which will open the Helpfile. Once you select the formula you want, Excel will launch a Function Arguments dialog for you that details the formula:

 

Figure 167  

• • AutoSum – While some people strictly write their own formulas, there are many times where Excel can do it faster than if you could yourself, and when you’re talking about sometimes entering literally thousands of formulas it can be really convenient to let Excel do it for you. The following example used the Sum function to Sum revenue for a particular sales person:

 

Figure 168  

• • With the cursor in cell N2, just select Sum from the AutoSum list and Excel will automatically apply the formula for you (you’ll see it in the cell and in the Formula Bar). Note Excel it highlights the range where it thinks you want the formula but doesn’t enter it, instead it wants you to verify that it got it right. If you’re happy with Excel’s selection, then just hit enter, then you can copy & paste the formula down to the rest of the sales people. In this case the cursor was to the right of the data, so Excel knew to go left; had you been underneath the data, Excel would have summed upwards. Will the AutoSum wizard always get it right? No, but it does a pretty good job.
• • Recently Used – Just what it sounds like, Excel will store a list of the last 10 formulas you’ve used. Unless you’re frequently dealing with formulas that have long names or are easy to misspell (like AMORDEGC), you probably won’t use this very often.
• • Financial – This deals with finance functions, like calculating payments on equipment at a given rate.
• • Logical – These are used quite frequently to tell Excel to do something if a condition is met. E.G. =IF(A1=1,1,2), which simply says IF A1 = 1, then the formula should return a 1, otherwise return a 2.
• • Text – These allow you to do things like change text case (Upper, Lower & Proper), split text apart (Parse) or join (Concatenate) text from separate cells. Lets’ say you have “SMITH, JOHN” in a cell and you want to get the first & last names. You could use a few text functions to do all of that for you instead of retyping the information. There will be examples of this in the Formulas lesson.

 

Figure 169  

• • Date & Time – These let you not only enter dynamic dates and times into your worksheet, but allow you to perform calculate on them, like calculating the number of years between and employee’s start date and today’s date.
• • Lookup & Reference – These are perhaps some of the most powerful formulas you have at your disposal. They let you store data in one place and retrieve it somewhere else instead of having to recreate it. Let’s say you have a customer list in a worksheet, but you need to enter a customer name in another and don’t want to have to copy and paste all of their information. You can use a referential formula on the customer name to return all of that data for you.
• • Math & Trig – While these deal with mathematical equations for higher math, there are many here that can be valuable to a small business, like rounding.
• • More Functions – This exposes non-standard functions from a daily business perspective. It doesn’t mean that you’ll never use them, but it’s not likely unless you’re in a relatively specialized business.

 

Figure 170  

• • Defined Names – Defined Names in Excel are a very powerful tool, and often overlooked. We’ll discuss them in more detail in the next lesson. Essentially it allows you to give a range a name and refer to it instead of the range address. You can also apply a formula to a name, e.g. =A1*Tax, where Tax refers to a value (like =.0975), not a range. Many people feel that it makes it easier to read formulas when they use named ranges. E.G. =VLOOKUP(A1,SalesData,2,FALSE) is for many a lot easier to read than: =VLOOKUP(A1,Formulas!$A$1:$N$6,2,FALSE)
• • Name Manager – The Name Manager is where you add new names, delete old ones as well as adjusting existing names. It will launch the Excel 2003 Name Manager dialog, which like so many of them is very robust. When you open it you’ll see any existing names in your workbook, and when you select one, you’ll see what it refers to at the bottom of the dialog.

 

Figure 171  

• • Note in this case you’ll see that “SalesData” refers to =Formulas!$A$1:$N$6, just as in the previous formula examples. You’ll also see some Tables that have been defined. When you create a table in Excel by applying a Table Style to a range, Excel automatically names that range. Whenever you make additions to it, Excel will automatically adjust the range for you.

 

• • New – this give you the option to add a new named range or value/formula.
• • Name - First you would enter a name for your range. It needs to follow some standards, like it can’t have spaces in it (underscores are OK though) – You’ll find the full details in the Excel Helpfile.

 

Figure 172  

• • Scope - Next define the scope of the name. Scope refers to where the name can be seen in the workbook. For the purposes of this course you’ll never need to declare anything with a scope past the Workbook level.
• • Comment - These can come in really handy when you start having a lot of names in a workbook and you want a quick reminder as to what they are, but don’t worry about it if you only have a few that are obvious.
• • Refers to - By default this is going to automatically fill with whatever range you have selected prior to invoking the Name Manager. If that’s not what you want, you can also use the Range Launcher at the right to go and directly select the range you want after calling the Name Manager. This is one of those personal preference things and there’s no “right” way to do it.
• • Edit - This does nothing more than re-launch the New Name dialog and lets you change any part of what you already entered.

 

Figure 173  

• • Delete – This will Delete the Name you have selected at the time, and as with so many other things, Excel will ask if you really want to do it:
• • Filter – This is a neat dialog in that it lets you narrow the scope of the names you want to see in the Name Manager, but unless you’re dealing with a lot of names, you probably won’t use it.

 

Figure 174  

• • Define Name – There are two options here:
• • Define Name - This just brings up the New Name dialog as seen in the example above.
• • Apply Names – Let’s say you already wrote a few formulas then decided it might be easier to have some defined names in them. Excel won’t automatically change your existing formulas to reflect the new names (it will when you start writing new formulas though), so you can use this dialog to update your named ranges in your formulas. For the purposes of this course this isn’t going to be covered in depth (and it’s relatively rarely used), but feel free to try it on your own. If you find yourself to be a fan of named ranges, then this may prove useful to you in the future.
• • In the following example, values were placed in A1 & B1, and the formula =A1+B1 placed below. After that, A1 & B1 were named “New” and “Old” respectively, then the Apply Names tool invoked:

 

Figure 175  

• • Use in Formula – This simply allows you to select from your list of names whether you decide to start a new formula, or place a name in a formula once you’re writing it. Again, this one is rarely at this level, so it’s not going to be discussed in depth here.
• • Create from Selection – Here Excel tries to read your mind, and create Named Ranges for you based on a range you select. It is not infallible, and unless your table ranges have relatively straightforward structure that you know won’t go wrong, you’re better off making sure by using the Name Manager and not leaving anything to chance. Hunting down an error in a Named Range is much better left avoided if at all possible.

 

 

Figure 176  

• • Formula Auditing – This is a somewhat advanced feature that won’t be covered in this course, but it allows you to evaluate formulas to determine if there are any cells dependent on them and vice versa, as well as watching Excel calculate a formula in steps, so you can see if some part of it isn’t doing what you expect. You’ll find these methods employed in detailed models, and while very useful, the odds that you’ll use them in the beginning are slim.
• • Trace Precedents – Shows you what cells the formula depends on.
• • Trace Dependents – Shows you what cells are dependent on the formula.

 

Figure 177  

• • Remove Arrows – This resets the formula trace arrows. If you have a lot of formulas they can get overwhelming (they’re literally all over the place!).

 

• • Show Formulas – This converts your formulas to text. It can be very handy if you want to distribute a workbook, but forgot where all the formulas are.
• • Error Checking – This is a tool for determining why certain formulas might evaluate to an error. Excel has a multitude of error messages to let you know why a formula doesn’t return the results you might expect, and they’re very useful (albeit somewhat scary at first). We’ll be discussing Excel’s Error Messages in the Formula lesson, but for now, the Error Checking option is a good consolidation tool for newer users, as it exposes several options in one place for you. Just be warned that the Error Checking tool can only estimate why your formula evaluates to an error; it gives you a good place to start looking, but not much more. You might find yourself using Excel’s Error Checking Options at first, but as you get stronger with Excel you’ll find that it’s often unneeded.

 

• • One reason we’ll talk Excel’s Formula Error Messages is because sometimes you want to suppress those errors on behalf of your users, so they don’t think that your spreadsheet is “broken”. Nothing will get users to scream more about that, even if the error messages are a result of them not yet entering required data. The following example shows how easy it is to get a #DIV/0 error (you can’t divide by zero), and it’s nothing more than =A2/B2, but B2 = 0, so it throws an error at you. But what if that 0 in B2 is dependent on user input? You can use error handling to catch it and not alarm your user like this: =IF(B2,A2/B2,0), which simply says that if a value in B2 exists, perform your function, otherwise return a 0 instead of an error message.

 

Figure 178  

• • In the following example you’ll see an error created by trying to reference a sales representative not in our list of sales reps, so it returns an #N/A error. This is one of those things where Excel will be less than useful by telling you that there’s an error in your formula, but not really tell you how to fix it. Unfortunately, Excel can’t read your mind even if the folks at Microsoft have done a pretty good job of trying to make it as smart as possible. Excel knows what font, size, color and even language you’re using when you write a formula, but it doesn’t know what you intended to do with it, so it gives you its best shot. As you get going on learning formulas you’ll start to understand what causes errors and how to address them instead of having to rely on the Error Checking tool, but for now it might still be a good way to ease into things.

 

Figure 179  

• • Error Checking Options
• • Help on this error – This will launch the Office Online Helpfile and try to find you a solution. If you’re not online it will resort to your PC’s Helpfile (if you chose to install it). Generally these will give you an ambiguous article reference that will leave you as lost as when you started, although the Help Writers at Microsoft have really stepped up their game in getting really good help content to you, and more is to come, so this will prove to be a more valuable resource in the future.
• • Show Calculation Steps – This will bring up the Evaluate Formula tool, which we’ll discuss shortly
• • Ignore Error – Does just that.
• • Edit in Formula Bar - Does just that as well, although you could get there faster by just hitting the F2 key when the active cell houses the formula in question to activate the cell in question, or just click on the formula bar instead of taking the extra steps to invoke the Error Checker.
• • Options – This will launch the Formula menu from the File, Options, Formulas dialog. It would be pretty silly to go all the way here just to invoke that, but sometimes you might find it handy.
• • Previous/Next – Will look for additional errors in your worksheet, but generally if you’re not disabling error messaging in formulas (which we’ll discuss in the Formulas lesson) you’ll know where to look, because a cell (or many cells, as is often the case) that should return a value gives you a result like #N/A or #DIV/0. Those are pretty hard to miss (they stick out like a sore thumb!)
• • Trace Error – This simply draws arrows from the formula in question to any dependent cells. It’s the same as using the Trace Precedents/Dependents tool. It’s just a visual indication of where your cell dependencies lie.
• • Circular Reference – A circular reference generally occurs when you try to get a cell to refer to itself. If you were to enter =A1 in cell A1 you’d get a circular reference, because a cell can’t depend on itself for a value. A real-world example of this would be if you try to calculate employee bonuses based on net profit, which would be Revenue - Expenses. If you were to then try to add that bonus amount back into your Expenses you’d get an error because the bonus calculation is dependent on the difference between Revenue and Expenses, so you can’t add that back in because it becomes part of the dependency, causing an error. If you do get a Circular Reference, Excel will let you know about it:

 

Figure 180  

• • The Circular Reference tool simply identifies the cell(s) with those errors.

 

Figure 181  

• • Evaluate Formula – This is a great tool for checking your formulas if they don’t give you the results that you expect. For simple formulas you probably won’t need it, but for combined formulas (where you use multiple formulas together – these are sometimes referred to as Mega Formulas) it can be invaluable, as it allows you to evaluate each section of a formula without having to break the formula apart into its different elements. Many people who don’t know about this tool will break a formula apart into its separate parts and test each one individually until they trace the error. With the Evaluate tool you don’t need to do that, instead you let Excel do the heavy lifting for you. Here’s a simple example of one of the Formulas in the Lesson 4 companion workbook: =Formulas!B2*Tax (which if you remember was defined as a named range with a value of 9.75%).

 

Figure 182  

• • You’ll see the first part of the formula that will be evaluated underlined. Pressing the evaluate button will tell you what that portion of the formula evaluates to, in this case it’s a static number from cell B2, so Evaluate gives you its value
• • Each press of the Evaluate button will move you onto the next step in the formula. Here you’ll see that it’s evaluated Tax as 9.75%.
• • Finally it gives you the result, now it’s up to you to determine if it’s right or not. This was a very simple example, but you can imagine how helpful it can be to point out what might otherwise be a very obscure error. It’s also a very interesting window into how Excel calculates formulas.

 

Figure 183  

• • Step In – This is just another way of showing a different level of detail.
• • Watch Window – This is another neat tool for testing or monitoring your formulas. Let’s say you have a formula on one sheet that’s dependent on the active sheet, but you don’t want to have to switch between sheets to see if it’s doing what it should. You can add a Watch Window to monitor it right there.

 

Figure 184  

• • In this case the formula being watched is dependent on changes in our sales table in cell B1. So changing that value will update that watched formula on the other sheet. Will you use this a great deal? Not likely, but when you do, it’s an invaluable resource, just like the Evaluate tool.

 

Figure 185  

• • Calculation – This is where you control how Excel calculates, whether it be Automatic or on demand (Manual). Why is this important? Let’s say you have a complicated model for your business that tracks Revenue & Expenses and also calculates things like your profit. It may be dependent on a lot of data entry, but also have a lot of formulas that calculate those figures. Each time you enter data Excel will recalculate all of your formulas. If you have a lot of formulas (and with 16 billion cells on a worksheet it’s not hard to imagine how many formulas you can have), each time Excel calculates can slow your data entry to a crawl, so you can turn calculation off an only recalculate when you’re done.
• • Calculation Options
• • Automatic
• • Automatic Except for Data Tables
• • Manual

 

Figure 186  

• • Calculate Now – Calculates the entire workbook.
• • Calculate Sheet – Calculates just the formulas on the active sheet.

Data

First, the Data tab gives you options as to what kind of external data you can pull into your Excel workbook, be it a Microsoft Access database, from the internet or even text files. You can also manage your data connections by refreshing them and even setting intervals for them. A lot of people like to use Excel to keep track of their investment portfolios, and you can have Excel update the portfolio data throughout the day, or maybe you’re linked up to a company server that lets you download transactional data specific to your business. Instead of doing it manually, you can tell Excel to do it for you. We’ll discuss this in depth in the Importing Data from Other Sources lesson.

 

Figure 187  

Second, the Data tab gives you a lot of important functionality for analyzing and manipulating your data, like Sorting, Filtering and a lot of other neat tricks. We’ll discuss a lot of this in the next lesson on Entering and Editing Data, so for now we’re just going to cover the basics.

• • Sort & Filter
• • Sorting - is relatively straightforward on this menu, you simply click on the direction you want to sort (Ascending or Descending). Just click on the A-Z or Z-A buttons to do either. Don’t worry about the A-Z/Z-A label, as it will work fine with numbers too. Clicking the Sort button will launch the Excel 2003 Sort dialog, which we’ll go into more detail later.

 

Figure 188  

• • Filter - is a tool you might have become familiar with if you looked at the earlier Data Table options. Again, it’s something we’ll discuss in more detail later. When you want to invoke Filtering the tool will automatically look for a header in your data and add drop-down to your contiguous range of data. Adding a Filter also gives you the ability to sort directly from the Filter dialog.

 

Figure 189  

• • Advanced – This is a great tool if you need to narrow down a list. Let’s say you have a list of customer transactions, but you want to create a list of just the customer names, or items purchased. You can use Advanced Filter to copy the Unique Records to another place on your sheet. From there you can move it to another sheet and do what you want with it. Unfortunately, Advanced Filter will only copy your data to the same sheet, so you have to cut and paste if you want to move it somewhere else (which you generally do).

 

Figure 190  

• • Data Tools

This group holds several tools that let you physically manipulate your data without having to cut and paste, or otherwise do it manually.

• • Text to Columns – This lets you quickly parse data without formulas or manual labor. Remember the formula example of breaking apart customer names (“Smith, John”)? Data, Text to Columns is faster and generally a much better way to go with large data sets. It’s got a good Wizard tool to help guide you, and we’ll discuss it in depth in the next lesson.
• • Remove Duplicates – This is a new tool that Microsoft released after years of begging. Previously if you wanted to remove duplicate information from you data you had to either do it manually or write code. Now you can do it in just a few steps. Simply select your data range and invoke the Remove Duplicates Wizard.

 

 

Figure 191  

• • Data Validation – This is one of your most powerful allies when it comes to defining what data your users can enter. For instance you can limit date entries to a certain range, like making sure that Time-Off requests only go from the current date forward, define a number range, like limiting an annual employee increase form to 5%. You can even use your own defined lists to select from a list of cities in which you do business to prevent misspellings, because frankly, given the opportunity, how many people are going to misspell Mississauga or Onondaga? If you have formulas depending on correct spellings then this is invaluable. Again, this is one of those tools that require some in-depth discussion, so we’ll cover it in detail in the next lesson.

 

Figure 192  

• • Consolidate – This allows you to combine data from multiple ranges in a new consolidated range. For the purposes of this course we won’t be discussing it, as it’s not likely you’ll use it this early on in the game, but if you are interested, the Helpfile documents it well.
• • What-If Analysis – There are literally entire books devoted to these tools, so we’re not going to cover them in detail here, instead we’ll just quickly review them. Note that these are just the tools that are natively included with Excel’s standard installation. There are additional Add-In tools that you can find both from Microsoft and other third-parties that perform a multitude of analysis tasks. If you have a relatively unique business model, then odds are you’ll find that someone’s written a tool to help you analyze it.
• • Scenario Manager – Allows you to define different scenarios based on certain inputs. Let’s say you have a best-case scenario for your business, but you also want to be prepared for an economic downturn or catastrophic event, Scenario Manager lets you build those scenarios without having to build complicated models. You can also combine separate scenarios into a Scenario Report so you can view them side-by-side.
• • Goal Seek – This is a great tool for determining a result based on variable inputs. Let’s say you need a new pizza oven and you know that it’s $30,000. Your credit union will give you a rate of 7% over 60 monthly payments, resulting in a monthly payment of $594.04, but you only want to spend $500 per month. With Goal Seek you can determine that you need to find an oven in the $25,000 range. Clicking OK will accept the solution, whereas Cancel will revert to your original values.

 

 

Figure 193  

• • Data Tables – These are similar to Scenarios except that you can build multiple variations in one spot vs. individual scenarios that can later be consolidated. For instance you could analyze the effects of different terms on lease options for a new location. The Excel Helpfile has good documentation on both Scenarios and Data Tables should you choose to explore them further on your own.
• • Outline
• • Group/Ungroup – This lets you group rows/columns so that they can easily be hidden or displayed with the click of a button instead of having to do it manually. Simply select the range you want to group and select the Group icon. Excel will ask you if you want to group rows or columns and then apply a group for you. In order to create groups you need to have some type of range break between them, otherwise Excel will just add any additional ranges to the contiguous group. This does take some time to set up, but it can be well worth it once you do. Where would you use this? Let’s say you have an employee roster that lists employee names, then days of the week. If you wanted to give each employee their own shift schedule you could group them. Once you apply a group, you’ll see Excel create a number dialog (1,2) to the left of the row headings or above the column headings depending on which grouping style you selected, as well as +/- signs for each group to show you whether they’re collapsed or expanded. Selecting the 1/2 will expand the entire range of groups, whereas the +/- will expand or collapse just that particular group.
• • Auto Outline – This is a way to let Excel apply multiple groupings to rows and columns, but it’s finicky at best, and should generally be avoided.
• • Subtotal – This is a huge tool for analyzing your data quickly without having to write formulas. Just let Excel do it for you! Following is an example of some transactional data for a fictitious company that has several regional sales offices and products that they sell, and a snapshot of the Subtotal Wizard.

 

Figure 194  

 

Figure 195  

• • With the Subtotal Wizard you can select which column you want to Subtotal (in this case we’re selecting the Region, then select the Function (Formula) you want to apply, in this case we’re going to use Sum. Next you can choose which columns you want to apply those formulas. Once you click OK, Excel’s going to give you instant Subtotals. Notice that it also adds grouping layers for you, similar to what we just discussed with Grouping.

 

Figure 196  

 

 

Figure 197  

Review

This is where you get a workbook ready for distribution by checking spelling, adding comments and protecting workbooks and worksheets from changes.

• • Proofing – This is pretty straightforward.

 

Figure 198  

• • Spelling - is going to Spell Check your entire worksheet based on the Office Dictionary, including any specific words you might have added.
• • Research - will launch an internal search pane on the right that will allow you to search for a term or word in internal content that came with Office, or the Internet, both directly from Excel.
• • Thesaurus - will bring up Excel’s internal Thesaurus tool to let you select from different terms. It also lets you use the Internet internally.
• • Language – This simply consists of a Translation tool that can translate words or entire worksheets to another language. Note that here Excel can only interpret your literal text, but it can’t interpret your intentions, so use it with caution. Excel will do its best to get it right, but if you deal with translations a lot it’s best to have a live person proof the document before you send it to anyone. Many a business deal has been lost in translation. Literally.
• • Comments – Comments are a twist on letting users know what’s going on in your worksheet/book. You can use Data Validation to add comments to a cell in terms of directing user input, but cell comments are simply to make note of something. You’ll see a lot of Excel templates that have comments directing you how to set it up for you, and then instruct you to delete them when you’re done. They can be pretty handy, but as with colors, use them sparingly, as they can get irritating fast. Comments can be displayed constantly, or hidden from view. When they’re hidden you’ll see a small red triangle in the upper right-hand corner of the cell. You can just hover over the red triangle to display the comment. As soon as you move the cursor off of the cell the comment will disappear. You can also choose to selectively hide comments by selecting them, then using the Show/Hide Comment button.

 

Figure 199  

 

Figure 200  

• • Show Ink – It’s unlikely that you’ll ever run into this unless you share your workbooks with users who use tablet PC’s or stylus type input devices. Show Ink just allows you to see hand drawn annotations or notes.
• • Changes
• • This is where you’ll do a lot of work for distributing workbooks to end users, whether you want them to only make changes to selected cells or ranges, or if you don’t want the workbook to be editable at all.

 

Figure 201  

• • Protect Sheet - By default all cells on a worksheet are Locked, so you need to unlock them prior to protecting the worksheet. First, you need to select the cells you want to allow to be editable (you can select multiple non-contiguous cells/ranges with Ctrl+Left-Click). Once selected, press Ctrl+1 to launch the Format, Cells dialog and goto the Protection tab. Uncheck the Locked check box, which will allow those cells to be editable once the worksheet is protected. The Hidden button isn’t for editing, but if you want to hide the contents of cells with formulas. Why would you want to do that? Maybe you have a hidden worksheet that you don’t want users to know exists, or you might reference sensitive information that you don’t want people to see. If you do have some of those, then once you’ve unlocked your editable cells you’d go back and repeat the process, but this time check Hidden, making sure not to uncheck “Locked”. One nice thing about sheet protection is that it introduces a Tab order in the unprotected cells, meaning that the Tab key will automatically move the user from one unprotected cell to the next, but it does have limits. Note that this does require some design considerations as the Tab order goes from Left to Right, then Down. If you’ve ever entered data into a web site form that had its Tab order out of sequence, like moving from City to Zip Code, then State, you’ll be immediately familiar with this concept.

 

Figure 202  

• • Protection Options – Prior to Excel 2003 you only had the option to protect the sheet, with or without a password. Now you have a whole slew of additional options at your disposal. For instance you can allow users to use AutoFilter or Sort data, where previously you had to write code or unprotect the sheet to allow this behavior. Unless a worksheet is specifically for you and you’re protecting it to take advantage of the Tab order, then you generally want to use a password. When you enter your password you’ll be prompted to enter it again. You don’t need to get fancy with your password like you would with your online bank accounts though. A simple password should suffice. It should also be something easy enough to remember. If you try to change a Locked cell on a protected sheet you’ll get a message telling you that the cell you’re trying to change is protected.

 

Figure 203  

 

 

Figure 204  

• • Protect Workbook – This prevents users from making changes to the overall structure of your workbook, like adding or deleting sheets. Here again you have the option of entering a password.
• • Share – This allows you to let multiple users work on a workbook simultaneously; however it should be avoided if at all possible, because Shared workbooks can cause all kinds of problems. They also have some pretty severe limitations with regards to the native functionality they support. If you need a multi-user input tool, then you’re much better off using Microsoft Access, or you can let users collaborate on documents using Microsoft’s free SkyDrive service. Note that workbooks that contain Tables can’t be shared.

 

Figure 205  

• • Protect and Share Workbook – This simply lets you protect a workbook before sharing it.

 

• • Allow Users to Edit Ranges – This is a new feature that allows you to set individual passwords and permissions to individual cells/ranges. This could be handy for something like an online timesheet application where you don’t want people to be able to enter data for anyone but themselves. When you launch the dialog you’d click “New” to get started adding a range.
• • You can add a password, and anyone who knows the password could edit the range, or you can define individual user roles by proceeding to the Permissions step. If you’re not familiar with setting user roles on a PC you might be better off having an IT specialist do this for you.

 

Figure 206  

• • Track Changes – This is a feature in Shared Workbooks that highlights changes that users make.

View

The View tab is where you control the appearance of your worksheets.

• • Workbook Views
• • Normal – This is the default view for your workbooks and is most likely where you’ll spend most of your time. You can also toggle Views from the Status Bar.
• • Page Layout – This allows you to see the worksheet as it will print, including revealing any Headers/Footers you might have. You saw an example of this view in the Header & Footer section.
• • Page Break Preview – This lets you see where your page breaks are, and they’re displayed as dotted blue lines horizontally and vertically. The Print Area is delineated by a solid blue border and everything outside of the print area is gray.

 

Figure 207  

• • Custom Views – This is a neat way to set up different views instead of constantly hiding rows or columns. Any existing views will be displayed in the dialog, and you can easily add new ones. In order to define a new view you first need to set up your worksheet the way that you want it, hiding rows and columns as necessary. Once you’ve defined the view you can unhide everything, then invoking that view will return the sheet to the view you set up. It takes some time to set up several views, but if you’re in the habit of hiding rows and columns this can be a huge timesaver.

 

Figure 208  

• • Full Screen – This simply hides the Ribbon and Quick Action Toolbar. You can revert to normal view by pressing the ESC key.
• • Show – This defines some of the physical elements you can have on the worksheet, like the Ruler, Gridlines, Formula Bar and Headings. In many cases you’ll want to turn these off, especially with a distributed report that is for viewing only.

 

Figure 209  

• • Zoom – These allow you to zoom in or out on your sheet to show more detail or less. You’ll also find the Zoom controls on the Status Bar if you have them activated. Zoom will launch a dialog that has preset Zoom values, or you can enter in your own value.

 

Figure 210  

• • 100% - Will return your worksheet to 100% Zoom.
• • Zoom to Selection – This allows you to select a range on your worksheet and quickly zoom into it. You can click on the 100% button to get back to normal.
• • Window – This allows you to view multiple worksheets/workbooks at once, as well as freezing rows and columns so that you can always see them regardless of where you are on a sheet.

 

Figure 211  

• • New Window – Clicking this will create a copy of your workbook and an instance number will be appended to the workbook name (“Workbook1:2”). Once you’ve done this you can use the Arrange All selection to view the workbooks side by side.
• • Arrange All – This just gives you options as to how you want to compare the open workbooks, whether it’s a new window or different workbooks. If you want to view different workbooks, make sure to uncheck the “Windows of active workbook” check box.
• • Freeze Panes – As mentioned, this lets you freeze columns and rows so that they’re always visible no matter where you scroll on the worksheet. To Freeze Panes select the row below the row you want to freeze and the column to the right of the columns you want frozen, then select Freeze Panes. You can also opt to freeze just the top row and first column. This is fine if you don’t have multiple detail rows and columns you want frozen in place.

 

Figure 212  

• • Split – This will split your worksheet into multiple panes. It can be useful for large sheets with a lot of data on them, because you can view the top of the sheet in one pane and the bottom in another.

 

Figure 213  

• • Hide - This will hide the entire workbook.
• • Unhide – This gives you the option of unhiding any hidden workbooks.

 

• • Macros – This course isn’t going to cover Macros or VBA (Visual Basic for Applications) programming, but you can view a list of all public macros, or record a new macro from this menu. The Use Relative References is just a way to tell the Macro Recorder to change its reference style when it deals with cells and ranges.

 

Figure 214  

Unit Summary: Lesson 4 –The Ribbon In-Depth - Part II

• • In this lesson you learned about the in-depth ins and outs of the Insert, Page Layout, Formulas, Data, Review and View tabs on the Excel Ribbon, while the previous lesson focused solely on the Home tab.
• • You walked through the elements of each Ribbon tab, and also saw how there are multiple ways to expose the elements with keyboard shortcuts and Dialog Launchers.

Review Questions – Lesson 4 – The Ribbon In-Depth – Part II

1. Name 4 groups on the Page Layout tab, and what do they do?

a. __________________________________________________

b. __________________________________________________

c. __________________________________________________

d. __________________________________________________

2. What are four of the Formula groups?

a. __________________________________________________

3. Where would you turn off Gridlines on a worksheet?

a. __________________________________________________

4. What does Grouping do?

a. __________________________________________________

5. Why would you want to use Comments?

a. __________________________________________________

b. __________________________________________________

6. Where do you find quick access to functions?

a. __________________________________________________

7. How can you keep a user from changing data in a worksheet?

a. __________________________________________________

Lesson Assignment – Lesson 4 - The Rest of the Ribbon

This assignment is to open the Lesson 4 workbook and start getting familiarized with the following (there is a Notes section below for you to keep track of your observations):

• • Insert Tab
• • Start working with Shapes & SmartArt
• • Page Layout Tab
• • Experiment with different page layout options, like Portrait vs. Landscape.
• • Formulas Tab
• • Try working with some of the AutoSum tools in the Formulas worksheet
• • Data Tab
• • Apply a Data Filter to some data and start exploring the different filtering options.
• • Review Tab
• • Try Spell Checking
• • Add a few Comments and format them
• • Explore what you can do with worksheet protection and the expanded user options you can grant
• • View Tab
• • Set up a worksheet for printing and adjust the display options, like Headings & Gridlines
• • Try the New Window feature and see how many different ways you can view worksheets & workbooks side-by-side