Your Excel Survival Kit: Your Guide to Surviving and Thriving in an Excel world
160
Your Excel Survival Kit: Your Guide to Surviving and Thriving in an Excel world
160eBook
Available on Compatible NOOK devices, the free NOOK App and in My Digital Library.
Related collections and offers
Overview
Product Details
| ISBN-13: | 9781615473526 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 06/01/2016 |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 160 |
| File size: | 16 MB |
| Note: | This product may take a few minutes to download. |
About the Author
Read an Excerpt
Your Excel Survival Kit
A Guide to Surviving and Thriving in an Excel World
By Anne Walsh
Holy Macro! Books
Copyright © 2016 Tickling Keys, Inc.All rights reserved.
ISBN: 978-1-61547-352-6
CHAPTER 1
Back to Basics: What Do You Know Already?
Before you really get going on any journey, it's a good idea to step back and check what you know already and what tools you already have. This chapter is essentially a refresher chapter. It reminds you about (or reacquaints you with) the basics — the stuff you really need — so you can get your bearings as you head out into the Excel wilderness.
The goal of this chapter is to remind you of what you know already and to fill in any gaps in your basic Excel knowledge. It begins with getting text and numbers into Excel and then moves on to basic functions and some other worksheet basics. If you think you have forgotten any of this material, now is the time to dust it down out of the Excel attic.
This is meant to be a hands-on book, so to play along, open up a blank Excel workbook and get ready to walk through all the step-by-step procedures presented in this chapter.
Note This book shows Excel 2010 in use, but what this chapter covers is true for all versions of Excel (even if you are rocking an Excel 2003 version).
Data Entry Tips and Tricks
Before you read any further, here are some tips that will make your Excel life a lot easier:
• If you plan to do any calculations with a number, do not put any text in the cell with it. When you mix text with numbers and then try to do calculations, Excel gives you a #VALUE! message.
• Try to keep your entries separate. For example, if you are entering names and addresses, and you think that in the future you may want to sort the names by surname, put first name in one cell and surname in the cell beside it.
• If you need to enter phone numbers or any other numbers that have leading zeros (e.g., 00353111155555), type in an apostrophe (') before entering the number. This is how you tell Excel to treat it as text so that it keeps the first two zeros. If you don't do this, Excel keeps removing the first two zeros so you get 353111155555. And like a dog playing fetch, you will spend the rest of the day trying to add those two zeros at the beginning.
Note Have I mentioned that I'm Irish? My spelling and some of my examples may be a bit foreign to those of you from the United States and other places. I'm told that U.S. phone numbers never start with two zeros but that a zip code could, in fact, start this way. You may also find other situations where you need to preserve leading zeros.
• Place entries side by side and underneath each other. If you don't, you create a lot of extra work for yourself. Why? Later on, if all your entries are together and you need to sort or filter your list, Excel will naturally sort and filter all items in the list.
• Before you can format numbers or text, you need to highlight them. The quickest way to do that is to click on the first cell. Then, keeping your left mouse button pressed down, drag the selection down until you have highlighted the cells you want to apply your formatting to.
Creating a Text Series
1. Click in cell B2 and type January. Note that the cursor is flashing at the end of the word.
2. Rest your mouse pointer in the bottom-right corner of the cell, on the fill handle (see Figure 1-1). (I think of it as a soap opera character named Phil Handle.) It should change to a black cross, but if it doesn't do so, hover the mouse pointer over it until it does.
3. Holding down the left mouse button, drag the cell down to the cells underneath. You can stop at December, but if you continue, January will reappear.
4. Release the mouse. Holy mackerel! It's a miracle! Excel has filled in the other months. Yep, that's how we do it in the Excel world.
Note You can do the same thing with the days of the week: Type Monday in another cell, grab the fill handle, and drag down. Excel fills in the days of the week for you! Sigh, I love Excel.
You can also create your own lists other than the months of the year and days of the week by using the Custom Lists option: Just select File | Options | Advanced | Edit Custom Lists and specify the type of list you want to create.
Creating a Numeric Series
1. Click in cell D2 and type 1.
2. Click in cell D3 and type 2.
3. Highlight both cell D2 and cell D3 by clicking in the centre of D2. Your mouse pointer changes to a white cross.
4. Drag down the mouse until you see a black line around D2 and D3.
5. Rest your mouse pointer on the bottom-right corner of the cell. (Remember Phil Handle?) It should change to a black cross, but if it doesn't do so, hover the mouse pointer over it until it does.
6. Holding down the left mouse button, drag down to cell D11. Holy-moly! Excel does the same neat trick with numbers that it does with words. This time, it fills in the numbers 1–10 for you.
Note Practise with other number sequences, such as 10, 20, etc. and 2, 4, etc. Remember that you have to highlight both numbers, or Excel doesn't know you want a sequence of numbers and just copies the first number, such as 10 or 2, all the way down.
Highlighting More Than One Group of Cells at a Time
1. Highlight the first group of cells you want highlighted (e.g., A2:A4).
2. Press the Ctrl key and keep it pressed down while highlighting the second block of cells (e.g., C2:C4).
3. Release the mouse and the Ctrl key. You now have two ranges highlighted, and when you apply formatting, it applies to both ranges.
Note I refer to tabs frequently in this book. By this I mean the set of icons at the top of the screen. For example, File, Home, and Insert are all tabs on the Excel ribbon.
Formatting Text Entries
1. Highlight the text to which you want to apply formatting.
2. On the Home tab of the ribbon, click on the font formatting you want to apply: B (Bold), I (Italic), U (Underline), etc. (see Figure 1-2). Excel immediately applies the selected formatting to the highlighted text.
Note It is worthwhile at this point to have a look at some of the formatting options on the Home tab. Note that when you rest your mouse on each icon, Excel tells you what that icon does. See, Excel is on your side.
Formatting Number Entries
1. Enter some numbers into some cells.
2. Highlight those cells.
3. On the Home tab of the ribbon, click on the numeric formatting you want to apply: Currency, Percent Style, Comma Style, etc. (see Figure 1-3).
Copying and Pasting
Note There are multiple ways to copy and paste: You can use keyboard shortcuts, right-click with the mouse and use the menu that pops up, or use the Home tab of the ribbon. I show only the ribbon method here, but if you prefer one of the other methods, feel free to continue using it.
1. Highlight the data you want to copy and paste.
2. Click Home | Copy.
3. Click where you want the data pasted.
4. Click Home | Paste (see Figure 1-4).
Survival Tip If you need to copy the same data somewhere else right away, you can just click in the next location and click Paste again.
Copying Formatting with the Format Painter
1. Highlight the text or numbers that already have the desired formatting.
2. Click Home | Format Painter (see Figure 1-5). A small brush appears on your mouse pointer.
3. Highlight the text or numbers you want to apply the formatting to. Excel applies the same formatting to these cells.
Putting the Same Text in Many Cells at the Same Time
1. Click on the first cell you want to add text to.
2. Press the Ctrl key and, keeping it pressed, click on all the cells you want this entry to appear in.
3. Release the Ctrl key. The last cell you clicked is now white.
4. Type the text you want to enter in all the selected cells. (Don't click again, though, or your selection work will be undone.)
5. When you have finished typing the text for all the selected cells, press Ctrl+Enter. Presto! The text appears in all the selected cells.
Dropping Your Dread of Formulas
When I ask people in class what they want to learn, the answer is invariably "formulas," which is rather like answering the question "What books do you like?" with "reading." We'll get further into the nitty-gritty of formulas later in this book, but in this first chapter, we'll keep it pretty basic. The following subsections show the basic steps involved in using the basic operators (+, -, /, *, ()) and some essential functions: Sum(), Max(), Min(), Average(), Count(), and CountA(). You will also see how to fix a cell so that it doesn't move when you copy it and revisit the basics of using worksheets, such as copying, deleting, colouring, etc.
Entering a Formula with the + (Plus) Operator
1. In cell A2, type 100.
2. In cell B2, type 300.
3. In cell C2, which is where you want the answer to appear, type =.
Survival Tip Make sure you have clicked where you want the answer to go. I say this because many times I've seen people with the right answer in the wrong cell because that is where they had placed the cursor when they began the formula. It's sort of "right lover, wrong place".
4. Click on cell A2 and then type +. Even though you click on the number 100, A2 appears in your formula.
5. Click in cell B2 and press Enter. Even though you click on the number 300, B2 appears in your formula. You now see the answer, 400, in cell C2.
The beauty of doing it this way is that if you decide to change one of your entries, Excel updates the values in the formula for you. For example, if you change the entry in A2 from 100 to 500, Excel changes the answer in C2 to 800.
Copying a Formula Down a Column
1. With the formula you just entered still in place in the workbook, in cell A3, type 50.
2. In cell B3, type 100.
3. Click in C2 and drag the Fill Handle down to copy down the formula. In cell C3, you should now see 150.
Note In this case, you have copied down a formula to only 1 row, but the same process applies if you have to copy it down for 100 rows or indeed 100,000 rows. Enter the formula once and then copy it down.
Entering a Formula with the – (Minus) Operator
1. In cell F2, type 500.
2. In cell G2, type 100.
3. In cell F3, type 1000.
4. In cell G3, type 250.
5. In cell H2, type = and then click on cell F2.
6. Type a minus sign: –.
7. Click in cell G2 and press Enter. You should now see the value 400 in this cell.
8. Copy down this formula, and you should see the value 750 in cell G3.
Entering a Formula with the / (Division) Operator
1. In cell K2, type 1500.
2. In cell L2, type 100.
3. In cell K3, type 1000.
4. In cell L3, type 250.
5. In cell M2, type =.
6. Click on K2, and then type /.
7. Click in cell L2 and press Enter. You should now see the value 15 in cell M2.
8. Copy down this formula, and you should see the value 4 in cell M3.
Entering a Formula with the Multiplication Operator *
1. In cell P2, type 500.
2. In cell Q2, type 10.
3. In cell P3, type 1000.
4. In cell Q3, type 4.
5. In cell R2, type =.
6. Click on cell P2, and then type *.
7. Click in cell Q2 and press Enter. You should now see the value 5000 in cell Q2.
8. Copy down this formula, and you should see the value 4000 in cell R3.
Using Brackets in Formulas
You use brackets in formulas when you need Excel to do a particular calculation before it does other calculations. For example, to calculate the total wages eligible for tax, you need to add basic wages plus overtime first — and you can do that by using brackets.
To see how this works, open the file 01_Brackets. In this file you want to add salary and overtime together and then find 20% of this total. In cell E4, the formula=B4+C4*D4 has been entered and copied down. You don't have to be an accountant or a tax collector to know that 20% of 8817 plus 215 is not 8860. Of course, if you are a tax collector, you might prefer this number.
Note Throughout this book, I many times ask you to open a particular file to play along with the text. You can find these files athttp://www.mrexcel.com/survivalfiles.html. Simply download the files and store them someplace you can easily access.
So what has happened here? Excel has found 20% of 214, which is 43, and added it to 8817 ... and that's how you ended up with 8860.
Note Excel does calculations in a very particular way. Before it does anything else, Excel first looks for brackets and does the calculations within those brackets. Then it uses a particular operator precedence: It does multiplication and division before it does addition and subtraction. This is what has happened here. Excel did the multiplication first and then the addition. If you search Excel help for "operator precedence," you will find a more comprehensive explanation of this concept.
But what you want Excel to do is add salary and overtime together first and then find 20% of that. You do that by using the formula =(B4+C4)*D4 and copying it down. (This formula has already been entered into cell F4 in 01_Brackets.) Now you get an accurate answer: 1806.4.
So remember that if you want to force Excel to do a specific calculation first, put brackets around it.
Getting to Know the Common Excel Functions
A function is essentially a predesigned formula in Excel. There are functions to handle most of the mathematical operations you might want to do. The following sections cover some of the most commonly used functions in Excel: Sum(), Max(), Min(), Average(), Count(), & CountA().
This is not by any means a comprehensive list of Excel functions. However, these are the ones that you will probably use most often. And part of the beauty of these particular functions is that they all operate in a very similar way.
Adding Up with Sum(): This Is Sum-Thing Good
(Sorry, I Do Love a Good Pun)
1. Open a blank Excel workbook.
2. In cell B2, type 500.
3. In cell B3, type 300.
4. In cell B4, type 250.
5. Click in cell B5 and click Home | AutoSum (see Figure 1-6). You now see the formula =Sum(B2:B4) in B5. You should also now see "marching ants" going around the range B2:B4. This means Excel has included all the cells from B2 to B4, inclusive.
Note In Excel, a range is any group of cells, and it's indicated with a colon between two cells (e.g., B2:B4 for the range of cells from B2 to B4).
6. Press Enter, and you should now see the number 1050. You've just used your first function!
Copying a Function
All the stuff you have learned already about copying text and numeric data also applies to copying functions.
1. Using the same Excel workbook you just used to try out the Sum() function, in cell C2, type 250.
2. In cell C3, type 300.
3. In cell C4, type 5000.
4. Copy across the Sum() function from cell B5 to cell C5. You should now see the number 5550 in cell C5.
5. Save this file at this point and name it Excel_Practice_1.
Finding the Highest Value with Max()
1. Open the file 01_Functions and in it open the Without Formulas sheet.
2. In cell D6, type =Max. As you start typing, functions appear. After you type M, you get a long list, then after you type MA, the list shortens to the list shown in Figure 1-7.
3. In the list of functions that appears, double-click Max. =Max( now appears in cell D6.
4. Highlight the range D2:D4 (and note that the marching ants appear).
5. Type ) and press Enter. You now see the highest value in cell D6: 500. At this point, be careful not to include the total figure in cell D6. If you now copy the formula across to E6, you now see 5000 in that cell.
Finding the Lowest Value in a List with Min()
1. Open the file 01_Functions.
2. In cell D7, type =Min.
3. In the list of functions that appears, double-click Min. =Min( now appears in cell D7.
4. Highlight the range D2:D4 (and note that the marching ants appear).
5. Type ) and press Enter. You now see the lowest value in cell D7: 250. If you now copy the formula across to E7, you should see 250 in that cell as well.
Finding the Mean with Average()
1. Open the file 01_Functions.
2. In cell D8, type =Aver.
3. In the list of functions that appears, double-click Average. =Average( now appears in cell D8.
4. Highlight the range D2:D4 (and note that the marching ants appear).
5. Type ) and press Enter. You now see the average value of these numbers in cell D8: 350. If you now copy the formula across to E8, you should see 1850 in that cell.
Finding a Count of Numbers with Count()
1. Open the file 01_Functions.
2. In cell D9, type =Coun.
3. In the list of functions that appears, double-click Count. =Count( now appears in cell D8.
4. Highlight the range D2:D4 (and note that the marching ants appear).
5. Type) and press Enter. This function tells you how many numbers you have in the list, and you get the answer 3 in cell D9. When you copy it across to E9, you see 3 in that cell as well because there are three numbers in this list.
Counting Something Other Than Numbers with CountA()
Count() is a very pure, unsullied function in the sense that it will only count numbers. But what if you want to count how many names you have in a list, such as Mary, John, and Paul? As you see in the Without Formulas sheet in the 01_Functions file, the Count() function does not give you the desired answer. If you try to use Count() in cell G1 with the range D1:F1, Excel gives you 0 as the answer, not 3, because Mary, John, and Paul are not numbers.
(Continues...)
Excerpted from Your Excel Survival Kit by Anne Walsh. Copyright © 2016 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.
Table of Contents
Contents
About the Author,Dedication,
Acknowledgements,
Introduction,
Chapter 1 - Back to Basics: What Do You Know Already?,
Chapter 2 Getting Your Data Together: Catching Your File,
Chapter 3 Further Cleaning, Slicing, and Dicing,
Chapter 4 The Vlookup() Function: An Excel Essential,
Chapter 5 Creating Pivot Tables,
Chapter 6 Using Power Query to Quickly Clean Up Data,
Chapter 7 Beyond the Pivot Table: Power Pivot,
Appendix Data Validation Techniques,
Index,