What Does This Book Cover?

This book contains three parts that cover everything you need to know to use Excel 2011 effectively.

Part 1, “Becoming Proficient with Excel:Mac,” makes sure you know essential moves for using Excel:

• Chapter 1, “Learning the Secrets of the Excel:Mac Interface,” teaches you the ins and outs of the four main means of controlling Excel: the Ribbon, the toolbars, the menus, and keyboard shortcuts. You grasp how to navigate through worksheets and workbooks; learn about quick ways of entering text in workbooks; and use splitting, freezing, and custom views to display exactly the items you need.
• Chapter 2, “Configuring Excel:Mac to Suit the Way You Work,” shows you how to make Excel work you way by setting the most important preferences and by customizing the keyboard shortcuts, toolbars, menus, menu bars, and Ribbon. You also learn how to open workbooks automatically when you launch Excel and how you can save the layout of multiple open workbooks as a workspace that you can instantly restore.
• Chapter 3, “Creating Effective Workbooks and Templates,” explains how to create workbooks in which you can enter, edit, and manipulate data quickly and effectively. You learn which file formats to save the workbooks in, how to how to add property information to help you identify workbooks when searching, and how to make the most of templates—including creating templates of your own. You also learn how to organize worksheets, lay data out effectively, define named ranges to make navigation easier, and create a collapsible worksheet.
• Chapter 4, “Formatting Your Worksheets Quickly and Efficiently,” shows you how to format worksheets quickly and efficiently using the various tools that Excel provides. We start with formatting rows and columns—everything from changing column width and row height to inserting and deleting rows and columns and hiding sensitive data. Then we go through how to apply straightforward formatting, how to apply conditional formatting to quickly flag values that need attention, and how to use data validation to check for invalid entries. Finally, we cover how to save time by using table formatting or Excel's styles, and how to add headers and footers to worksheets.

Part 2, “Performing Calculations and Presenting Data,” gets you up to speed with formulas, functions, charts, and graphical elements such as pictures and sparklines:

• Chapter 5, “Performing Custom Calculations with Formulas,” makes sure you know what formulas and functions are, and what the difference between the two is. This chapter then teaches you how to create your own formulas using Excel's calculation operators, starting with straightforward formulas that use a single calculation operator each, and then moving on to more complex formulas that use multiple calculation operators. You also learn how to override Excel's default order for evaluating operators and how to troubleshoot common problems that occur with formulas.
• Chapter 6, “Using Excel's Built-In Functions,” explains how to insert functions in your worksheets using the various tools that Excel provides, find the functions you want, and point the functions to the data they need for the calculations. The second part of the chapter reviews Excel's different categories of functions, such as database functions, logical functions, and math and trigonometric functions, and gives examples of how to use widely used functions.
• Chapter 7, “Creating Clear and Persuasive Charts,” teaches you how to present data clearly and persuasively using Excel's wide range of charts. You learn the different ways you can place charts in worksheets, the components of charts, and the types of charts you can use. We then dig into how you create a chart from your data, lay it out the way you want, and then give it the look it needs. We also look at ways of reusing the custom charts you create and ways of using Excel charts in Word documents or PowerPoint presentations.
• Chapter 8, “Using Data Bars, Color Scales, Icon Sets, and Sparklines,” shows you how to add visual appeal to your worksheets by using those four types of single-cell graphical elements. You quickly get the hang of using data bars to compare the values in a range of cells, adding color scales to adjust the background colors of cells to provide a visual reference to their values, and using icon sets to provide quick visual reference to performance. And you learn to create single-cell charts using sparklines.
• Chapter 9, “Illustrating Your Worksheets with Pictures, SmartArt, and More,” explains ways of giving your workbooks visual interest by adding graphics, shapes, SmartArt diagrams, and WordArt items. You learn how to make a picture look the way you want it, how to position graphical objects wherever you need them, and how to position graphical items relative to cells and how to arrange graphical objects to control which ones are visible.

Part 3, “Analyzing Data and Sharing and Automating Your Workbooks,” shows you how to analyze, manipulate, and share the workbooks you've built:

• Chapter 10, “Creating Tables with Databases,” covers using Excel's tables to create databases for storing information, sorting it, and filtering it to find the records you need. You also learn how to put Excel's database functions to work with tables.
• Chapter 11, “Solving Business Questions with What-If Analysis, Goal Seek, and Solver,” teaches you how to analyze your data using four powerful tools. You learn to use data tables to assess the impact of one or two variables on a calculation and how to use scenarios to experiment with different sets of values without changing your core data. You also learn to use Goal Seek to solve single-variable problems and Solver to crack multi-variable problems.
• Chapter 12, “Analyzing Data with PivotTables,” explains what PivotTables are and how you can use them to examine the data in your worksheets and find the secrets it contains. You learn how to create PivotTables either using Excel's automated tool or by placing fields manually where you need them, how to change the PivotTable once you've created it, and how to sort and filter the data it contains.
• Chapter 13, “Collaborating and Sharing with Macs and Windows PCs,” takes you through ways of sharing your workbooks with others. We start by covering how to print worksheets, create PDF files from them, and export data to comma-separated values files. We then move on to sharing workbooks so that multiple people can work on them at the same time, tracking the changes if necessary so that you can review them. We finish by looking at how to merge changes from separate copies of the same workbook into one workbook and how to consolidate multiple worksheets into a single worksheet.
• Chapter 14, “Automating Tasks with Macros and VBA,” shows you how to record macros to eliminate the drudgery of performing the same task over and over again. You learn how to run macros using the menus or toolbars, using keyboard shortcuts, or even by assigning them to worksheet objects such as command buttons. I also introduce you to the Visual Basic Editor and show you how to edit a macro to change what it does.