eBook
Available on Compatible NOOK devices, the free NOOK App and in My Digital Library.
Related collections and offers
Overview
Product Details
| ISBN-13: | 9781615473311 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 12/01/2012 |
| Series: | Excel for Professionals series |
| Sold by: | INDEPENDENT PUB GROUP - EPUB - EBKS |
| Format: | eBook |
| Pages: | 247 |
| File size: | 13 MB |
| Note: | This product may take a few minutes to download. |
About the Author
Read an Excerpt
Excel for the CFO
By P. K. Hari
Holy Macro! Books
Copyright © 2013 Tickling Keys, Inc.All rights reserved.
ISBN: 978-1-61547-331-1
CHAPTER 1
Brushing Up on Excel 2010 Basics
Welcome aboard! This book is written based on actual experience using Excel 2010 for practical problem solving in the finance organization. The solutions and scenarios you will encounter here have been time tested and perfected. They will help jump-start your innovative thinking about new challenges that you encounter in your work.
Because this book is designed for finance professionals, it assumes that you are already aware of the fundamentals of Excel operations. However, you may be new to using Excel 2010 or find it handy to have a refresher, so this chapter provides a bit of guidance on the basics. (If you're a power Excel user, you may want to take a quick glance through this chapter and then head for Chapter 2.)
Here's what this chapter covers:
• The parts of an Excel workbook
• The ribbon in Excel 2010
• The Backstage view
• File-handling operations
• Navigation and selection
• Data editing and formatting
The Parts of an Excel Workbook
To make the best use of this book — and Excel 2010 — you need to understand some basic terms and know the various parts of an Excel workbook. This section will re-familiarize you with Excel's parts and terminology.
To begin, take a look at Figure 1, which shows the parts of a basic Excel workbook. The following sections examine each of the parts called out in this figure.
The Title Bar
The title bar shows the name of the active file (the file that is currently open) followed by the name of the application. For example, the title bar in Figure 1 says "Book1 - Microsoft Excel," which indicates that Book1 is the active file and Excel is the application.
Note: Previous versions of Excel presented the information on the title bar in the opposite order: the application name followed by the name of the active file (for example, "Microsoft Excel — Book1"). What a simple, thoughtful change!
The Quick Access Toolbar
The Quick Access Toolbar is a customizable palette of the commands you use most often. By default, the Quick Access Toolbar has Save, Undo, and Redo buttons, but you can easily customize it. Here's how you customize the Quick Access Toolbar:
1. Click the down arrow at the right end of the Quick Access Toolbar. A drop-down menu appears, showing some commonly used commands.
2. Select from the drop-down list those commands that you want to add to your Quick Access Toolbar. Deselect commands from this list to remove them from your Quick Access Toolbar.
3. To add to the Quick Access Toolbar commands that don't appear in the drop-down menu, click More Commands. The Excel Options dialog appears, as shown below.
4. Select the Choose Commands From drop-down to see a huge variety of commands and macros.
5. If you see a command or macro you want to add to your Quick Access Toolbar, select it in the box under Choose Commands From and click Add. Excel adds it to the Customize Quick Access Toolbar list on the right.
Note: You can use the Customize Quick Access Toolbar drop-down on the right side of the Excel Options dialog to choose whether to apply your customizations to the current workbook only or to all documents.
6. When you're done adding commands and macros to your Quick Access Toolbar, click OK to close the Excel Options dialog. Excel adds your choices to the Quick Access Toolbar.
Tip: Finance folks commonly add favorites such as Paste Values, Print Preview, Trace Precedents, and Trace Dependents to the Quick Access Toolbar. You can choose to add these or any other commands you use often.
The Ribbon
Office 2007 introduced the ribbon as a replacement for the traditional menu bar. Each of the tabs in the ribbon more or less replicates the earlier menu groups — but in a more orderly and comprehensive grouping. You'll learn more details about the ribbon a little later in this chapter.
Tip: You can click the Minimize Ribbon button (or press Ctrl+F1) to temporarily hide the ribbon if you want to view a larger work area.
The Help Button
The Help button, as you might guess, takes you to a search box that offers you options of both online and offline Help topics. In Excel 2010, as in other Office applications, you can press the F1 key instead of clicking the Help button to bring up Help.
The Minimize, Maximize, and Close Buttons
There are two sets of minimize, maximize, and close buttons: one at the application level and another one at the worksheet level. You can use these buttons to minimize, maximize (or restore), and close Excel or the current worksheet.
Tip: Microsoft generally offers a few ways to approach a task. For example, instead of using your mouse to click the minimize, maximize, and close buttons, you can use the following keyboard shortcuts:
Ctrl+F9: Minimize the active file.
Ctrl+F10: Maximize/restore the active file.
Ctrl+W: Close the active file.
Alt+F4: Close Excel.
Rows and Columns
A worksheet is a collection of many cells, each of which is formed by the intersection of a row and a column. A cell is identified by its cell address, which is a combination of the column letter and row number. For instance, the cell address of the 26th row in Column A is A26.
Microsoft Excel 2010 supports a maximum of just over 1 million rows (1,048,576, to be precise) and 16,384 columns. This is a welcome change for many corporate users who were inconvenienced by the 65,536-row limit in previous versions of Excel.
The active row is the current working row of a worksheet. You can select the whole active row by pressing Shift+Spacebar. Similarly, the active column is the current working row of a worksheet. You can select the entire active column by pressing Ctrl+Spacebar.
The Active Cell
The current working cell is called the active cell in Excel, as well as in Visual Basic for Applications (VBA). The active cell is identified by a thick black border around the cell. In addition, the row and column headings of the active cell are highlighted in a different color to make it easy to identify the current working row and column.
The Formula Bar
The formula bar shows the formula contained in the active cell and makes it available for editing (see Figure 3). The formula bar is handy because the active cell usually displays only the results of the formula it contains. As with many other things in Excel, you can control whether you want to see the formula bar. To hide the formula bar, you select the View tab in the ribbon and deselect the Formula Bar check box.
You can change the size of the formula bar in order to view more or less content. You click the down arrow that appears at the end of the formula bar (or press Ctrl+Shift+U) to toggle between a large formula bar and a smaller one.
The Function Wizard helps you select and insert functions into the active cell. To activate it, you click the Function Wizard (fx) button to the left of the formula bar (or press Shift+F3).
When you are editing a formula in the formula bar, you see two more buttons to the left of the Function Wizard button: the x and ([check]) keys. You can use these to cancel (x) the current edit or apply your formula changes to the active cell ([check]).
The Name Box
The name box and named ranges are some of the most important features available in Excel. The name box is especially useful when you're navigating in a large worksheet. If you have a fairly good idea of which row and column number you want to view, you can click the name box, type the cell address (for example, AA1875), and press Enter. You are then transported directly to the chosen cell. (Another way to navigate around a worksheet is to press F5, which brings up the Go To dialog.)
The name box is also useful when you have named ranges. You can select one of the available ranges by using the name box drop-down. (You will learn more about named ranges in Chapter 5.)
Note: Sadly for power users, Excel doesn't offer a predefined shortcut key that you can use to immediately access the name box. To overcome this hassle, Pearson Consulting has created a workaround — a macro you can use to set the focus to the name box. Check out this link for details: http://www.cpearson.com/excel/NameBoxShortcut.htm
The Scrollbars
Scrollbars have been familiar to computer users since the days of Windows 3.1, so they don't require much explanation. Excel has both horizontal and vertical scrollbars.
You can control whether the horizontal and/or vertical scrollbars should be displayed on a workbook. To do so, you select File, Options. In the Excel Options dialog that appears, you select Advanced and then, under Display Options for This Workbook, you select whether to display the horizontal and/or vertical scrollbars.
The horizontal and vertical scrollbars automatically adjust with the size of the work area. By default, the vertical scrollbar in a new worksheet is visually just as long as the size of the screen. As you edit the worksheet, the vertical scrollbar adjusts itself to accommodate the data in the file. Let's say you enter some data in Cell C550. You then see that the vertical scrollbar has grown smaller in size; when you pull the scrollbar tab down to the bottom of the screen, you reach Cell C550. This handy navigation feature helps you more quickly access your data.
Tip: Pressing Ctrl+End also takes you to the last active cell in the current worksheet.
Tab Scroll Buttons
Tab bar scrolling has become a popular feature in various Windows programs — and even browsers. When there are too many sheets in a workbook, you can't see all their tabs within the available work area. In such a situation, you can choose to see the leftmost or rightmost sheet by selecting the leftmost or rightmost scroll button. Or you can choose to browse through the left or right side of the scrollbar, one sheet at a time, by using the second (browse left) or third (browse right) button.
Another available tab scrolling option is to right-click in the tab scroll buttons area to bring up a list of all the available tabs in the workbook. You can use this list to easily select and navigate to a tab. In addition, a couple keyboard shortcuts are available for tab scrolling: Ctrl+PgUp moves you one tab to the left, and Ctrl+PgDn moves you one tab to the right.
Yet another cool option available in the tab buttons is to jump to the next group of tabs (those which are not in view) — achieved by pressing shift while clicking on the tab navigation buttons. For instance, if you can see only 4 tabs in current view, and your file has more than 4 tabs, you can shift-click the Next tab button which would immediately scroll the other tabs in to view.
Note: Similar to the way you can choose to display or hide the horizontal and vertical scrollbars, you can choose to show or hide the display of tabs in a workbook. To do this, you select File, Options. In the Excel Options dialog that appears, you select Advanced and then, under Display Options for This Workbook, you select whether to display sheet tabs.
The Status Bar
The status bar appears at the bottom-left corner of the active workbook. It shows the current status of the workbook, which may be Ready, Edit, Circular Reference, or Calculate, depending on the circumstances.
Since Excel 2007, the status bar has been customizable. By right-clicking in the status bar, you get the context menu shown in the following figure.
Here's what you can customize by using this context menu:
• Whether the active cell mode is displayed
• The status of the information management policy, security, permissions, and signatures
• The status of display items such as caps lock, numbers lock, and scroll lock, along with fixed decimal display, overtype mode, end mode, and macro recording
• The status of the selection mode
• The status of the page number
• The status of mathematical functions, such as average, count, numerical count, minimum, maximum, and sum
• The display of upload status, shortcuts, zoom percentage, and the zoom slider
Note: The zoom slider is of great help in quickly adjusting the window size to suit your requirement, and it is certainly a welcome addition to the status bar.
The default selection in the mathematical functions section is Sum, but you can choose to select all six functions at the same time. When you do, the status bar shows the six different values for a group of selected items (see Figure 5). This is really handy because it means you don't have to insert in the worksheet a separate formula for each math function.
Tip: Excel 2003 made it possible to turn off the status bar. However, considering the great value that the status bar now provides, Microsoft has removed the option of turning it off. Power users can still count on VBA to turn it off though.
The Ribbon in Excel 2010
The ribbon was introduced in Office 2007, and it was a major change from the Office 2003 menu bar. However, the ribbon in Office 2007 was not very customizable; unless you used a third-party tool, you couldn't add new tabs to the ribbon, and you couldn't add or rearrange the commands on the ribbon. Many Excel users expressed their displeasure with this situation.
Note: Some users still find it difficult to understand why the ribbon was introduced and how to get around with the ribbon. To address such concerns, Microsoft has put together a collection or resources at http://www.office.microsoft.com/en-us/support/ office-ribbon-find-commands-FX101851541.aspx. Microsoft also provides a resource workbook that shows the menu-to-ribbon mapping for all commands that used to exist in the menu bar. You can download this Excel 2010 workbook from http://www.office.microsoft.com/en-us/templates/ excel-2010-menu-to-ribbon-reference-workbook-TC101842354.aspx? CTT=5&origin=ZA101859927.
Microsoft listened to users' concerns about the customizability of the ribbon. Office 2010 gives you full control over what you see on the ribbon. To customize the ribbon, you follow these steps:
1. Right-click anywhere on the ribbon and click Customize the Ribbon (or select File, Options). The Excel Options dialog appears.
2. Select Customize Ribbon on the left side of the Excel Options dialog (see following figure).
3. On the bottom-right side of the Excel Options dialog, click the New Tab button. You can then create a new tab and rename it with a name of your choice (why not yours?).
4. After you create a new tab, click the New Group button at the bottom right of the Excel Options dialog to create groups of commands. You can easily add commands to the group by selecting from the list of commands available on the left side and clicking Add.
You could create a custom tab for the ribbon that includes all your most frequently used commands. This would help you reduce your work time and streamline your productivity. In addition to the flexibility and comfort your new tab would give you, you'd have a sweet customized version of Excel that would be sure to impress your audience when you're giving a presentation.
Tip: Microsoft has made available some video tutorials that make it easier to understand the concept of customizing the ribbon. You can find them at http://www.office.microsoft.com/en-us/access-help/video-customize-the -ribbon-HA101850352.aspx.
The Backstage View
Excel 2010 has a brand-new feature called the Backstage view (see Figure 7). Many common activities — such as save, print, and share — have become one-click jobs from within the Backstage view. It also houses a lot of jam-packed features — so much so that the Backstage view could easily be called the center stage of Excel 2010.
When you click the File tab of the ribbon, the Backstage view appears. It makes available all the file-related commands, including Save, Save As, Open, Close, Info, Recent, New, Print, and Save & Send. In addition, it includes Help, Options, and Exit.
One useful feature in the Backstage view is Recent, which shows a short list of the most recently used workbooks. You can use this list to quickly return to a recently used workbook. You can control the number of workbooks that appear in this list by changing the setting for Quickly Access This Number of Recent Workbooks at the bottom of the Backstage view.
(Continues...)
Excerpted from Excel for the CFO by P. K. Hari. Copyright © 2013 Tickling Keys, Inc.. Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.