[Head First Series 01] • Head First Excel by Milton, Michael -- Read -- Imperial Library of Trantor

Index

Head First Excel A Note Regarding Supplemental Files Advance Praise for Head First Excel Praise for other Head First books Author of Head First Excel How to Use This Book: Intro

Who is this book for?

Who should probably back away from this book?

We know what you’re thinking We know what your brain is thinking Metacognition: thinking about thinking Here’s what WE did Here’s what YOU can do to bend your brain into submission Read Me The technical review team Acknowledgments O’Reilly Safari

1. Introduction to Formulas: Excel’s real power

Can you live it up on the last night of your vacation? Here’s what you budgeted and what you spent Excel is great for keeping records... Formulas work with your data Looks like Bob forgot a receipt... Your friends sent you all the receipts References keep your formulas working even if your data changes Check your formulas carefully Refer to a bunch of cells using a range Use SUM to add the elements in a range Bob and Sasha wonder whether we’ve been taking the right approach... Your friends agree: split the checks individually When you copy and paste a formula, the references shift Excel formulas let you drill deep into your data Everyone has plenty of cash left for a food-filled night in New York City!

2. Visual Design: Spreadsheets as art

CRMFreak needs to present their financials to analysts The dollar sign is part of your cell’s formatting How to format your data The boss approves! Design principle: keep it simple Clash of the design titans... Use fonts to draw the eye to what is most important Cell styles keep formatting consistent for elements that repeat With your cell styles selected, use Themes to change your look He likes it, but there’s something else... Use proximity and alignment to group like things together Your spreadsheet is a hit!

3. References: Point in the right direction

Your computer business is in disarray Your production manager has a spreadsheet with costs MIN returns the lowest number in a series Let Excel fill in ranges by starting your formula and using your mouse Excel got the right answer using a more sophisticated reference Things just got even better... Use absolute references to prevent shifting on copy/paste Your profit margin is now even higher... Absolute references give you a lot of options Named ranges simplify your formulas With all this data, you’d have to write a ton of formulas Excel’s Tables make your references quick and easy Structured references are a different dimension of absolute reference Your profitability forecasts proved accurate

4. Change your Point of View: Sort, zoom, and filter

Political consultants need help decoding their fundraising database Find the names of the big contributors Sort changes the order of rows in your data Sorting shows you different perspectives on a large data set See a lot more of your data with Zoom Your client is impressed! Filters hide data you don’t want to see Use Filter drop boxes to tell Excel how to filter your data An unexpected note from the Main Campaign... The Main Campaign is delighted with your work Donations are pouring in!

5. Data Types: Make Excel value your values

Your doctor friend is on a deadline and has broken data Somehow your average formula divided by zero Data in Excel can be text or numbers The doctor has had this problem before You need a function that tells Excel to treat your text as a value A grad student also ran some stats...and there’s a problem Errors are a special data type Now you’re a published scientist

6. Dates and Times: Stay on time

Do you have time to amp up your training for the Massachusetts Marathon? VALUE() returns a number on dates stored as text Excel sees dates as integers Subtracting one date from another tells you the number of days between the two dates When subtracting dates, watch your formatting Looks like you don’t have time to complete training before a 10K Coach has a better idea DATEDIF() will calculate time between dates using a variety of measures Coach is happy to have you in her class Excel represents time as decimal numbers from 0 to 1 Coach has an Excel challenge for you You qualified for the Massachusetts Marathon

7. Finding Functions: Mine Excel’s features on your own

Should you rent additional parking? You need a plan to find more functions Excel’s help screens are loaded with tips and tricks Here’s the convention center’s ticket database for the next month Anatomy of a function reference The Dataville Convention Center COO checks in... Functions are organized by data type and discipline Your spreadsheet shows ticket counts summarized for each date Box tickets for you!

8. Formula Auditing: Visualize your formulas

Should you buy a house or rent? Use Net Present Value to discount future costs to today’s values The broker has a spreadsheet for you Models in Excel can get complicated

Rent model Mortgage model

Formula auditing shows you the location of your formula’s arguments Excel’s loan functions all use the same basic elements The PMT formula in the broker’s spreadsheet calculates your monthly payment Formulas must be correct, and assumptions must be reasonable The broker weighs in... Your house was a good investment!

9. Charts: Graph your data

Head First Investments needs charts for its investment report Create charts using the Insert tab Use the Design and Layout tabs to rework your chart Your pie chart isn’t going over well with the corporate graphic artist You’re starting to get tight on time... Your report was a big success...

10. What if Analysis: Alternate realities

Should your friend Betty advertise? Betty has projections of best and worst cases for different ad configurations You need to evaluate all her scenarios Scenarios helps you keep track of different inputs to the same model Scenarios saves different configurations of the elements that change Betty wants to know her breakeven Goal Seek optimizes a value by trying a bunch of different candidate values Betty needs you to add complexity to the model

She needs you to do two things Goal Seek cannot do

Solver can handle much more complex optimization problems Do a sanity check on your Solver model Solver calculated your projections Betty’s best-case scenario came to pass...

11. Text Functions: Letters as data

Your database of analytic customers just crashed! Here’s the data Text to Columns uses a delimiter to split up your data Text to Columns doesn’t work in all cases Excel has a suite of functions for dealing with text LEFT and RIGHT are basic text extraction functions You need to vary the values that go into the second argument Business is starting to suffer for lack of customer data This spreadsheet is starting to get large! FIND returns a number specifying the position of text Text to Columns sees your formulas, not their results Paste Special lets you paste with options Looks like time’s running out... Your data crisis is solved!

12. Pivot Tables: Hardcore grouping

Head First Automotive Weekly needs an analysis for their annual car review issue You’ve been asked to do a lot of repetitive operations Pivot tables are an incredibly powerful tool for summarizing data Pivot table construction is all about previsualizing where your fields should go The pivot table summarized your data way faster than formulas would have Your editor is impressed! You’re ready to finish the magazine’s data tables Your pivot tables are a big hit!

13. Booleans: TRUE and FALSE

Are fishermen behaving on Lake Dataville? You have data on catch amounts for each boat Boolean expressions return a result of TRUE or FALSE IF gives results based on a Boolean condition Your IF formulas need to accommodate the complete naming scheme Summarize how many boats fall into each category COUNTIFS is like COUNTIF, only way more powerful When working with complex conditions, break your formula apart into columns Justice for fishies!

14. Segmentation: Slice and dice

You are with a watchdog that needs to tally budget money Here’s the graph they want Here’s the federal spending data, broken out by county Sometimes the data you get isn’t enough Your problems with region are bigger Here’s a lookup key VLOOKUP will cross-reference the two data sources Create segments to feed the right data into your analysis Geopolitical Grunts would like a little more nuance You’ve enabled Geopolitical Grunts to follow the money trail... Leaving town... It’s been great having you here in Dataville!

A. Leftovers: The Top Ten Things (we didn’t cover)

#1: Data analysis #2: The format painter #3: The Data Analysis ToolPak #4: Array formulas #5: Shapes and SmartArt #6: Controlling recalculation and performance tuning #7: Connecting to the Web #8: Working with external data sources #9: Collaboration #10: Visual Basic for Applications

B. Install Excel’s Solver: The Solver

Install Solver in Excel

Index About the Author Copyright

← Prev
Back
Next →

← Prev
Back
Next →