Basic Microsoft Excel 2010
Yashwantrao Chavan Academy of Development Administration
Course Material for
(For free internal distribution only. Not for sale or resale)
Rajbhavan Complex, Baner Road, PUNE - 411 007, Maharashtra State, India
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Guidelines and tips
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Telling Excel to Flag Result Cells with an Error . ... Applying Fonts, Sizes, Styles, and Text Color . ... In Figure 2-8, the formula in cell E12,.
Typology: Exams
2022/2023
1 / 313
This page cannot be seen from the preview
Don't miss anything!
Related documents
Partial preview of the text
Download Basic Microsoft Excel 2010 and more Exams Printing in PDF only on Docsity!
Basic Microsoft Excel 2010
Yashwantrao Chavan Academy of Development Administration
Course Material for
(For free internal distribution only. Not for sale or resale)
Rajbhavan Complex, Baner Road, PUNE - 411 007, Maharashtra State, India
___________________________________________________________________________
Introduction
- Exploring Excel - Identifying Screen Elements - Zeroing in on the Ribbon - Introducing the Backstage View. - Moving Around the Excel Screen - Using the Keyboard. - Using a Mouse - Using the Ribbon - Entering Excel Data. - Entering Labels. - Entering Values - Entering Dates - Using AutoFill - Selecting Cells on a Spreadsheet - Using the Mouse - Using a Keyboard. - Editing a Worksheet - Using Undo and Redo - Inserting and Deleting Cells - PART
- THE BASICS
- Chapter
- Creating a Basic Excel Worksheet
- Moving and Copying Data ___________________________________________________________________________
- Dragging and Dropping Data
- Using Cut, Copy, and Paste
- Working with Range Names
- Naming a Range of Cells
- Moving and Copying Data ___________________________________________________________________________
- Chapter
- Working with Formulas
- Creating Formulas
- Using Mathematical Operators
- Creating a Simple Formula
- Using Range Names in Formulas
- Creating a Compound Formula
- Considering the Order of Operations
- Editing Formulas
- Controlling Recalculation
- Copying Formulas
- Copying with AutoFill
- Copying with Copy and Paste
- Copying Values Instead of Formulas.
- Creating an Absolute or Mixed Formula Reference.
- Creating an Absolute Formula Reference
- Creating a Mixed Formula Reference
- Creating Formulas
- Chapter
- Using Excel Functions.
- Considering Form versus Function
- Understanding Function Syntax
- Creating a Total with the SUM Function
- Entering a SUM Function Manually.
- Entering a SUM Function with the Function Wizard
- Entering a SUM Function with the AutoSum Button
- Calculating Results Without Entering a Formula ___________________________________________________________________________
- Nesting Functions
- Using Other Functions
- Using the Financial Functions
- Using Logical Functions
- Using Text Functions
- Using Date and Time Functions
- Using Lookup and Reference Functions
- Using Mathematical Functions
- Using Other Functions
- Displaying Formulas in a Worksheet
- Understanding How Excel Handles Formula Errors
- Understanding Formula Error Messages
- Avoiding Common Formula Errors
- Telling Excel Which Errors to Flag.
- Just What Is a Calculated Column?
- Telling Excel to Flag Result Cells with an Error
- Checking for Errors Automatically One at a Time
- Using the Watch Window
- Evaluating a Formula
- Identifying Formula Precedents and Dependents
- Tracing an Error
- Controlling Whether Errors Print
- Adjusting Columns and Rows.
- Changing Column Width.
- Changing Row Height.
- Inserting and Deleting Columns and Rows.
- Chapter
- Correc ting Formula Errors
- Chapter
- Making your Worksheet Look Good - Formatting Cell Content ___________________________________________________________________________ - Applying Fonts, Sizes, Styles, and Text Color - Adding Borders and Shading - Working with Alignment and Spacing - Applying Numeric Formatting - Applying Conditional Formatting - Creating Cell Rules - Using Data Bars, Color Scales, and Icon Sets - Applying Office Themes and Layouts - Working with Multiple Worksheets - Moving Between Worksheets - Inserting Additional Worksheets - Deleting Worksheets - Renaming Worksheets - Copying Worksheets. - Moving Worksheets - Changing Worksheet Tab Colors - Displaying Data Stored Elsewhere in the Workbook - Displaying Data Stored in Another Workbook - Linking to Elsewhere in the Same or Different Workbook - Using Find and Replace - Searching for Data - Replacing Cell Data. - Managing Worksheet Views - Zooming In and Out - Changing Worksheet Views
- PART
- HANDLING LARGER WORKBOOKS
- Chapter
- Managing Large Data
- Freezing Row and Column Headings ___________________________________________________________________________
- Splitting the Excel Screen
- Hiding Rows and Columns
- Chapter
- Sorting Data.
- Sorting from the Ribbon
- Working with the Sort Command
- Creating a Subtotal Report
- Expanding and Collapsing Your Subtotal Report
- Creating Manual Groups from Sorted Rows
- Chapter
- Filtering Data.
- Chapter
- Preparing to Print
- Setting the Print Area
- Previewing Your Worksheet
- Changing Orientation
- Creating an AutoFilter
- Applying the Filters
- Copying Filtered Data
- Performing a Secondary Filter Selection
- Exploring Special Filters
- Searching for Blank Cells
- Filtering by Values
- Filtering by Date
- Filtering by Color.
- Creating Custom Filters.
- Filtering Text
- Adding Multiple Comparison Criteria
- Using Formulas with Filtered Data
- Choosing a Paper Size ___________________________________________________________________________
- Making It Fit
- Adjusting Page Layout
- Switching Worksheet Views
- Working with Margins
- Manually Changing Page Breaks
- Creating Backgrounds
- Printing Gridlines and Headings
- Including Titles
- Adding Headers and Footers
- Inserting a Watermark
- Chapter
- Printing and Other Output Formats.
- Printing Your Workbooks
- Handling Special Printing Needs
- Selecting Multiple Worksheets to Print.
- Changing Orientation in the Same Print Job
- Printing Multiple Pages on a Single Sheet
- Making Comments Visible
- Showing Off Your Formulas
- Printing Named Ranges
- Printing Charts
- Printing a Draft
- Changing Printers
- Printing Without Opening Excel
- Choosing an Alternative to Printing
- Creating a PDF
- E-Mailing Your Worksheet.
- Printing from the Quick Access Toolbar
- Creating a Basic Chart ___________________________________________________________________________
- Choosing the Right Kind of Chart
- Selecting Chart Data and Creating the Chart
- Resizing Your Chart
- Changing the Chart Options.
- Switching Your Chart’s Focus
- Changing Chart Colors.
- Formatting Your Chart Wall, Area, and Gridlines
- Customizing Your Legend
- Adding Titles
- Enhancing Your Chart with Data Labels and Tables
- Changing Chart Types
- Chapter
- Inserting Illustrations
- Designing with Illustrations.
- Adding Saved Pictures
- Using Clipart
- Formatting Illustrations
- Adjusting Illustrations.
- Applying Picture Styles
- Arranging Illustrations
- Changing the Size
- Working with Shapes.
- Creating SmartArt
- Adding Pictures to SmartArt
- Changing SmartArt Shapes.
- Using Screenshots.
- Adding WordArt
- Designing with Illustrations.
- PART
- Chapter WORKING WITH GRAPHICS
- Cre ating Excel Charts
- Inspecting for Private Information. ___________________________________________________________________________
- Hiding Data
- Hiding Worksheets
- Hiding a Workbook
- Locking and Unlocking Cells
- Protecting Data
- Protecting a Worksheet.
- Protecting a Workbook
- Preventing a Workbook from Being Opened
- Marking a Workbook as Final
- Chapter
- Collaborating with Others
- Considering Your Collaboration Options
- Turning Track Changes On and Off.
- Choosing Which Changes to Track
- Editing with Track Changes
- Listing Tracked Changes in a New Sheet
- Accepting and Rejecting Collaborative Changes
- Using Comments in Collaboration
- Adding Comments.
- Editing and Deleting Comments
- Formatting Comments.
- Validating Excel Worksheet Content
- Setting Up Rules, Messages, and Alerts
- Looking for Duplicate Entries
- Considering Your Collaboration Options
- PART
- USING EXCEL TOOLS
- Chapter
- Us ing Security Options
- Importing and Exporting Your Excel Content ___________________________________________________________________________
- Using Word, PowerPoint, and Excel Together
- Using Access Tables and Excel Worksheets Together.
- Using Your Excel Data in Other Applications.
- Importing and Exporting Your Excel Content ___________________________________________________________________________
- Chapter
- Using PivotTables
- Creating a PivotTable
- Choosing Your PivotTable Data
- Setting Up Your PivotTable Layout
- Using the PivotTable Tools
- Sorting and Filtering PivotTable Data
- Filtering PivotTable Fields.
- Changing Sort Order
- Updating a PivotTable Report
- Formatting Your PivotTable
- Creating a PivotChart
- Creating a PivotTable
What You’ll Find in This Book This book is designed to take you from the very basic Excel skills to the more complex features, all at a manageable pace, through the use of visual instruction, clear, informative discussion, logical procedures, and insightful examples. Tips and Notes are plentiful, providing expert advice to go along with the topic at hand, and thus enhancing your understanding. As a reader, you can take a linear approach, read- ing the entire book from start to finish, or you can take a more shotgun tactic, looking up just those features you need to use right away. The book can be used effectively either way. We hope, however, that you’ll read everything, so that you can confidently say you know how to: Open the application and begin using the Excel workspace Create your own workbook from scratch Add and remove worksheets Navigate a workbook with your mouse and keyboard shortcuts Save your workbook Enter and edit worksheet text and numbers
Microsoft Excel 20 1 0 is the latest version of what is undoubtedly the world’s most popular spreadsheet software. It provides powerful tools for storing and manipulating just about any numerical or text-based data—for accounting, statistics, sales tracking, and scientific analysis— and also gives you effective tools for viewing and accessing the data quickly and conveniently. It’s also a great tool for building charts, which take your complex numeric data and convert it to simple, compelling images. Of course, you’ll want to use your charts—and perhaps pieces of your worksheets—in your Word and PowerPoint creations, to create a complete set of documents, reports, and presentations that help convey your message to anyone, anywhere. Excel makes that possible, enhancing the power of the entire Office suite. For all that power, Excel has always been a very user-friendly application, a fact that hasn’t changed over the years. There are quite a few tools, however, that bear explaining, which is where this book comes in. For new and self- taught beginning users, this book provides a great set of foundation skills. It then takes read- ers through more complex and powerful tools, providing comprehensive coverage of a variety of topics that will make this book a must-have for more experienced users as well. There’s “something for everybody” here, so you’ll find what you need in Excel 2010.
Introduction
Part 1
THE BASICS
Chapter 1: Creating a Basic Excel Worksheet
Chapter 2: Working with Formulas
Chapter 3: Using Excel Functions
Chapter 4: Correcting Formula Errors
Chapter 5: Making your Worksheet Look Good
many criteria to consider—some positive and some negative—about each alternative. If only there were a way to compile the data in a way that makes the decision easier. There is...Excel!
Microsoft Excel is the most widely used spreadsheet program in the world. A spreadsheet is a software application that organizes data in rows and columns. Spreadsheets are most commonly used to manipulate numerical data like those used to establish your household budget, calculate profit/loss statements for your business, or determine your GPA.
As good as Excel is at handling numerical data, it can also be used to organize other types of data, including text and formulas. Use the rows and columns in Excel to enter data about your household inventory for your insurance needs, your address book to make sure that birthday cards are sent out on time, or even catalogue your CD collection. Once the data has been stored in Excel, you can sort and filter the data to suit your needs.
Understanding how to create an effective spreadsheet can help you make better decisions. But first, let’s start with the basics.
Chapter 1
Many times you are required to make a difficult decision. Perhapsyou
want to buy a new car, or decide which college to attend, orwhich investment option will result in the biggest return. There are
Creating a Basic
Excel Worksheet
Name box: Shows the cell address, or name, of the active cell. You can use the range name feature to customize this name. (See “Working with Range Names” later in this chapter.) Insert Function button: Opens the Insert Function dialog box. (See Chapter 3, “Using Excel Functions.”) Formula bar: Displays the contents of the active cell. Scroll bars: Excel worksheets have both horizontal and vertical scroll bars. Sheet tabs: A new Excel Workbook opens with three worksheets. You can delete unneeded worksheets, or add extra work- sheets to suit your needs.
Apart from the Ribbon, which will be discussed later in this chapter, you will need to be aware of several other elements (see Figure 1-2). Title bar: At the top of the application you see a title bar that shows the applica- tion name and the file name. Active cell: The currently selected cell is called the active cell. The active cell has a thick border around it.
Figure 1-
Elements of the Excel window.
Name Box Insert Function Button
Active Cell
Status Bar
Formula Bar Title Bar
Sheet Tabs
Scroll Bars
Cell Addresses Every cell in Excel has an address. The cell address is the column letter and row num- ber associated with a particular cell. Cell A is the cell address for the cell that is active when Excel is first opened.
Status bar: At the bottom of the Excel screen is the status bar that provides feedback to you of the current state of your worksheet. The status bar will indicate if the worksheet is ready for data entry, busy calculating, or has identified an error.
Zeroing in on the Ribbon First introduced in Microsoft Office 2007, the Ribbon (see Figure 1-3) is common to all Microsoft Office applications. It visually displays all of the most commonly used options needed to perform a particular task. The Ribbon groups these command buttons under functional tabs. Excel program icon: Clicking on the program icon in the upper-left corner of Excel displays a menu with options for minimizing and closing the application. Quick Access Toolbar: The Quick Access Toolbar provides access to basic file functions. By default, those functions are Save, Undo, and Redo. However, as demonstrated in Figure 1-4, you can click the Customize Quick Access Toolbar arrow and choose More Commands from the drop-down menu to add the commands that you use most frequently.
Figure 1-
Excel’s Ribbon is a task-based collection of the commands you can perform in the application.
Customize Quick Access Toolbar
Quick Access Toolbar
Excel Program Icon
Title Bar Minimize the Ribbon Button
Tip
Don’t think that Sheet1 is the best name for your worksheet? You can rename the sheet tabs. Double-click the sheet tab name and type a name that better describes your data.
Introducing the Backstage View New to Office 2010 applications is the introduc- tion of a Backstage view of the documents you are working with. The Backstage view is a collec- tion of the commands you use to open a new or existing document. You will also find the commands you might use as you are exiting a document. For instance, commands related to saving, printing, protecting, versioning, and storing properties about your document. None of these commands affect the appearance of the document and affect the whole file, not just a portion of the file. To access Excel’s Backstage view, choose the File tab on the Ribbon. Let’s take a look at some of the things you can do from this new view. Info tab: Illustrated in Figure 1-8, the Info tab can be used to establish security options for the file, like requiring a pass- word to open the file, or protecting the file from changes to the formatting. You can also record data about your files. You will learn more about these features later in this chapter.
Galleries: A Gallery is most often a collection of related formatting options. For instance, in Figure 1-7, clicking the down arrow next to the Themes command button on the Page Layout tab displays a preview of all themes that can be applied to your document. Make a selection from the gallery or click the arrow again to close the gallery. Themes are discussed further in Chapter 5, “Making the Worksheet Look Good.”
Figure 1-
The Themes Gallery shows you all themes that can be applied to your document.
Figure 1-
Excel 2010’s File tab gives you access to the new Backstage view.
Print tab: Change the page layout, attach headers and footers, select a printer, and specify exactly what you want Excel to print from this tab. You’ll learn more about these options in Chapter 10, “Printing and Other Output Formats.” Save & Send tab: With the Save & Send tab, you can either send your file as an e-mail attachment, create a PDF version of the file, or save it online, where others can view it with one of the new Office Web applications. You can read more about this tab later in this chapter. Help tab: From this tab, you can access simple solutions to common questions, as well as get information about product support, and links to contact Microsoft directly with your questions or suggestions about Excel. If you are new to Excel, the Getting Started button offers a variety of online tutorials and additional training to accelerate your learning curve. The Options button opens the Excel Options dialog box.
Recent tab: This tab displays a list of the last 20 documents that were opened in Excel 2010. New tab: Open a new blank spreadsheet, or create a new file based on one of the dozens of templates provided with Excel. Excel gives all new workbooks a default file name (Book1, Book2, and so on) until you replace it.
Caution If you close a file in Excel without sav- ing it first, Excel will remind you to save a copy as in previous versions. Excel 2010 goes one step further by automat- ically saving a copy of your file every 10 minutes while you are working. You will be prompted to open the latest version of that file when you re-open Excel.