Lesson 3 – The Ribbon In-Depth – The Home Tab

In Lesson 2 you got an introductory/refresher course in basic Windows file operations, like Saving, when and where, as well as a deep dive into how to set up Excel the way you want it by walking through all of your setup options through the File/Office menus. In general you’ll likely only set those options once, and never worry about them again; however there are a few you might change between workbooks and worksheets depending on what you’re doing:

• • Changing the direction of the cursor when you hit Enter (Down, Right, Up, Left)
• • For the workbook it could be showing Comments, ScrollBars, Sheet tabs
• • For worksheets you might change Displaying Zero values, Gridlines, etc.

In Lesson 3 we’ll review the Ribbon’s Home tab and all of its elements. The Home Tab is the default tab for the Ribbon, and as such Microsoft has lumped most of what you do the most there, so this will get the most attention of the Ribbon elements. Before we do that though , you should understand a bit of the history behind the Ribbon - The original Excel menu interface was designed in 1992, and as with all products, Excel has gone through some significant changes since then, each one giving users more capabilities than the next. The engineers at Microsoft did a great job of keeping this product the number #1 spreadsheet application in the world for a long time, but then a new generation of engineers came on board and had some great new ideas based on their experiences with computers. Just as computer games have evolved since the days of the Commodore and Atari, the Ribbon is nothing less than an evolutionary step in the Excel product life cycle, and it’s something that had to happen if Excel was to remain the most used application on the market. Oddly enough, Bill Gates was against the Ribbon, fearing that its introduction would alienate a large group of experienced users who had grown comfortable enough to memorize all of Excel’s keystrokes, and more importantly, knew where everything was. But he was outvoted and development on the Ribbon for Office applications really took off. Was Bill Gates right? To a certain degree, but most experienced users have now adopted the Ribbon; those who haven’t have been left behind because their customers have moved on. Probably the most important element of the Ribbon is that it puts everything right out in the open for you in a great graphical display, which earlier versions lacked. For newer generations of users who are as comfortable with a mouse as they are a keyboard, the Ribbon is a natural step, and Microsoft has done a great job of it. There have been some hiccups along the way, which is why you’ll find certain things in the Excel (and Office 2010 Ribbon) that weren’t available in 2007, like customizing the Ribbon, but that’s just another evolutionary step (and Microsoft continues to do a good job of listening to their customers with regards to making the whole thing more user friendly).

• • To get an idea of the difference between a Ribbon and non-Ribbon version of Excel, here’s a screen shot of Excel 2003, which was the last version of Excel without the Ribbon:

 

Figure 69  

• • Now compare that to Excel 2010 with the Ribbon:

 

Figure 70  

• • It may not seem like that much, but the big difference is that the toolbars you see beneath the Excel 2003 File menus (File, Edit, View, etc.) didn’t change no matter what Menu you selected (although you could add or remove Icons from the Toolbars). Instead the rest of your options were Dialog selections, like New, Open, Close, etc.

 

 

Figure 71  

• • With the Ribbon however, when you select a new Tab group from the Ribbon it exposes an entirely different set of graphical menu commands:

 

Figure 72  

• • This advancement in the graphical menu commands has made it a lot easier for new Excel users to get a grasp of where everything is located, while users who were comfortable with earlier versions had often worked with them for years and had just learned where things were. If you’re serious about using Excel to help you with your business, then this will dramatically speed up your learning curve. .
• • As we already discussed the features you’ll find on the File Tab, we jump right into the Home Tab, which is the default Ribbon Tab anytime you open Excel (the following screenshot is from Excel 2010 – If you’re in Excel 2007 it will be slightly different, but not drastically, you just won’t see as many commands exposed):

 

Figure 73  

• • The Home Tab primarily deals with most of the things you’ll do all the time when you’re working in a worksheet, like formatting, inserting/deleting cells and some degree of editing. Here we’ll go over each group and what it does. For this lesson you should work in Excel alongside the course material and use some of these menu commands so you can start to get comfortable with them yourself. And don’t worry about messing anything up, we’re not working on workbooks with any data in them yet, just sample data that you’ll enter as we go along. There’s no need to worry about saving any of this unless you want. So go ahead and open a new workbook, look through the Ribbon Tab items again, and when you’re ready, goto the Home Tab and come back here.

Let’s get started with the Home Tab!

Home Tab

This is the Ribbon Tab that’s always going to be active when you open Excel. It holds the following Tab Groups that we’ll go through one by one:

• • Clipboard – This is where you control copying and pasting, and the myriad of things you can do with pasted data. Lesson 2 covered some of those with regards to showing your Pasting options on the worksheet, but this is where you get to try some of them on your own. In your blank workbook, just enter some sample data (it doesn’t matter what it is).

 

Figure 74  

• • Enter the following into any cell: =1+1 (hit enter when you’re done to confirm the formula and move to the next cell)
• • Now click the Copy button, select the destination range, either with the mouse or using the arrow keys to scroll to it (you can copy an entire range too, you’re not limited to copying one cell at a time), and paste using the Paste button. If you just click on the Copy/Paste Icons, you will perform a simple Copy/Paste, but if you select the drop-down arrow beneath either you’ll get expanded functionality (this holds true for any Ribbon Group Command that has a drop-down beneath it).

• • When you paste you’ll see the Paste Options dialog to the right of the cell, which was mentioned in Lesson 2), and at that point you can choose to invoke any of those operations (from left-to-right, then down – if you hover the mouse pointer over any of these commands a ToolTip will appear):

 

• • All – Pastes formulas, cell formatting, font style, etc. This is the same as a straight Copy/Paste using either the Ribbon commands, or keyboard shortcuts.

 

Figure 75  

• • Formulas – Will only paste the source range’s formula, not any formatting
• • Values – Will only paste the source range’s Values – it will eliminate formulas.
• • This trick comes in handy if you want to create a snapshot of a range and don’t want any inputs to alter the formulas. It’s also very good for distributing workbooks for a number of reasons: it can greatly reduce workbook size, no one can alter your data by using the formulas, and if you have complicated formulas then no one can delete them. Many workbooks have been compromised when someone sent it out only to get it back with formulas changed.
• • Formats – Will only paste the source range’s formats
• • Comments – Will only paste the source range’s Comments
• • Validation – Will only paste any Data Validation associated with the source range (we’ll discuss Data Validation in the lesson on Entering and Editing Data).
• • All using Source theme – will paste the source range’s contents and whatever theme was applied to the worksheet.
• • All Except Borders – Will only paste the source range’s contents
• • Column widths – Will only paste the source range’s column widths. This can be handy if you have column sizes that are greater/less than the default column sizes and don’t want to have to manually adjust them to match the source.
• • Formulas and number formats – Handy for pasting formatted data to unformatted cells
• • Values and number formats – Same, but will eliminate any formulas that were in the source range
• • All merging conditional formats – Conditional Formatting is a way of evaluating a range for certain criteria and formatting the range based on the criteria. We’ll discuss Conditional Formatting later in this lesson (and in more detail in the Formatting lesson), but this option evaluates a range of cells (it won’t be an option on a single cell source or if no Conditional Formatting has been applied) and will copy any conditional formats applied within the source range. Where any Conditional Formats are the same, they’ll be pasted.
• • What we didn’t discuss on the Clipboards’s Paste menu was the Paste Special dialog, which offers you some additional tools not found on the worksheet paste dialog. To get to this dialog you have to copy a range first, then use the Paste drop-down from the Clipboard group and select the Paste Special option from the bottom, and you’ll get the following dialog box (you can also call the Paste Special dialog with ALT+E+S):

 

 

Figure 76  

• • Here you see a list of three groups, and if you compare this dialog to the one from the Ribbon side by side you’ll see that much of the functionality is shared between the two (the Paste Special dialog above is actually from Excel 2003). While the Ribbon gives you the ability to paste a picture or a linked picture, which the Paste Special dialog doesn’t, the Excel 2003 dialog gives you a few things that the other doesn’t (this is one of those things that Microsoft will fix in later versions, and you’ll probably see the dialog above go away or be changed to match the new look at that time):
• • Operation
• • This is a very neat set of options that lets you copy a source value and mathematically apply that value to a range with one of the four major operators (Add, Subtract, Multiply & Divide). Let’s say you have a range of product prices and you need to apply a 3% increase across the board. Enter a set of sample values in a few cells then put 1.03 in a cell outside of that range. Now select the range of prices and Paste, Paste Special, Multiply and see what happens.

 

Figure 77  

• • Unfortunately, as you might expect the destination receives the source range’s number format. But, in this case, even though you were multiplying a percentage increase, if you didn’t want to reformat the destination after pasting you could simply format the destination as currency beforehand and that format would apply.
• • Skip Blanks
• • This is another cool feature that even a lot of seasoned Excel pros don’t know about. It lets you paste a range of data that has blank cells in it onto a range of data that doesn’t, and only the source range’s data will be copied - any blank cells in the source range will be ignored and the destination data won’t be disturbed. So let’s say you have a range of cells like this:

 

Figure 78  

• • And you realize that instead of having your monthly variances at the bottom, you want to have them where last year’s figures are. You can select the variance range and copy it (this example is included in the Lesson 3 workbook):

 

Figure 79  

• • Then paste special and select the Skip Blanks option and you’ll get this:

 

Figure 80  

• • Transpose – This is another cool feature that lets you physically flip your data’s orientation. Suppose you have a series of data that runs in rows/columns left-to-right like the example above, but for a certain application it really needs to go the other way. You could come up with all kinds of complicated formulas to transpose the data, or you could use this feature. Just copy the range from the previous example (A1:F2), find an unused range somewhere you want to put it and goto Paste,Paste Special,Transpose (Alt+E+S+V+E) and you’ll get this (and all of your formatting will be copied over to the destination too).

 

Figure 81  

• • Paste Link – Finally, you can paste links to the range you copy. So let’s say you want to have a copy of those figures in A1:F2 on another sheet, but you don’t want to have to update both sets of records. The PasteLinks option will paste the source data as formulas that reference it. Anytime you change the source data, the pasted links will automatically update. Note that when you paste links you’ll lose your formatting, so it’s a good exercise for working with the different pasting options. While your paste range is still active, click the paste options button and select the Keep Source Formatting option. Viola!
• • Clipboard Dialog Launcher
• • Next click the Clipboard Dialog Launcher in the lower right hand corner of the Clipboard Group. A new pane will immediately open on the left of the worksheet window:

 

Figure 82  

• • The Clipboard will store the 24 most recent items you’ve copied, and you can come back to any of them and paste them anywhere in your workbook as long as the item(s) is in the Clipboard (even pictures, graphics, etc.). And this isn’t just the Excel Clipboard, but the Windows Clipboard, so you’ll see what you copy from other applications appear in this list too.
• • The first options you see are Paste All and Clear All.
• • Paste All will paste everything in the Clipboard
• • Clear All option will delete everything in the Clipboard

 

Figure 83  

• • In order to paste something from the Clipboard, all you have to do is have the cursor on the cell in which you want to paste the item in question, then click on the item in the list, and select Paste or Delete from the drop-down that appears on the right (remember, if you paste an object it won’t paste into the cell, but on top of it):
• • Note: choosing Delete won’t delete the item from the worksheet, just the Clipboard.
• • At the bottom of the Clipboard window you have some options regarding how you can control the Clipboard’s behavior:

 

Figure 84  

• • Font
• • This is where you control the Font type/style, Size, Effects (Bold, Italicize, Underline), Font/Cell Color, and Gridlines. It’s all relatively straightforward, so go ahead and apply some different formatting to a range of cells.

 

Figure 85  

• • Toggle Font Size - Of note are the two A’s to the right of the Font Size box. Both are clickable; the first will increase the selected range’s font size, while the second will decrease it.
• • Borders - The icon that looks like an underlined grid lets you control the border pattern your range will have. Border options are pretty simple to grasp, but the Draw Borders tools aren’t quite so. There aren’t a whole lot of practical applications for them, but some people do find them handy, especially if you need to apply borders to several non-contiguous ranges. The Line Color/Style options are very handy if you don’t like the default black gridline colors or you want a different style of line. Just note with colored gridlines that they may be hard to see on some monitors, so you’re generally better off with a darker color than a light one.
• • More Borders – Here you actually have the chance to completely define your border style(s) if none of the above suit you. That option will launch the Format Cells dialog, which we’ll discuss next.

The Font Group’s Dialog Launcher will load an Excel 2003 dialog with the following Tab groups (when selected from the Font Dialog Launcher Font will be the active Tab):

 

Figure 86  

• • Number Format
• • General – The default number format is General, meaning that any numerical value you enter in a cell will appear just as you entered it.
• • Number – A number with comma separator(s) and a variable number of decimal places you select.
• • Currency – A number with a Currency symbol, comma separator(s) and variable number of decimal places you select. You can also change the format for negative values – ($123.45)/($123.45). You also have your choice of currency symbol.

 

Figure 87  

• • Accounting – Same as Currency, but with no color change for negative values and the Currency symbol is automatically placed on the left side of the cell. Unless you’re an accountant you’ll probably find this to be a pretty strange way to display currency.
• • Date – Allows you to apply different date formats to a range. By default, Excel will recognize any values entered in mm/dd/yy format as a date. 1/1/11 would automatically be converted to 1/1/2011. There are multiple date formats you can select:

 

Figure 88  

 

• • Time – Time entries can be formatted many ways as well, including Military 24-Hour time. The standard time display is HH:MM AM/PM.
• • Percentage – Applies a % format to a range with a variable number of decimal places. With percentage format it’s generally advisable to format the range before entering the decimal values (unless they’re the result of a calculation), because Excel will convert existing values. E.G. 125 would be converted to 12500% if it wasn’t formatted first. But if you format the range first, then entering 125 would result in 125%.
• • Fraction – Allows you to convert values into their fractional equivalents. If you deal with fractions a lot you’ll find that this format isn’t the most dependable, and won’t always give you the desired results.

 

Figure 89  

• • Scientific – Applies Scientific Notation to values. This is generally reserved for very large numbers. E.G. 123456789 is represented as 1.23E+08
• • Text – This will convert values into text. Once converted to text you can’t perform mathematical calculations on the value without converting it back to a number. This can be useful for things like part numbers that have leading zeros, because interpreted numerically Excel will strip the leading zeros. Since you won’t likely be performing mathematical calculations on part numbers it’s generally not an issue. Some data imports can convert values to text and leave you puzzled as to why you can’t work with them as numbers, so you should be aware of it.
• • Special – Allows you to apply Zip Code, Phone and Social Security formats to numeric values you enter. E.G. enter 2125551212 and it will automatically be represented as (212) 555-1212.
• • Custom – This allows you to define your own custom number formats if the ones available won’t cut it. You can learn a lot about a number format by applying a standard format to a cell with a value it in, then select the Custom option and Excel will show you the number format that was applied (along with all the technical stuff you’ll need to copy it). From there you can select one of the number formats from the Custom list, or build your own.

 

• • Alignment – This controls the way your data is represented in a range.

 

Figure 90  

• • Text Alignment
• • Gives you control over Horizontal and Vertical alignment, and allows you to set an Indent if you want.
• • Text Control
• • Wrap Text – this allows text to continue to a new line within a cell if the text gets to the right side of the cell.
• • Shrink to fit – Instead of wrapping text to a new line, this will shrink the text to fit the cell. Note that it will only work for alphanumeric entries, not value entries.
• • Merge Cells – NEVER merge cells. It can cause all kinds of problems, especially with sorting. It’s here to ostensibly help you format better, but in almost any practical application it’s absolutely useless. Imagine wanting to merge a report header across multiple months – most people would merge cells. An alternative is to put your header text in the first column of the report, then select the cell and all the columns in that row for the report. Then in the Alignment option goto Horizontal, Center Across Selection. Excel won’t merge the cells, it just looks like it did. And it’s not going to cause any problems down the road.
• • Orientation
• • This allows you to change the angle at which your data is displayed in a range. By default, Excel will display data horizontally, or at 0 degrees. You can change that up to 90 degrees up or down by dragging the control arm in the Orientation window and dragging it in either direction, or use the Spinner control at the bottom of the window (you can also enter your own value manually there).

Figure 91  

• • Be aware that any gridlines you might have applied will follow the same angle as your text; if you’re at 0 or either 90 degree intersection (Up/Down) you’re fine, but if you’re in between, like in the 45 degree example above you’ll get some strange behavior:

 

Figure 92  

• • Font – The Font tab on the Format Cells dialog is essentially an expanded version of what you can get on the Ribbon group, with the exception of being able to add the following effects to your range – There’s also a live preview window so you can see what your proposed changes will look like:
• • Strikethrough – Which looks like this.
• • Superscript – Which looks like this
• • Subscript – Which looks like this

 

Figure 93  

• • Border – This exposes the same functionality as the Border control on the Ribbon. Generally there’s not really any reason to use this dialog, unless you’ve launched the Format Cells dialog by using the Ctrl+1 shortcut.
• • Fill – The Fill tab on the other hand can be very handy because it exposes functionality that’s not included in the Ribbon, namely Fill Effects and Patterns.
• • Fill Effects – These are handy tools that allow you to apply gradient shading to a range. You can get very creative with these, and they can be great tools for formatting things like Dashboards or areas to which you want to attract special attention. Just remember that colors should be used sparingly and not too loud. Bright pink and yellow may look great to you, but probably not to someone else (like a banker or investor). Excel 2007+ gives you the ability to access a lot more colors than before, but that doesn’t mean you need to use them all. The following example shows you fill effects applied to a range of cells, then to a range that’s been merged in a Dashboard type of example.

 

Figure 94  

 

• • Patterns – These are very simple cross-hatched and dotted designs you can apply to a range. Note that they can make it difficult to read the underlying data and some printers that can’t achieve the detail necessary to render them will simply render them as black.
• • Protection – The ability to protect Excel worksheets is one of its coolest features. You can protect a worksheet so that no one can make any changes, or you can unprotect certain cells so that users can only enter information where you want. You can also hide formulas from view so that only their resulting values are visible. By default all cells on a worksheet are Locked. In order to allow entry, you need to select those cells, then in the Protection tab uncheck Locked (you would check Hidden if you don’t want your formulas to be visible, but you’re generally not going to mix the two, as you normally won’t have formulas in data entry cells). After doing this, when you protect the worksheet (see the Review Tab on the Ribbon), only those cells will accept entries.

CAVEAT: Protected workbooks and worksheets are only as good as your recipients. Excel is not a secure environment, nor has it ever been marketed as one. If you need to distribute sensitive material and there’s the slightest chance that someone could alter or otherwise use your data in a manner inconsistent with your expectations, then save it as a PDF. Think of Excel protection like a lock on a door; all it does is keep honest people honest. Excel security is good enough for 99.9% of the population, but just be aware that if someone wants to break your passwords and access your information they can…

 

• • Alignment
• • The Alignment Ribbon group is where you control how your text will behave in a range. This is probably as close as you’ll get to trying to have Excel act like Word. Just be aware that it’s limited in what it can do (just as Word is limited in the calculations it can perform - But the two programs do work brilliantly with each other as we’ll discuss in the Mail Merge lesson). Note that everything you see in this Ribbon group is also contained in the Format Cells dialog we just discussed. Again, Microsoft has given you multiple ways to expose certain command elements. In many cases, using CTRL+1 to launch the Format Cells dialog is a lot faster than using the mouse to get to the Ribbon commands, but it’s all personal preference.

 

Figure 95  

• • In the following example you’ll see what might be a bit surprising with the way things are lined up, but it’s completely natural: Excel will align text to the left of a cell and numbers to the right of a cell.

 

Figure 96  

• • The alignment options here on the Ribbon are almost the same as are contained in the Format Cells dialog. On the top are your Vertical Alignment options (Top, Center, Bottom) and beneath that are the Horizontal options (Left, Center, Right), but to Justify text you’ll need to open the Format Cells dialog and look in the Horizontal options there.
• • Next you have the Orientation options, and beneath that are the Decrease/Increase Indent controls.
• • To the right of those is the Wrap Text option and the Merge & Center, which you should avoid at all costs (hence it being marked out!)

 

• • Number
• • Most of your numeric formatting options are here, but if you want something not in the default list, then you can either click the More Number Formats selection at the bottom or use the dialog launcher, both of which will launch the Format Cells dialog. Note that in the Number drop-down Excel will preview what your data will look like (provided it is a number, if you’re attempting to format a text range then all you see is text with no formatting).

 

Figure 97  

• • Both the Currency and Comma number format will give you two decimal places by default. If you want more or fewer decimal places you can use the decimal toggle buttons to the right. The Currency selection has an additional option that will let you change the currency symbol (Dollar, Pound, Euro, Yen & Franc). If you need more, there’s a list of expanded options.
• • Styles
• • Styles are where you can really put Excel to work in terms of making your work look good with minimal effort. This section will get you started with knowing where everything is, but the only way to really learn to apply the different options is with practice, so start seeing what you can do.

 

Figure 98  

• • Conditional Formatting - This allows you to apply rules to cell values and graphically distinguish those cells that meet the criteria. Common uses are for dates that fall before or after a certain period or to highlight cells that are above/below a certain value. For instance you can format all cells where customer payments are over 30 days due.
• • There are five major Conditional Formatting components:
• • Highlight Cell Rules
• • Top/Bottom Rules
• • Data Bars
• • Color Scales
• • Icon Sets
• • Within those components, you have the ability to define formats based on:
• • Cell values (like High and Low ranges)
• • Only format cells that contain certain values
• • Top or Bottom ranked values
• • Values above or below average
• • Format only unique or duplicate values
• • Use a formula to determine what cells to format – This is probably the most powerful because you can write your own complex rules, but by no means are any of the other choices lightweights!
• • Data Bars, Color Scales and Icon Sets are all new additions to Excel with the introduction of the Ribbon and they can really help you tell a story with your data quickly. They can be especially good tools for people who aren’t graphically oriented. If you look in the Lesson 3 workbook you’ll see examples of them.

 

Figure 99  

Format as Table – This is another cool feature that lets you quickly apply formatting to your data, provided it has as contiguous structure. Select any cell in your data area then select the table format that you want to apply from the table gallery. As soon as you select a table style you’ll get a dialog asking you if it got the range right and if you have headers for your data:

 

Figure 100  

• • Make sure that if you do have headers for your table (always recommended) that you select this option or else you may get unintended consequences, like funky table formatting where your headers get lost in the shuffle.
• • Once you select the Gallery Style you want, then your unformatted data will look like this:

 

Figure 101  

 

• • Note the callouts in the image that point to Data Filter drop-downs in your header row, and the Table Fill Handle. Both of these are very handy tools that can speed up your work.
• • Data Filter allows you to quickly filter your data table by the criteria you select. Clicking on any of the drop-downs in any header column will expose the Data Filter options:

 

Figure 102  

• • Certain types of data will expose certain Filtering options, for instance a date column filter will let you filter by week, month, quarter, etc., while a numeric filter will let you select values equal to, greater than or less than a certain value, top ten values, averages, etc. Filtering is a hugely powerful tool for displaying just the snapshot data that you want to see at any given moment. And Excel doesn’t alter your base data, it just hides what you don’t want to see until you want to see it again.
• • The Table Fill handle lets you drag the format of the table down as far as you want it. Although once you’ve created a data table, all you need to do to expand it is add data to the bottom. Excel will automatically add it to the table and format it for you.
• • Now if you go to the bottom of the table and select any function you want from the AutoSum control from the editing toolbar, the data table will automatically add a Totals row and the function you chose will apply to the entire column. When you do that you’ll see that you can quickly toggle between functions.
• • Styles Gallery – The final element of the Styles Group is the Styles Gallery. With this tool you can select a range of data, let’s say a header row, or individual input cells, and quickly apply a format to give users certain visual clues as to what they should do. This can be a very useful tool for your users, but there are a lot of options here, and it’s very easy to add so many to a worksheet that it’s more confusing than helpful, so make sure to use them judiciously.

 

Figure 103  

• • Visual Clues – Who Really Cares?
• • The easier you make it for people to interact with your workbooks and worksheets, the least resistance you’ll find when getting them to use them. If you make it difficult for people to give you the information you need then they’ll make getting it difficult as well. You can easily overwhelm users with too many styles, but as you attempt to direct users to enter data in certain areas, it can be a very important feature to providing seamless user interaction. Balance is key, but it’s up to you to determine what’s right for you. Seamless user interaction also goes back to the comments on Worksheet Protection and how you can unlock certain cells for data entry and direct users to those cells/ranges. You can make that interaction easier with styles, but the folks at Microsoft understand that they can’t make everyone happy, so they give you the chance to define your own way of doing things wherever possible which you’ll find by clicking on the New Cell Style button. From there you can define your own style(s).

 

Figure 104  

• • Cells - The options on the Cells group are as simple as they come. Insert/Delete Rows & Columns and apply formatting elements like height, width. There are also some more advanced options that deal with the not just Rows & Columns, but the worksheet itself.
• • Visibility – You can hide Rows & Columns in any worksheet. If you protect the worksheet prior to distribution, your users can’t unhide those Rows & Columns. Let’s say you need to send out some information from an employee table, but you don’t want anyone to see certain details, like Social Security Numbers. Simply hide that column and protect the sheet. You also have the option to hide the worksheet itself, which can come in very handy if you have sensitive information on some sheets and you don’t want people to see it.

 

Figure 105  

• • Organize Sheets
• • Rename Sheet - You can rename the sheet through this control, and you can also double-click on the worksheet tab itself. Note that you can’t have invalid characters (: \ / ? * [ ] )in a sheet name and it can’t exceed 31 characters.
• • Move or Copy Sheet – this is a neat feature that lets you perform some tasks that you would otherwise need to do manually.

 

Figure 106  

• • In the “To book“ section you can choose any open workbook, or even have Excel create a new workbook for you. From there your option is to move the sheet to another location in the current workbook or to another one. You can also create a copy of an existing worksheet. This can be particularly handy if you don’t want someone to see your entire workbook and you don’t want to go hide all of your worksheets. Let’s say you have a workbook with all of your prices by supplier, and each supplier has its own worksheet. You can copy one supplier’s worksheet to a new workbook, send it to them for updates, and then replace it when they send it back.
• • Tab Color – After years of begging for this feature, which has been a part of Lotus 1-2-3 for years, Microsoft finally gives you the ability to color your worksheet tabs. This is a great feature because it helps quickly group/identify which worksheets do what. For instance, data entry can be yellow, reports - green, sensitive data - red, summaries – blue, and so on. Again, you can go overboard with your color selections, so don’t try to do too much lest you actually end up confusing your users.

 

Figure 107  

• • Protection – We discussed worksheet protection, and this just gives you another location to invoke it.
• • Format Cells – This brings up the Format Cells dialog, so this is again, just another place where Microsoft gives you access to a particular tool in multiple places.
• • Editing- This is the last stop on the Home Tab, and is where you’ll come a lot to quickly add functions without writing them yourself, add or remove data to/in ranges with just a few clicks, Sort & Filter your data and Find and/or Replace hard to spot data.
• • AutoSum – If you’re not all that comfortable with Excel’s functions, then this can do a lot of work for you. All you need to do is go directly beneath a column of data to the first empty row beneath the data and click the AutoSum tool. You’ll immediately be presented with a list of common functions:

 

Figure 108  

• • Sum – Returns the Sum of the values in the range
• • Average – Returns the Average of the values in the range
• • Count Numbers - Returns the Count of the values in the range
• • Max – Returns the Highest value in the range
• • Min – Returns the Lowest value in the range
• • The More Functions option will launch the Insert Function dialog, which we’ll discuss in more detail in the Function lesson.

 

Figure 109  

• • Fill – This is an interesting feature, and it’s an extension of what you can do with the AutoFill Handle. If you’re comfortable with the AutoFill handle and what it can do, there is probably only one compelling reason to use this command, and that is you can tell it to fill a number of rows or columns you choose, instead of dragging the handle all the way yourself (so if you don’t want to drag down/across 1,000 rows/columns you’d use this). Unfortunately, the Fill dialog isn’t quite as intuitive, and requires a bit of work on your part to make it do what you want. For the purposes of this class we’re not going to get very deep into this, but you should certainly try it on your own and see if it brings value to you in your situation.
• • As you already learned, the AutoFill command will allow you to input the beginning of a series and Excel will do its best to complete the series for you. Let’s say you wanted a list of row numbers, you could type 1 & 2 in adjacent cells, select them both and then drag the AutoFill handle down and Excel would complete 1, 2, 3, 4, 5, for as long as you drag it. You can create an even numbered list by entering 2 & 4, then filling, while odd would simply be 1 & 3. You can do the same with Dates, Months, etc. Excel will try to recognize the pattern and complete it for you. This can be a huge timesaver when it comes to setting up your worksheets.
• • With the Fill command you don’t enter your initial data in one cell then the increment you want in the next cell (e.g. 1 & 2), you simply enter your starting value in any cell, then select the Fill option, where you have several options:

 

Figure 110  

• • Let’s say you want to start with 1 and fill it down, but increment by 0.5 for each subsequent row. Enter 1 in A1 and reselect A1, then invoke the Fill command and select Series:
• • The default options will be Rows (so change that to Columns to go down), and Linear, which simply adds the Step Value you input to the next entry down/across). Then in the Step value enter .05, and in Stop value enter the maximum number to which you want extend this iteration. Hit OK and you’ll see something like this:

 

Figure 111  

• • That’s a lot easier than trying to do it with the AutoFill handle, especially if you’re looking at hundreds even thousands of rows. And you can do the same thing across columns instead of rows.
• • Your other options are:
• • Growth – This will build your list by multiplying each subsequent value by whatever you put in the step box.
• • Date – This allows you to increment dates and when you select it you’ll see the Date Unit options become enabled.
• • AutoFill – This extends the same AutoFIll behavior as dragging the fill handle.
• • Justify – This allows you to work with text without having to Merge Cells or Wrap Text. It’s invaluable for maintaining data integrity if you do happen to have text strings in a document.
• • If you want to use Excel as a text editor, which isn’t recommended, you do have some options with arranging text the way that you need it. Let’s say you have a long line of text that you’ve input to be at the bottom of a form, but you want it all to wrap within the confines of your form’s width.

 

Figure 112  

• • You could try to select the right number of cells that the text might fit into and then Word Wrap/Merge Cells and experiment with it a bit. Or you could use the Fill, Justify command to do it for you. Just select the cell holding your text, drag it down to the end/side of your form, then select Fill, Justify:

 

Figure 113  

• • And you’ll end up with this:

 

Figure 114  

• • Note that Excel automatically slices your text string into smaller chunks that fit the range you selected. As you look at it, yes, it has been physically separated, but don’t worry, Excel can put it back the way it was too. To revert back to your original text string, just select the entire range of justified text then extend it across as many columns as it will take to put it back. From there select Fill, Justify and Excel will reverse it for you.

 

• • Sort & Filter - Sorting is something you’ll probably do quite a bit of if you have data that you want to rearrange. To quickly sort data, just put the cursor in any cell in any dataset (the key is to put the cursor in the column that you want to sort first), then select the Soft & Filter option. Excel will automatically select the sorting range for you based on where the cursor is at the time. If you want to sort by multiple criteria, then you need to select the Custom Sort option, which will launch a new dialog:

 

Figure 115  

• • With earlier versions of Excel you only had 3 Sort by options, now you have up to 64! Although it’s not likely you’ll ever need that many. There is a Sorting & Filtering worksheet in your Lesson 3 companion workbook that you can work on to see all of your sort options.

WARNING: One of the best ways to completely screw up your data when sorting is to have Column gaps in it. If you invoke the AutoSort command, then please make sure that any data you want to sort is in contiguous ranges and doesn’t have any empty columns between vital data. E.G. if you put an empty column in between your data to give it a visual spacer (which is very common when people want to separate right justified numbers from left justified text), Excel won’t recognize the range past the blank column UNLESS you have manually selected the entire range (and even then you can have problems). The result will be that the portion of your data where the cursor was when you invoked the sort will be fine. BUT, the range that was outside of the range because of that Column separation will no longer be associated with the data that was sorted! If you find yourself in this position then the first thing to do is UNDO (Ctrl+Z)! If you’re too late to catch that, then close the workbook without saving it! You might lose some work, but at least your customer data won’t be completely screwed up.

• • Here’s an example of some customer data with an empty column (D) as a spacer:

 

Figure 116  

• • Here’s what happens if you choose to auto sort from the Ribbon with the cursor in cell A1 and not select the entire range:

 

Figure 117  

• • Uh ohh, Excel just did exactly what you told it to do, which was to sort the contiguous range. But look what happened to the items and Invoice amounts! They didn’t change in relation to their rightful orders…Unfortunately, it’s not uncommon at all to lose an entire data set because of something like this.

 

• • Now here’s an example of the same dataset sorted, but with the entire range manually selected before invoking the sort:

 

Figure 118  

• • The rule of thumb here is to let Excel perform relatively simple sorting for you. If you need to go beyond that then you need to go through some manual steps, but fortunately, they’re there for you.
• • In the Sort dialog you have Add/Delete/Copy Level options, which let you add or delete criteria, and copy one level to repeat, as well as an Options selection. The most important menu item here is the “My data has headers” check box. In most cases your data will have headers (as in our previous customer example), so you’ll generally want to have this selected. If you don’t, then Excel will sort your header row along with all of your data. If that happens you’ll know about it immediately, because your header will disappear to some place in your data.

 

• • Find & Select – This Ribbon group exposes a lot of functionality that can come in very handy.
• • Find – This does just what it sounds like, it lets you find items in a worksheet or an entire workbook (text or values), formulas and in comments. You can even tell it to find certain applied formats. Simply enter your search criteria and tell Excel how you want to search for it. When you first call the Find dialog you’ll see a short version, and an expanded version that you expose by clicking the Options button:

 

Figure 119  

• • Something to note is that the simple Search doesn’t always return the results you expect, so if that happens, expand the Options dialog and narrow your search criteria:

 

Figure 120  

• • Search Criteria
• • Within – Sheet or entire workbook
• • Search – By Rows or By Columns
• • Look in – Formulas, Values or Comments
• • Match case – Allows you to specify an Upper or Lower case search
• • Match entire cell contents – Only use this one if you know exactly what you’re looking for, like a product or employee name.
• • Method
• • Find All – Finds all matching instances of your search criteria
• • Find Next – Finds the first instance of your search criteria. Each successive button press will move on to the next match.
• • Replace – The Replace dialog is contained in the Find dialog and follows the same constraints as Find, but it allows you to not only find something, but replace it with something else. This is incredibly useful for making mass changes to a worksheet or workbook, and not have to do it manually. It’s especially powerful for making changes to a lot of formulas too, just note that it will replace whatever you tell it to, so you can inadvertently reduce your formulas to garbage if you’re not careful. Replacing “E” with “A” in this formula (=Sheet1!E1) without selecting the Match Case option will result in =ShAAt1!A1, which won’t be exactly what you were after (Excel will also pop up an irritating dialog asking you to locate the workbook where “ShAAt1” is located – you’ll learn this lesson when you change 500 formulas at once and get the criteria wrong). When replacing formula elements it’s best to be as specific as possible. In this case you could have used Find: “!A”, Replace with: “!E”, which would resolve the problem. As you start getting into more functions later in the course, you’ll be able to quickly identify which elements you can use in your Find/Replace criteria.
• • Go To – This gives you the option to go to a specific cell or range, and will also give you the option to select the Go To Special Option. If you need to do this it’s generally much faster to just use the F5 shortcut key to invoke this dialog, or even just enter the cell address in the name box (left of the Formula bar).

 

Figure 121  

• • Go To Special – The Special dialog gives you a lot of features that you probably won’t use at a beginner or intermediate level, but it does a lot of things of which you should be aware. Here you have the ability to select cells with all kinds of different characteristics, such as all the formulas or blank cells in a region. Think of the Special dialog as an auditing tool that lets you identify areas that you would otherwise need to find manually.

 

Figure 122  

• • Criteria
• • Comments – Finds all cells with cell comments (Comments are entered through the Review Ribbon group cell, and are addendum you can make to cells that are independent of the cell’s value – think of them like sticky notes for users).
• • Constants – Any non-calculated cells, like text entries (e.g. employee names, city, state, etc.)
• • Formulas – Any calculated cells
• • Blanks – Any blank cells in a region. This one can be really helpful for identifying or filling in missing data.
• • Current Region – Selects the current region of contiguous cells. This is helpful if you have disparate datasets on the same worksheet and need to isolate them for sorting, charting, etc. You can accomplish this with the shortcut Ctrl+* (from the 10-Key pad), or CTRL+SHIFT+* from the regular keypad.

 

• • Current Array – We won’t cover Arrays in this course, but they’re a way of getting Excel to evaluate a large range and produce multiple results. If you’re interested in Arrays you’ll find lots of documentation on the Internet and in the Help File, but they can be very difficult to implement, and often inefficient with regards to calculation dynamics. Suffice it to say, it’s not likely that you’ll ever need to get this far.
• • Objects – This selects any objects you may have on a worksheet (buttons, graphics, charts, etc.)
• • Row Differences – This will highlight any rows that are different from the active cell. It’s essentially invoking the error checker in case you turned it off. Note that you need to select a range or entire row to be able to do this; if all you have selected is the active cell, then it won’t do anything.
• • Column Differences – Same as the Row differences option, but selects column differences.
• • Precedents – This finds any cells that lead to the active cell via formulas (any cells that the active cell references).
• • Dependents – This finds any cells that are dependent on the active cell for calculations (any cells that reference the active cell).

 

• • Last Cell – This finds the last cell in a worksheet that contains data. This one can be very useful if you have what should be a relatively small workbook that’s blown up in size.
• • Visible Cells Only –Why do I need to select Visible Cells Only? Well, Data Filter is great for segmenting data that you need to distribute to individual employees or customers, but you might find that once you apply a filter to get just the data you want shown, when you copy the data to a new workbook all of the data you filtered out is shown! What happened? Excel doesn’t know you don’t want the entire dataset, and it doesn’t want you to forget it, so it copies all of it. Using the Visible cells only option will only copy the data you filtered. Do this once or twice and you’ll have one of those “Ahh-haa!” moments.
• • Conditional Formats – Selects all cells that have Conditional Formatting applied. Why is this important? Not really, but if you’ve applied some formats and forgotten about them, this will help you find them. This generally isn’t a problem, since Conditional Formatting is very visual.
• • Data Validation – This is similar to the Conditional Format option, but Data Validation cells can be harder to find than Conditional Formats, as Data Validation options don’t appear until you actually activate a cell. Both of the above can be handy tools when you’re preparing a workbook for distribution and want to make sure you have the user interface is perfect.
• • Formulas – This will identify all formulas on a worksheet. It’s useful for if you want to distribute a workbook without formulas and check if you haven’t gotten rid of all of them. It also gives you some useful functionality in that you can edit the first cell (F2), then Tab between all of the selected Formulas cells in case you need to edit them.
• • Comments – This will find any comments you might have entered. Again, why is this important? Probably, not really, but if you have a workbook with comments to aid users with data entry and now you’re at the distribution stage you might want to know where there are in case you need to get rid of them.
• • The remaining menu items in the Find & Select group (Conditional Formatting, Constants, Data Validation, Select Objects and Selection Pane) will expose the same elements as the Go To Special dialog, but give you a more direct approach than the Go To Special dialog:

Unit Summary: Lesson 3 – The Ribbon In-Depth – The Home Tab

• • In this lesson you learned about the in-depth ins and outs of the Home tab on the Excel Ribbon. Since it’s where you’ll generally spend the most time, it’s probably the most important Ribbon element to learn.
• • You walked through the Home tab’s elements, and also saw how there are multiple ways to expose the elements with keyboard shortcuts and Dialog Launchers.

Review Questions – Lesson 3 – The Home Tab

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

a. __________________________________________________

b. __________________________________________________

c. __________________________________________________

d. __________________________________________________

2. On the Clipboard group, what does the Format Painter do?

a. __________________________________________________

3. If you wanted to change formatting elements on a worksheet, how many ways can you do it, both through the Ribbon and shortcuts?

a. __________________________________________________

b. __________________________________________________

c. __________________________________________________

4. Why would you want to format your data as a Table (Styles group)

a. __________________________________________________

5. Why would you want to use Styles?

a. __________________________________________________

b. __________________________________________________

6. Where do you find quick access to functions?

a. __________________________________________________

7. What would you do if you wanted to fill in a series of dates, but skip weekends?

a. __________________________________________________

Lesson Assignment – Lesson 3 - The Home Tab

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

• • The Home Tab
• • General Navigation – What can you find? What can’t you find?
• • Paste Operations & Fill Effects
• • Skip Blanks & Transpose
• • Text Alignment
• • Conditional Formatting & Styles
• • Sorting & Filtering
• • Explore the Helpfile