Guerilla Data Analysis Using Microsoft Excel: Covering Excel 2010/2013 - Softcover

Du Soleil, Oz; Jelen, Bill

Softcover

ISBN 10: 1615470336 ISBN 13: 9781615470334

Verlag: Holy Macro! Books, 2015

Inhaltsangabe

This book includes step-by-step examples and case studies that teach users the many power tricks for analyzing data in Excel. These tips have been honed by Oz du Soleil, a former financial analyst charged with taking mainframe data and turning it into useful information quickly, and &;Mr. Excel&; himself, Bill Jelen. Topics include data quality, validation, perfectly sorting with one click, matching lists of data, data consolidation, data subtotals, pivot tables, pivot charts, tables, and much more. This new edition has been updated for Excel 2013.

Über diesen Titel

Über die Autorin bzw. den Autor

Oz du Soleil teaches Excel workshops and consults clients who have small businesses with no IT teams or sophisticated data management systems. He&;s worked with data and Excel for more than 10 years, including time spent as a financial analyst, facing issues of data quality, modeling business rules in Excel formulae, and revising reports. He lives in Chicago. Bill Jelen is the host of www.MrExcel.com, a Microsoft MVP, and writes the monthly Excel column for Strategic Finance Magazine. He is the author of 44 books about Excel. He lives in Akron, Ohio.

Auszug. © Genehmigter Nachdruck. Alle Rechte vorbehalten.

Guerrilla Data Analysis Using Microsoft Excel

By Oz du Soleil, Bill Jelen

Holy Macro! Books

Copyright © 2015 Holy Macro! Books
All rights reserved.

ISBN: 978-1-61547-033-4

Contents

Dedications, vii, 
About the Authors, vii, 
Acknowledgements, viii, 
Introduction: Welcome to the World of Guerrilla Data Analysis!, x, 
Reviewing the Basics, 2, 
Developing Dynamic Spreadsheets, 15, 
Conditional Formatting, 16, 
Using IF Statements, 22, 
Sorting, 24, 
Filtering, 32, 
Using Consolidate, 47, 
Using Subtotals, 51, 
Summing and Counting Using Criteria, 58, 
Using Pivot Tables, 68, 
Using Array Formulas, 92, 
Excel Tables: The Glue in Dynamic Spreadsheet Development, 99, 
The INDIRECT and OFFSET Functions, 107, 
Controlling User Inputs and Data Integrity, 112, 
Error-Handling and Formula Triggers, 117, 
Graphing and Charting, 119, 
Using Slicers, 133, 
Excel 2013: Guerrilla Data Analysis Gets Real, 136, 
Embedding Excel in Web Pages, 140, 
Down and Dirty Tips and Insights, 143, 
Useful Excel Functions, 154, 
Troubleshooting in Excel, 164, 
Spreadsheet Layout and Development, 168, 
Using Keyboard Shortcuts, 173, 
Wrap-Up, 177, 
Index, 179, 

CHAPTER 1

Reviewing the Basics

This book assumes that you have some basic knowledge of Excel. However, even seasoned Excel veterans miss some of the basics, so let's start with some fundamentals that will serve you well when you're working with data and things get hot.

Overview of Excel Formulas and Functions

There is a difference between a formula and a function. Formulas start with = and do not always use a function.

=3+2 is a formula without a function.

=B3+E3 is a formula without a function, and it adds the values in cells B3 and E3.

SUM is a function.

=SUM(3,2,11) is a formula with the SUM function, and would add 3+2+11.

=B1*MAX(A3:C20) is a formula that uses MAX to find the maximum value in the range A3:C20 and multiplies that result by the value in cell B1.

Therefore, functions are those features in Excel that are named like MAX, COUNTA, SUMIFS, NOW, TAN, KURT, CHAR, etc, and they are programmed to perform specific tasks.

There are hundreds of Excel functions, grouped in 11 categories. No one has reason to memorize every function. Instead, getting the most from Excel requires using resources like online forums, tutorials, books, and just asking people if they can help answer questions.

The following tables show some of the ways that Excel communicates with its users via formula notation and error messages.

Formula Notations

Here is a list of some of the notation that you'll see in formulas and what they mean.

Excel Error Notations

Here is a list of some of the errors that you'll see in Excel and what they mean.

Changing Formulas to Values

Here's a must-know: You should always get rid of formulas if they've done their job and are no longer needed.

Say that you've received a report that has the first and last names in separate cells and you need the full names in one cell.

To get the names put together, you can use the & symbol to concatenate them:

[ILLUSTRATION OMITTED]

In this figure, notice the formula =A2&" "&B2. You have to include the space between the first and last names. (To see for yourself, set up a similar spreadsheet and use the formula =A2&B2 instead.)

Once you have the names in one cell, you're done with columns A and B. But can you just delete them? Nope! If you delete the columns, you get a #REF! error because the formulas are looking for data that no longer exists:

[ILLUSTRATION OMITTED]

You need to undo the deletion with Ctrl+Z and get your data back.

To do this right, you have to get rid of the formulas in column C before you can delete the data in columns A and B. There are several ways to do this. Here's one:

1. Highlight the range that contains underlying formulas and right-click.

2. In the context menu that pops up, select Copy and then select Paste As Values, which is designated by the clipboard icon with the 123, as shown in the image below.

Now the formulas are gone. You have actual data in the Full Name column, and you're free to delete the First Name and Last Name columns.

If your keyboard has an Application key (which looks like a mouse cursor pointing to a dropdown menu and is often between the right Alt and Ctrl keys), you can convert to values by pressing Ctrl+C and then pressing and releasing the Application key and then pressing the V key.

You don't have an Application key?

Here's yet another way to paste as values after you've copied the original data:

1. On the Home tab, select the arrow at the bottom of the Clipboard group.

2. In the menu that appears, select Paste Special to open the Paste Special dialog. Notice in the figure below that Values is selected in the Paste section.

3. Click OK to get rid of the formulas in the Full Name column, leaving only the data.

Using Paste Special in Other Ways

The Paste Special options in Excel are worth getting to know. You've seen how to use Paste Special to change formulas to values. The following sections cover two more features: Transpose and, Multiply.

Transposing Columns and Rows

For one thing, you can use Paste Special to transpose columns and rows. Say that you have city names as column headers, as shown in the next figure, but you'd rather work with them as row headers.

Here's what you do:

1. Highlight the range and press Ctrl+C (or right-click and select Copy) to copy it.

2. Select the cell where you want the vertical data to start. The following figure shows the cursor in cell A4.

3. On the Home tab, select the arrow at the bottom of the Paste icon. From the menu that appears, select Paste Special to open the Paste Special dialog.

4. Select the Transpose check box, as shown in the following figure. Click OK.

Excel makes the cities into row headers instead of column headers — and it transposes the corresponding data into the right place, too.

Performing a Calculation on Every Cell in a Range

Say that you have a range of 48 cells that represent sales data, and you need to calculate a revenue share amount of 14% on each of the 48 cells. You can use Paste Special for this, too, and here's how you do it:

1. Enter 0.14 in an empty cell (G2 in the image below).

2. Press Ctrl+C (or right-click the cell and select Copy) to copy it.

3. Highlight the range of 48 cells of sales data, right-click, and select Paste Special.

4. In the Paste Special dialog, select the Multiply radio button and then click OK.

5. Excel did the multiplication for you but it's also changed the formatting to General.

With the cells still highlighted, Select Home | Accounting | click the dollar-sign to convert the General format to Accounting.

Using Helper Columns

Using helper columns isn't actually an Excel feature, but it's a really great strategy for dealing with complex formulas. When you need multiple calculations in a formula, you can break the actions into several steps or multiple formulas rather than try to write a massive formula all at once.

Say that you have a list of employees, along with the pay rate for each, the number of hours each employee has worked, and the number of overtime hours worked. The rule is that employees get time-and-a-half...

Details zur Ausgabe

• Verlag: Holy Macro! Books
• Erscheinungsdatum: 2015
• Sprache: Englisch
• ISBN 10: 1615470336
• ISBN 13: 9781615470334
• Einband: Taschenbuch
• Auflage: 2
• Anzahl der Seiten: 181
• Kontakt zum Hersteller: Nicht verfügbar
• Verantwortliche Person: preigu; mail@preigu.de; ; Lengericher Landstr. 19; Osnabrück; 49078; Deutschland