 |
Add caption
|
The Excel Ribbon is one of the first topics covered on our Introduction to Excel. Ribbon, first introduced with Microsoft Office 2007, is the key component of the Excel interface and offers access to most of its features, facilities and utilities. However, as is now the case with all sophisticated software packages, there are often several other ways of accessing Excel’s features and bypassing the Ribbon.
The Ribbon consists of a series of tabs, each of which offers commands related to a certain theme. These commands are further subdivided into Groups, with the name of each Group displayed at that the bottom of the Ribbon.
The Ribbon is a very honest and open interface: basically, everything is laid out for you: the simple, the intermediate, and the advanced; it’s all there; nothing is hidden. There follows a brief description of each of the Ribbon Tabs and the Groups of controls found in each one. I’d recommend that you take some time reviewing the various Tabs: this will help in the process of demystifying Excel and making it seem less daunting.
Watch out for the launcher buttons
In the bottom right-hand corner of some Ribbon Groups, you will notice a tiny icon consisting of a rectangle with an arrow. As you browse through the Ribbon Tabs, keep an eye out for these rather inconspicuous buttons. They are called launcher buttons and what they usually launch are the rich and powerful Excel dialog boxes, many of which have been part of Excel for ages. For example, on the Home Tab, the Clipboard, Font, Alignment and Number Groups each contain a launcher button (See figure 1-19, below).
Launcher buttons are found in the bottom right of some Ribbon Groups and normally display a dialog box containing a detailed series of options.
Clicking on these launcher buttons will be particularly reassuring for those of you who have upgraded to Excel 2016 straight from version 2003, or earlier; since it will help you to realize that your existing skill set is still very relevant to this latest version.
The Home Tab
The Home Tab of the Excel Ribbon contains those essential editing and formatting commands which one uses all the time.
The Clipboard Group offers the long-winded way of performing the common clipboard operations: Cut, Copy and Paste. (Most people prefer to use keyboard shortcuts (Control-X, Control-C and Control-V) or to choose these commands from the right-click menu).
The Clipboard Group contains a launcher button which displays the Office Clipboard, in a task pane on the left of the screen. The Office Clipboard keeps track of up to 24 copied items at a time and allows you to paste any of them. It can come in quite handy when building complex Excel formulas.
The Font Group allows you to change the stylistic appearance of the text you enter into your spreadsheets such as its font, size and colour.
The Font Group contains a launcher button which displays the Format Cells dialog box, with the Font tab activated.
The Alignment Group provides commands for rotating and wrapping text—very useful for long headings—merging cells and controlling both horizontal and vertical alignment.
The Alignment Group contains a launcher button which displays the Format Cells dialog box, this time with the Alignment tab activated.
The Number Group provides options for data formatting—for example, controlling the number of decimal places shown with numbers, and the way in which dates are to be displayed.
The Number Group contains a launcher button which displays the Format Cells dialog box, this time with the Number tab activated.
The Styles Group provides automatic formatting options—in contrast to the manual formatting offered by the Font Group. These options include Excel’s powerful conditional formatting commands.
The Cells Group provides commands for manipulating cells, columns and rows: such things as inserting, deleting and specifying widths and heights. It also contains options for hiding, moving and protecting the sheet as a whole.
The Editing Group has a variety of popular commands for entering and modifying cell data. The AutoSum tool is used to rapidly create simple formulas with just one or two clicks. The Sort & Filter button can be used for quickly sorting data by the column in which the cursor is positioned. The Group also contains options for deleting, finding and replacing data.
The Insert Tab
The Insert Tab of the Excel Ribbon contains options for superimposing elements onto a worksheet which differ from the normal cell contents—items such as charts, imported images and vector graphics.
The Insert Tab is mainly used for creating graphic elements which are not contained by the cells of the worksheet.
The Tables Group allows you to create Excel tables (which are used to store columnar data) and pivot tables (which are used for data analysis). Although pivot tables store their data within worksheet cells, they then prohibit normal access to those cells; the cells within a pivot table cannot be individually edited.
The Illustrations Group contains options for adding bitmapped and vector graphics to Excel. Images can be inserted from a local drive or via the web: there is even the option of searching for images using Bing. Vector images can be drawn within Excel using the Shapes and Smart Art buttons. The Screenshot button allows you to grab any part of any window currently open on your computer screen.
The Apps Group lets you access Microsoft apps for Office solutions. Apps for office resemble iPhone and Android apps: they are essentially web pages which Excel (or any other Office program) can host; and which provide some useful functionality or service not native to the program.
The Charts Group allows you to add Excel charts and pivot charts to your worksheet. A pivot chart is simply a chart which uses a pivot table as its data .source, rather than a regular Excel data range.
The Charts Group contains a launcher button which displays the same dialog as is shown when you click the Recommended Charts button.
The Reports Group contains just one item: the Power View button. Power View is a built-in Excel add-in which just needs to be activated. It allows you to create stunning business visualizations driven by the new Excel Data Model.
The Sparklines Group allows you to insert sparklines in your worksheets. Sparklines are miniature charts which are displayed within cells, usually adjacent to the data they plot, and which provide handy visual clues regarding trends.
The Filters Group contains buttons for adding slicers and timelines to tables, pivot tables and pivot charts. Both of these elements provide a highly interactive mechanism for filtering structured data. Slicers can be used with any data type; while timelines can only be used to filter dates.
The Links Group contains only the Hyperlink button. Hyperlinks work in pretty much the same way in all of the Microsoft Office products. In Excel, you can place a hyperlink on any cell, or range of cells; which takes the user to another worksheet, opens a different workbook, creates a new email message, or goes to a web URL.
The Text Group allows you to insert text based elements which reside on the object layer, above the worksheet cells. You can add text boxes, headers and footers, Word Art, signature lines and use Microsoft’s Object Linking and Embedding (OLE) technology to insert objects.
The Symbols Group has options for inserting equations and symbols. Symbols can be inserted like any other character, inside a cell. However, equations are inserted in a text box on the object layer, on top the worksheet cells. Excel provides a sophisticated Equation toolbar for editing equations, containing dozens of mathematical and scientific symbols and constructs.
The Page Layout Tab
The Page Layout Tab of the Excel Ribbon contains options for controlling the appearance of your worksheets as a whole. Here, you’ll find settings relating to margins, orientation, size of paper, and the area of the worksheet to be printed. You will usually go to the page layout Tab before printing worksheets.
The Page Layout Tab contains all of the options relating to Page Setup.
The Themes Group allows you to make use of the built-in Themes feature now common to all Microsoft Office products. Themes provide a simple and quick method of controlling the format of an entire document. Choosing options from the Themes, Colors, Fonts and Effects drop-down menus, found in the Themes Group, can change the appearance of worksheet cells and graphic objects in one hit, using tasteful and well-coordinated tones.
The Page Setup Group contains the commands that you normally need to use before printing a document: for example, changing the margins, orientation and page size; or specifying a print area (whereby only a portion of the worksheet will be printed).
The Page Setup Group contains a launcher button which displays the Page Setup dialog box with the Page tab activated.
The Scale to Fit Group simply contains the commands found in the Scaling section of the Page tab of the Page Setup dialog.
The Scale to Fit Group contains a launcher button which displays the Page Setup dialog box, also with the Page tab activated.
The Sheet options Group contains options for hiding and showing gridlines and column and row headings, both on screen and at print time.
The Sheet Options Group contains a launcher button which displays the Page Setup dialog box, this time with the Sheet tab activated.
The Arrange Group is encountered in several different Tabs. It provides a series of commands allowing you to align and arrange objects: such as Bring to Front, Send to Back and Group.
The Formulas Tab
The Formulas Tab of the Excel Ribbon contains commands for dealing with one of the most powerful features of Excel: formulas and functions. Here you can insert functions, track down errors in formulas and control the way in which Excel deals with formulas and carries out its calculations.
The Formulas Tab contains Excel’s main options for creating, displaying and auditing formulas and determining how the program performs worksheet calculations.
The Function Library Group allows you to insert any of Excel’s several hundred functions into your formulas. The functions are divided into categories, with the final category (More Functions) displaying, in a series of sub-menus, those categories which do not have their own drop-down menu.
This group does not need a launcher button, since the main dialog associated with formulas (Insert Function) can be accessed via the final option in each of the drop-downs.
The Defined Names Group deals with the creation and management of names. Names are an Excel resource which allow you to associate a text label (the name) with a range of cells, a formula or a static value (such as a tax rate). When creating formulas, you can use the name instead of the value that it represents. This adds clarity to your formulas and offers you a powerful mechanism for updating several formulas by making a change to the value associated with a single name.
The Formula Auditing Group options are designed to help you find errors in your formulas. Using these options, you can quickly highlight cells which are referred to by a formula and vice versa, or step through a complex formula, statement by statement.
The Calculation Group has commands for controlling how and when Excel performs calculations. In workbooks that contain many formulas—especially formulas that link to external workbooks—the need to calculate the result of formulas can have a negative impact on the usability of a workbook. Hence, this group offers the ability to make calculation manual, so that Excel will only perform calculations when you click the Calculate Now button.
The Data Tab
In the Data Tab of the Excel Ribbon you will find commands for importing information into Excel from sources such as external databases; sorting information and also filtering (in other words returning only data which matches certain criteria).
Here you’ll also find advanced tools for consolidation, validating data and performing “What-if” analysis. Also, in the outline section, there are commands for assigning different levels to the various columns and rows of a worksheet. Excel’s powerful outlining commands make large worksheet easier to manage and to navigate.
The Data Tab contains Excel’s powerful data analysis tools.
The Get External Data Group contains commands which are used to import data from external sources into the Data Model, which can then be used to perform data analysis and visualization using pivot tables, pivot charts and the Power View add-in. These same commands can also be found in the PowerPivot add-in, which offers you a host of additional features for modelling imported data sets.
The Connections Group has options for working with existing connections. The Connections button displays the Workbooks connections dialog where you can review and remove connections. The Refresh All button allows you to replace obsolete copies of imported data, permanently stored within the data model, with the latest version from the original source.
The Sort & Filter Group commands allow you to perform basic and advanced sorting and filtering on tabular data. The Sort A to Z and Sort Z to A buttons allow one-click sorting by a single column; while the Sort button displays the Sort dialog allowing you to perform multi-column sorting, as well as sorting by colour and custom lists.
Similarly, the AutoFilter button allows rapid filtering of information according to preset criteria; whereas the Advanced Filter command allows you to isolate data which matches more complex criteria.
The Data Tools Group provides access to specialized, built-in Excel utilities each of which performs a useful data manipulation task. For example, the Text to Columns command allows you to parse data which currently occupies a single column (typically after being imported into Excel) into separate columns. The group also contains the powerful Remove Duplicates and Flash Fill commands.
The What-if Analysis tools (Goal Seek, Scenarios and Data Tables) are data analysis tools which have been part of Excel’s arsenal for ages.
The Outline Group is the home for another long-standing Excel feature called grouping and outlining: the assignation of a hierarchy of levels to different rows and columns. This capability is very useful for making large worksheets easier to manage and manipulate.
The Review Tab
The principal theme of the Review Tab is checking, protection and the multi-user sharing of workbooks. It is here that you will find Excel’s spell-checker, commands enabling users to add comments to a worksheet, options for protecting both worksheets and workbooks and for sharing workbooks with other users across a network.
The Review Tab contains options for the protection and sharing of workbooks.
The Proofing Group contains the familiar Spell-checker and Thesaurus commands; as well as a Research tool which allows you to perform searches in several local and online references, including Bing and Encarta.
The Language Group is a subset of the Research tool which allows you to translate common words and phrases from, or into, over thirty different languages.
The Comments Group provides the ability to annotate worksheets with notes; both for your own benefit and as a workgroup facility, for sharing your thoughts on worksheet content with colleagues. The Group has buttons for creating, editing, deleting, showing and hiding comments.
The Changes Group houses the most powerful commands in the Review Tab. The Protect Sheet and Protect Workbook commands can be used to restrict access to specific cell ranges, control user modifications, and to protect the workbook structure. There are also commands for the multi-user sharing of workbooks and the tracking of changes made by different users to the same workbook.
The View Tab
The View Tab houses controls relating to the way in which your workbooks and worksheets are viewed on screen. It contains commands for zooming in and out on your work and for displaying several worksheets and workbooks simultaneously.
The View Tab contains options which allow you to control the on-screen appearance of your worksheets.
The Workbook Views Group buttons change the way your worksheets are displayed: Normal view is optimized for speed; Page Break Preview allows you to customise pagination; Page Layout gives an accurate preview of how documents will print; and Custom View lets you save on-screen and page setup attributes.
The Show Group has commands which allow you to show and hide key elements of the Excel interface such as row and column headings, gridlines and the formula bar.
The Zoom Group buttons control the magnification at which you view your work. You can, for example, highlight a range of cells and click the Zoom to Selection button to have Excel automatically zoom in or out so that the selected range fills your screen.
The Window Group offers options which are useful when working with several different documents. For example, the Arrange All commands will reduce and size all of the currently open windows, so that you can see their contents simultaneously.
The Macros Group has a single drop-down control which allows you to view all of the macros in any of the open workbooks, as well as recording new macros. (Macros are automated series of commands which can be executed automatically at the click of a button.
