Your Excel Survival Kit

A Guide to Surviving and Thriving in an Excel World

Anne Walsh

• Publisher: Holy Macro! Books
• Year: 2016
• Print ISBN:
  9781615470457
• Edition: 1
• eBook ISBN:
  9781615473526
• Topic: Computer Science
• Subtopic: Business Skills
• Index: Computer Science

Master Excel and Thrive in Today's Data-Driven WorldAre you ready to take control of Excel and transform your data analysis skills? Your Excel Survival Kit is your comprehensive guide to navigating the complexities of Excel, designed to help you not just survive, but thrive. Whether you're struggling with vlookups, pivot tables, or the powerful Power Query, this book provides clear, accessible instructions to empower you.

Inside, you'll discover how to:

• Clean and organize your data for maximum efficiency
• Master essential Excel functions and formulas
• Create dynamic pivot tables to summarize and analyze data
• Automate tasks with Power Query to save time and reduce errors
• Present your findings with compelling charts and visualizations

Transform from an Excel novice to a confident user, capable of tackling any data challenge. This book is for Excel users, accountants, business professionals, and data analysts. Anne Walsh, a Microsoft Certified Trainer, provides the tools and techniques to make Excel your ally, not your adversary. Visit www.the-excel-expert.com for more resources.

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. No...

Table of contents

• About the Author
• Dedication
• Acknowledgements
• Introduction
• Who This Book Is For
• What This Book Covers
• Typographical Conventions and Special Elements
• Chapter 1 - Back to Basics: What Do You Know Already?
• Data Entry Tips and Tricks
• Dropping Your Dread of Formulas
• Getting to Know the Common Excel Functions
• Understanding the Copying Functions
• Rolling in the Worksheets
• Printing in Excel
• Charts, Charts, Charts
• Keyboard Navigation
• Summary
• Chapter 2 Getting Your Data Together: Catching Your File
• Creating a Spreadsheet List from Scratch
• Getting Data from a Data Dump
• Sorting and Filtering
• Summary
• Chapter 3 Further Cleaning, Slicing, and Dicing
• Removing/Completing Blank Columns/Rows/Cells
• Cleaning, Combining, and Amending Existing Data
• Extracting Specific Pieces of Data from a Cell to Refine a Data Set
• Using the Iferror() Function
• Summary
• Chapter 4 The Vlookup() Function: An Excel Essential
• Understanding the Vlookup() Function Syntax
• Troubleshooting Vlookup(): Dealing with #N/A Errors
• Understanding When to Use Vlookup()
• How to Solve Common Vlookup() Problems
• Summary
• Chapter 5 Creating Pivot Tables
• Assembling Data for a Pivot Table
• Creating Your First Pivot Table
• Understanding Summarize Values By
• Tidying Up the Numbers in a Pivot Table
• Refreshing Data in a Pivot Table
• Grouping by Dates
• Grouping by Amounts
• Showing Values As
• Using Report Filters
• Setting Up Slicers (but Only if You Have Excel 2010 or Later)
• Changing Pivot Table Layout
• Adding a Chart
• Adding Conditional Formatting
• Troubleshooting Pivot Tables
• Summary
• Chapter 6 Using Power Query to Quickly Clean Up Data
• Cleaning Up an Accounting Data Dump
• Converting Unpivoted Data to a Pivoted Format
• Creating a Query in Power Query to Merge Data Sets
• Summary
• Chapter 7 Beyond the Pivot Table: Power Pivot
• Installing and Locating Power Pivot
• Before You Start Using Power Pivot
• Getting Your Data into Power Pivot
• Linking the Data Sets Together
• Creating a Pivot Table from the Combined Data
• Recommended Resources
• Summary
• Appendix Data Validation Techniques
• Restricting Number Sizes
• Restricting Date Entries
• Restricting Text Length
• Extending Data Validation
• Applying Data Validation to Another Part of a Sheet
• Identifying Duplicate Entries in a List
• Simple Normalization (Getting Crossways Data to Go Lengthwise or Vice Versa)
• Unpivoting Data
• Index