book

Excel® 2013 Absolute Beginner’s Guide

by Tracy Syrstad

March 2013

Beginner

368 pages

9h 30m

English

Que

Read now

Unlock full access

Who Should Read This BookHow This Book Is OrganizedDownloading the Example FilesConventions Used In This BookSpecial Elements
Taking a Closer Look at the Excel WindowCustomizing the Excel WindowMoving Around and Making Selections on a SheetInstalling Optional Components
Managing WorkbooksUsing Templates to Quickly Create New WorkbooksWorking with Sheets and TabsWorking with Rows and ColumnsWorking with Cells
Types of Data You Enter into ExcelEntering Different Types of Data into a CellControlling the Next Cell SelectionUsing Copy, Cut, Paste, Paste Special to Enter DataUsing Text to Columns to Separate Data in a Single ColumnInserting Symbols and Equations into a CellUsing Web Queries to Get Data onto a SheetUsing Series to Quickly Fill a RangeEditing DataWorking with TablesFixing Numbers Stored as TextSpellchecking Your SheetFinding Data on Your SheetUsing Data Validation to Limit Data Entry in a Cell
Adjusting Row Heights and Column WidthsChanging the Font Settings of a CellAligning Text in a CellReflowing Text in a ParagraphApplying Number Formats with Format CellsCreating a Custom FormatWhen Cell Formatting Doesn’t Seem to Be Working RightThe Number Group on the RibbonAdding a Border Around a RangeColoring the Inside of a CellCreating HyperlinksQuick Formatting with the Format PainterDynamic Cell Formatting with Conditional FormattingUsing Cell Styles to Quickly Apply FormattingUsing Themes to Ensure Uniformity in Design
The Importance of Laying Out Data ProperlyAdjusting Calculation SettingsViewing Formulas Versus ValuesEntering a Formula into a CellThree Ways of Entering a Formula’s Cell ReferencesRelative Versus Absolute FormulasUsing a Cell on Another Sheet in a FormulaUsing R1C1 Notation to Reference CellsUsing F4 to Change the Cell ReferencingMathematical OperatorsCopying a Formula to Another CellUsing Names to Simplify ReferencesInserting Formulas into TablesUsing Table Names in Table FormulasWriting Table Formulas Outside the TableUsing Array FormulasConverting Formulas to ValuesTroubleshooting Formulas
Breaking Down a FunctionFinding FunctionsEntering Functions Using the Function Arguments Dialog BoxEntering Functions Using In-Cell TipsUsing the AutoSum ButtonUsing Lookup Functions to Match a Value and Return AnotherSUMIFSSUMPRODUCTLogical FunctionsNested IF StatementsIFERRORDate and Time FunctionsTroubleshooting Dates and Times Stored as StringsGoal SeekUsing the Function Arguments Dialog Box to Troubleshoot Formulas
Preparing DataOpening the Sort Dialog BoxSorting by ValuesSorting by Color or IconUsing the Quick Sort ButtonsQuick Sorting Multiple ColumnsRandomly Sorting DataSorting with a Custom SequenceRearranging Columns Using the Sort Dialog BoxRearranging Columns Using the MouseFixing Sort Problems
Preparing DataApplying a Filter to a Data SetClearing a FilterReapplying a FilterTurning Filtering On for One ColumnFiltering for Listed ItemsFiltering the Grouped Dates ListingUsing the Search Function to Filter for or Exclude ItemsUsing the Search Function on Grouped DatesUsing Text, Number, and Date Special FiltersFiltering by Color or IconFiltering by SelectionAllowing Filtering on a Protected SheetUsing the Advanced Filter OptionRemoving Duplicates from a Data SetConsolidating Data
Using Cell Comments to Add Notes to CellsAllowing Multiple Users to Edit a Workbook at the Same TimeHiding and Unhiding SheetsLocking Rows or Columns in PlaceCreating Custom Views of Your DataConfiguring the Page SetupCreating a Custom Header or FooterUsing Page Break Preview to Set Page BreaksPrinting SheetsProtecting Your Workbook from Unwanted ChangesProtecting the Data on a SheetPreventing Changes by Marking a File as FinalRestricting Access Using IRMCertifying a Workbook with a Digital SignatureSharing Files Between Excel VersionsRemoving Hidden or Confidential InformationRecovering Lost ChangesSending an Excel File as an AttachmentSharing a File Online
Using the SUBTOTAL FunctionSummarizing Data Using the Subtotal ToolCopying the Subtotals to a New LocationFormatting the SubtotalsApplying Different Subtotal Function TypesCombining Multiple Subtotal Results to One RowSubtotaling by Multiple ColumnsSorting SubtotalsAdding Space Between Subtotaled GroupsGrouping and Outlining Rows and Columns
Preparing DataElements of a ChartTypes of ChartsAdding a Chart to a SheetAdding, Removing, and Formatting Chart ElementsEditing and Formatting a Chart TitleChanging the Display Units in an AxisApplying Chart Styles and ColorsApplying Chart LayoutsMoving or Resizing a ChartSwitching Rows and ColumnsChanging an Existing Chart’s TypeCreating a Chart with Multiple Chart TypesUpdating Chart DataCreating Stock ChartsCreating Bubble ChartsPie Chart Issue: Small SlicesAdding Sparklines to DataCreating a Chart Using a User-Created Template
Preparing Data for Use in a PivotTablePivotTable LimitationsPivotTable CompatibilityPivotTable Field ListCreating a PivotTableChanging How a PivotTable Appears on a SheetPivotTable SortingExpanding and Collapsing FieldsViewing the Records Used to Calculate a ValueGrouping DatesFiltering Data in a PivotTableCreating a Calculated FieldHiding TotalsFormatting ValuesSlicers
Power View RequirementsCreating ReportsInserting and Formatting a Report TitleChanging Data VisualizationsCombining Multiple VisualizationsChanging ColorsSortingFilteringSharing Power View Reports
Working with SmartArtInserting WordArtInserting Pictures
Enabling VBA SecurityDeveloper TabIntroduction to the Visual Basic EditorUnderstanding How the Macro Recorder WorksRecording a MacroRunning a MacroUser-Defined Functions
RequirementsAcquiring a Microsoft AccountUploading and Downloading WorkbooksOpening a WorkbookCreating a New Workbook OnlineSaving a File to Your Local DriveSharing a Folder or WorkbookInteracting with a Sheet OnlineConfiguring Browser View OptionsSetting Up a Survey