Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun
532
Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun
532eBook
Available on Compatible NOOK devices, the free NOOK App and in My Digital Library.
Related collections and offers
Overview
Product Details
| ISBN-13: | 9781615473021 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 02/21/2011 |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 532 |
| File size: | 11 MB |
| Note: | This product may take a few minutes to download. |
About the Author
Read an Excerpt
Slaying Excel Dragons
A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun
By Mike Girvin, Timmy Joy
Holy Macro! Books
Copyright © 2011 Mike Girvin and Holy Macro! BooksAll rights reserved.
ISBN: 978-1-61547-302-1
CHAPTER 1
How Excel Is Set Up
As we get started, we must look at how Excel is set up. The essence of Excel is that it has a rectangular shape that has two directions.
The left-to-right direction is represented by letters that indicate columns. The letters are called column headers. In Figure 1, you can see the vertical column C. As we move to the left from column C, the letters go backward, and as we move to the right, the columns advance through the alphabet. (When you get to Z, the next columns are AA, AB, AC, and continue to the last column, which is XFD, which is the 16,384th column.)
The up-and-down direction is represented by numbers that indicate rows. The numbers are called row headers. In Figure 1, you can see the horizontal row 5. As we move down from row 5, the row numbers increase (the last row is 1,048,576), and as we move up, the row numbers decrease.
The intersection of a row and column is called a cell or a cell reference. In Figure 1, you can see that the cell C5 is the intersection of column C and row 5.
All the cells together are called the worksheet or spreadsheet or simply sheet. There can be many sheets in an Excel file. The name of the sheet is shown in the sheet tab. In Figure 1, the sheets tabs are a dark color, and the active sheet tab (sheet showing) is a light color. The default names for the sheets are sheet1, sheet2, and so on.
Because the default names hinder efficient and robust formula creation and navigation through a workbook, you should always give the sheet a logical name. For example, if the sheet has sales data, name it something like SalesData. This way, when you look at the sheet or make a formula with a sheet reference (more later), you have a good idea about what the sheet contains. To name the sheets, just double-click the sheet tab, type a name, and press Enter. To select a sheet, simply click the sheet tab with your cursor. All the worksheets in an Excel file are together called the workbook. You can see the workbook name in the title bar at the very top of the window (excelisfun-Start.xlsm).
Because there are more than 150 sheets in the Excel workbook file named excelisfun-Start.xlsm that came with this book, we need to be sure that you know how to navigate through this large workbook to any particular sheet that we may be working with. In Figure 2, the active sheet is named Setup, and it is colored white to indicate that the cells from this sheet can be seen and worked with. To select a sheet, simply use your cursor (white diagonal arrow) to click the sheet tab. In Figure 2, the last sheet that we can see is named Decimals. But there are many more sheets beyond (to the right). There are three ways to access sheets that cannot be seen:
• Use the keyboard shortcuts Ctrl + Page Down to move to the next sheet in a workbook (thus making it the active sheet) or Ctrl + Page Up to move to the previous sheet in a workbook (again, making it the active sheet).
• Use the sheet navigation arrows. The arrows without vertical lines move the view of the sheets without changing the active sheet, and the arrows with the vertical lines jump all the way to the end or beginning of the sheets.
• Right-click any of the sheet navigation arrows, click More Sheets, and then navigate to whichever sheet you would like.
If you are not familiar with navigating in workbooks with a large number of sheets, try all three methods before reading further in this book. Doing so will help you to follow along with the more than 150 sheets in the Excel examples in this book.
Now we can state our first two Excel Efficiency-Robust Rules:
Rule 1: Excel sheets are rectangles with columns (letters) that move left to right and rows (numbers) that move up and down. A firm understanding of this will help us later to build formulas that are efficient and robust.
Rule 2: Always name sheets (double-click the sheet tab, type the name, press Enter) with an easy-to-understand name so that navigation through the workbook and formulas with sheet references are easy to understand.
Figure 3 shows a few more Excel elements that this book assumes you are familiar with, or at least have seen before.
Note: Your ribbon might look slightly different. This is because the groups in the ribbon will expand and collapse depending on two things:
• Whether your window is maximized or restored down
• The display resolution for your computer (Control Panel settings)
Note: There is one ribbon that has many tabs. The standard seven tabs are Home, Insert, Page Layout, Formulas, Data, Review, and View. There are many context-sensitive ribbon tabs that will show up when we use certain features. For example, when we make charts or pivot tables, specific ribbon tabs will appear when we work with the charts or pivot tables. In this book, when we want to get to the Insert or Page Layout part of the ribbon, I write, "Click the Insert tab or Page Layout tab."
Note: Because the ribbons take up a lot of space, you can hide and unhide them with the keyboard shortcut Ctrl + F1.
Note: You can add buttons that you see in the ribbons to the Quick Access Toolbar (QAT) by right-clicking a button in the ribbon and pointing to Add to Quick Access Toolbar. The advantage to this is that the QAT is always visible no matter what ribbon tab you have selected.
Now it's time to take a look at keyboard shortcuts.
CHAPTER 2Keyboard Shortcuts
Now we are about to learn the best trick in all of Excel! Yes, this is the one trick that will guarantee you extra vacation time and instant success in the eyes of your bosses and co-workers. The one trick is ... well it's not just one trick, it is many. Are you ready for this?
Learn keyboard shortcuts!
Keyboard shortcuts are one of the best ways to save time and become efficient. Let's look at a few examples here, and then throughout the rest of the book, you will see many more keyboard shortcuts.
To follow along, open the file named excelisfun-Start.xlsm and navigate to the KeyB sheet.
Ctrl Key Shortcuts (and a few others)
Figure 4 shows a summary sales report for January sales summed by product and sales representative. We would like to move this summary sales report from the cell range A1:F7 to H1:M7.
Moving Data
When you move something, there are four steps:
1. Highlight the current region (cell range with data) (Ctrl + *).
2. Cut the cell range (Ctrl + X).
3. Select the upper-left corner of the destination cell range. For example, select cell H1 if you are pasting the cut cell range into the range H1:M7.
4. Paste the cut cell range (Ctrl + V).
Highlighting the Current Region
In Excel, if a cell is selected, the term current region refers to the cells surrounding the selected cells that contain data. For example, in Figure 4 if cell A1 is selected, the current region is the range A1:F7 because the cells around the parameter of cell A1 contain data and because the data stops before the G column and the eighth row. This term is important because there is a great keyboard shortcut to select the current region. Now, Let's try an example.
1. Click the sheet named KeyB and then click cell A1.
2. To select the whole report use Ctrl + * (the * on the number pad).
The way most Ctrl keyboard shortcuts work is that you hold the Ctrl key (and keep it held), and then tap the second key. For example, our Ctrl + * would involve holding the Ctrl key and then "tapping" the * key, just as you would tap someone on the shoulder. We write this keyboard shortcut as follows: Ctrl + *
How is Ctrl + * fast? Highlighting a block of data like that would take a lot longer if we used our mouse to drag from cell A1 to cell F7. This keyboard shortcut is particularly amazing when you have large sets of data.
3. To cut the range of cells, press Ctrl + X.
In Figure 5, after pressing Ctrl + X, you should see "dancing ants" around the outside of the cut area. The dancing ants tell you that the range has been cut.
To paste the cut cell range:
4. Click in cell H1.
5. Press Ctrl + V.
You should see that your range has been moved to a new location. Notice that when you copy a range and paste it into just one cell, the range is pasted to the right and down.
How is that faster? Because you avoid moving your mouse up to the Home tab to copy and paste. Most of the time, using the mouse and the ribbon is much slower than using keyboard shortcuts.
Copying Data
In Figure 6, we see a summary sales report (range of cells). We would like to copy this report and paste it into a new sheet. To copy something, you must complete four steps:
1. Highlight the current region (Ctrl + *).
2. Copy the cell range (Ctrl + C).
3. Select the upper-left corner of the destination cell range.
4. Paste the copied cell range (Ctrl + V).
The complete list of steps follows:
1. To copy the range of cells, press Ctrl + C.
Before we can paste, we need to insert a new sheet to the left of the sheet named KeyB.
2. To insert a new sheet to the left of the sheet named KeyB, press Shift + F11.
3. A new sheet tab will appear. (It might have a different name from the one shown.)To name the sheet, double-click the sheet tab.
4. Type CopiedTable.
5. Press Enter.
6. After inserting the new sheet, A1 is selected; so to paste the copied range, press Ctrl + V.
Amazing Excel 2010 Ctrl Key Smart Tags
In Figure 10, we can see that after we paste, a bunch of pound signs (#####) appear. Pound signs mean that the columns are not wide enough to allow the data to be seen. Also notice in Figure 10 that there is a Ctrl key smart tag that has the Paste icon and the word Ctrl.
This is a new keyboard shortcut in Excel 2010, and it is one of the most amazing new features in Excel 2010. In older versions of Excel, fixing the column width problem was more difficult than it is with this new Ctrl Key smart tag. Let's see how this works.
7. Press the Ctrl key to open the smart tag.
8. Tap the W key to apply the Keep Source Column Width option.
Figure 12 shows the end result.
Keep Source Column Width means that we can keep the original, copied cell range's column width. Figure 11 shows that when the Ctrl key smart tag opens it shows icons. Microsoft put these there for people who like to use the mouse. If you hover your cursor (using your mouse) over the icons without clicking them, however, a screen tip pops up that has the keyboard shortcut in parentheses. In Figure 11, we can see that the letter W is the letter keyboard shortcut for applying the Keep Source Column Widths option.
So in total, the new Ctrl key smart tag keyboard shortcut for "paste everything we copied including the source column width" is as follows:
1. Press Ctrl + V.
2. Tap Ctrl.
3. Tap W.
This is much faster than how it was done in earlier versions of Excel. Also notice that the second part of the keyboard shortcut requires that we not hold Ctrl and press the second key. To keep source column widths, we had to use the original Ctrl keyboard method (pre-Excel 2010) of hold Ctrl and press V to paste, and then we had to use the new Excel 2010 Ctrl keyboard method of tap Ctrl and then tap W. By tap, I mean press Ctrl and then the W key in succession.
Going forward in this book, to help us distinguish between holding a key and tapping two keys in succession, we will use this convention:
• Plus sign (+) means "hold"
• Comma (,) means to tap in succession.
With this new convention, the keyboard shortcut for "paste everything we copied including the source column width" is as follows: Ctrl + V, Ctrl, W.
Figure 13 shows a list of the Ctrl key smart tag keyboard shortcuts. Learning the shortcuts for the operations that you use most often in your job will save you a lot of time.
Another option to the keyboard shortcuts in Figure 13 involves using the right-click key (in-between the Ctrl key and Window key on the right side of the keypad). Although I do not illustrate this method in this book, Figure 14 shows a list of these keyboard shortcuts that you can use after you have copied data using Ctrl + C.
Now let's look at a brilliant use of the Ctrl key smart tags for flipping a summary sales report of data on its side (transposing a range of cells).
Transposing a Range of Cells
In Figure 15, you can see we have a cell range of data. Our goal is to flip our range so that the row headers in Figure 15 become the column headers in Figure 16 and the column headers in Figure 15 become the row headers in Figure 16.
The process of flipping the row and column headers so that they become column and row headers, respectively, is called transposition. In Figure 17, you can see that the letter T will execute the transpose operation. Let's try this and see whether we can get it to work with keyboard shortcuts.
1. Select the range A1:F7.
2. Press Ctrl + C.
3. Select cell A10.
4. Press Ctrl + V.
5. Tap the Ctrl key.
6. Tap the T key.
7. Notice that the G column is not wide enough.
8. To make column G wider, hover your cursor in-between the G and H columns, and when you see the vertical black line and horizontal-arrow cursor, double-click.
Double-click between columns to change the column width to the largest item in the column (called best fit).
Figure 21 and Figure 22 show two alternatives for transposing a range of cells
After you copy, transposing can be done by going to the Paste drop-down arrow in the Clipboard group in the Home tab.
This method is particularly useful because it will give you a Live Preview! Live Preview is great if you are doing this operation for the first time. However, if it is a task you do regularly, the Ctrl keyboard method is significantly faster.
After you copy, paste and press Ctrl to open the Ctrl key smart tag, then transposing can be done by using your arrow keys to move through the icons. When you get to the Transpose icon and you see an orange highlight, press Enter.
This method is still faster than using the mouse to go to the Home tab.
Highlighting a Column of Data
Next we want to look at a keyboard shortcut that is a huge time saver. This keyboard shortcut involves the Ctrl key and the arrow keys (Up, Down, Left, Right). Let's take a look.
In Figure 23, the content of cell A11 is the column label Dec Sales. But how far down do the numbers go? You could use the scrollbar to scroll down and find out, but that would take a long time. Instead, we will use the keyboard shortcut to jump to the bottom of a column: Ctrl + Down Arrow.
This keyboard shortcut will jump to the bottom of the column of data. How does it know to stop? It will know to stop when it sees the first blank cell. This sort of keyboard shortcut is called a navigation keyboard shortcut.
Let's see how to do this.
1. Select cell A11 on Sheet KeyB.
2. To jump to the bottom of the column of data, press Ctrl + Down-Arrow.
3. To jump back to the top of the column press, Ctrl + Up Arrow.
4. To jump to cell A1, use Ctrl + Home-key.
5. To move to cell B1, press the Right-Arrow key.
6. The entire B Column has no data. So if we press Ctrl + Down arrow, we will jump to the last cell in the column.
7. To jump to B1048576, use Ctrl + Down Arrow.
8. To jump back to cell A1 , press Ctrl + Home key.
In the last few navigation keyboard shortcuts, you saw how to jump the cursor to a new location. In all those actions, if we had held the Ctrl key and Shift key together (instead of just the Ctrl key), it would have highlighted, or selected, the regions instead of just jumping the cursor. To see how adding the Shift key will work, let's create a formula that requires that we highlight a column of numbers. Our goal with this next formula is to use the SUM function to add the Dec Sales numbers.
1. Click in cell C11.
2. Then use the Alt + = keyboard shortcut for the SUM function. (Tap the equal sign key while holding the Alt key.)
3. Select cell A12. You can do this with the keystrokes Left Arrow, Left Arrow, Down Arrow. Dancing ants will appear around the selected cell.
4. To highlight to the bottom of the column, press Ctrl + Shift + Down Arrow.
5. After you press Ctrl + Shift + Down Arrow, the screen will jump down to show you that all the numbers are highlighted, and the formula bar will show you that the SUM function is looking at the correct range of cells. The screen tip shows us that we have a block of cells that is 66R x 1C.
6. Now that we are finished making the formula, we need to put the formula in the cell and jump back up. To do this, instead of pressing the Enter key to put the formula in the cell, we press Shift + Enter.
Shift + Enter puts the formula in the cell and jumps the cursor up, one cell above cell C11 (where the formula is located). This is advantageous here because you will immediately be able to see the label and formula result.
7. After we use Shift + Enter, you should see the total 44164.
Altogether, the keyboard shortcuts for adding the Dec Sales in cell C11 were as follows:
1. Alt + = (SUM function).
(Continues...)
Excerpted from Slaying Excel Dragons by Mike Girvin, Timmy Joy. Copyright © 2011 Mike Girvin and Holy Macro! Books. 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.
Table of Contents
Contents
Chapter 1, "How Excel is Set Up",Chapter 2, "Keyboard Shortcuts",
Chapter 3, "Data in Excel",
Chapter 4, "Style Formatting and Page Setup",
Chapter 5, "Formulas and Functions",
Chapter 6, "Data Analysis Features",
Chapter 7, "Charts",
Chapter 8, "Conditional Formatting",
Chapter 9, "Find and Replace and Go To Features",
Appendix, "Excel Efficiency Rules",
Index,