book

Excel® 2007 Bible

by John Walkenbach

January 2007

Beginner to intermediate

912 pages

21h 53m

English

Wiley

Read now

Unlock full access

Dedication
Is This Book for You?Software VersionsConventions This Book UsesExcel commandsFilenames, named ranges, and your inputKey namesFunctionsMouse conventionsWhat the Icons MeanHow This Book Is OrganizedHow to Use This BookWhat’s on the Companion CD
What Is Excel Good For?Understanding Workbooks and WorksheetsMoving Around a WorksheetNavigating with your keyboardNavigating with your mouseIntroducing the RibbonRibbon tabsContextual tabsTypes of commands on the RibbonAccessing the Ribbon using your keyboardUsing the shortcut menusCustomizing your Quick Access ToolbarWorking with Dialog BoxesNavigating dialog boxesUsing tabbed dialog boxesUsing the TaskbarCreating Your First Excel WorksheetGetting started on your worksheetFilling in the month namesEntering the sales dataFormatting the numbersMaking your worksheet look a bit fancierSumming the valuesCreating a chartPrinting your worksheetSaving your workbook
A New User InterfaceLarger WorksheetsNew File FormatsWorksheet TablesStyles and ThemesBetter Looking ChartsPage Layout ViewEnhanced Conditional FormattingConsolidated OptionsSmartArtFormula AutoCompleteCollaboration FeaturesCompatibility CheckerImproved Pivot TablesNew Worksheet FunctionsOther New Features
Exploring the Types of Data You Can UseAbout numerical valuesAbout text entriesAbout formulasEntering Text and Values into Your WorksheetsEntering Dates and Times into Your WorksheetsEntering date valuesEntering time valuesModifying Cell ContentsErasing the contents of a cellReplacing the contents of a cellEditing the contents of a cellLearning some handy data-entry techniquesAutomatically moving the cell pointer after entering dataUsing arrow keys instead of pressing EnterSelecting a range of input cells before entering dataUsing Ctrl+Enter to place information into multiple cells simultaneouslyEntering decimal points automaticallyUsing AutoFill to enter a series of valuesUsing AutoComplete to automate data entryForcing text to appear on a new line within a cellUsing AutoCorrect for shorthand data entryEntering numbers with fractionsSimplifying data entry by using a formEntering the current date or time into a cellApplying Number FormattingImproving readability by formatting numbersUsing automatic number formattingFormatting numbers by using the RibbonUsing shortcut keys to format numbersFormatting numbers using the Format Cells dialog boxAdding your own custom number formats
Learning the Fundamentals of Excel WorksheetsWorking with Excel’s windowsMoving and resizing windowsSwitching among windowsClosing windowsActivating a worksheetAdding a new worksheet to your workbookDeleting a worksheet you no longer needChanging the name of a worksheetChanging a sheet tab’s colorRearranging your worksheetsHiding and unhiding a worksheetControlling the Worksheet ViewZooming in or out for a better viewViewing a worksheet in multiple windowsComparing sheets side by sideSplitting the worksheet window into panesKeeping the titles in view by freezing panesMonitoring cells with a Watch WindowWorking with Rows and ColumnsInserting rows and columnsDeleting rows and columnsHiding rows and columnsChanging column widths and row heightsChanging column widthsChanging row heights

Understanding Cells and RangesSelecting rangesSelecting complete rows and columnsSelecting noncontiguous rangesSelecting multisheet rangesSelecting special types of cellsSelecting cells by searchingCopying or Moving RangesCopying by using Ribbon commandsCopying by using shortcut menu commandsCopying by using shortcut keysCopying or moving by using drag-and-dropCopying to adjacent cellsCopying a range to other sheetsUsing the Office Clipboard to pastePasting in special waysUsing the Paste Special Dialog boxPerforming mathematical operations without formulasSkipping blanks when pastingTransposing a rangeUsing Names to Work with RangesCreating range names in your workbooksUsing the New Name dialog boxUsing the Name boxUsing the Create Names From Selection dialog boxManaging NamesAdding Comments to CellsFormatting commentsChanging a comment’s shapeReading commentsHiding and showing commentsEditing commentsDeleting comments
What Is a Table?Creating a TableChanging the Look of a TableWorking with TablesNavigating in a tableSelecting parts of a tableAdding new rows or columnsDeleting rows or columnsMoving a tableSetting table optionsWorking with the Total RowRemoving duplicate rows from a tableSorting and filtering a tableSorting a tableFiltering a tableConverting a table back to a range
Getting to Know the Formatting ToolsUsing the Formatting Tools in the Home TabUsing the Mini toolbarUsing the Format Cells dialog boxUsing Formatting in Your WorksheetsUsing different fontsChanging text alignmentChoosing horizontal alignment optionsChoosing vertical alignment optionsWrapping or shrinking text to fit the cellMerging worksheet cells to create additional text spaceDisplaying text at an angleControlling the text directionUsing colors and shadingAdding borders and linesAdding a background image to a worksheetUsing Named Styles for Easier FormattingApplying stylesModifying an existing styleCreating new stylesMerging styles from other workbooksControlling styles with templatesUnderstanding Document ThemesApplying a themeCustomizing a theme
Excel File OperationsCreating a new workbookOpening an existing workbookSelecting a different locationUsing the My Places barFiltering filenamesChoosing your file display preferencesSaving a WorkbookUsing AutoRecoverSpecifying a passwordOther workbook optionsSetting workbook propertiesInspecting a documentEncrypting a documentRestricting permissionsAdding a digital signatureMarking a document finalChecking compatibilityClosing workbooksSafeguarding your workExcel File CompatibilityRecognizing the Excel 2007 file formatsSaving a file for use with an older version of Excel
Exploring Excel TemplatesViewing templatesCreating a workbook from a templateModifying a templateUnderstanding Custom Excel TemplatesWorking with the default templatesUsing the workbook template to change workbook defaultsUsing the worksheet template to change worksheet defaultsEditing your templatesResetting the default workbook and worksheet settingsCreating custom templatesSaving your custom templatesIdeas for creating templates
Printing with One ClickChanging Your Page ViewNormal ViewPage Layout ViewPage Break PreviewAdjusting Common Page Setup SettingsAdjusting the page marginsChanging the page orientationSpecifying the paper sizeSpecifying the print areaUnderstanding page breaksInserting a page breakRemoving page breaks you’ve addedUsing a background imagePrinting row and column titlesScaling printed outputPrinting cell gridlinesPrinting row and column headersAdding a Header or Footer to Your ReportsSelecting a predefined header or footerUnderstanding header and footer element codesOther header and footer optionsAdjusting the Settings in the Print Dialog BoxChoosing your printerSpecifying what you want to printPrinting multiple copies of a reportPreventing Certain Cells from Being PrintedPreventing Objects from being PrintedCreating Custom Views of Your Worksheet
Understanding Formula BasicsUsing operators in formulasUnderstanding operator precedence in formulasUsing functions in your formulasExamples of formulas that use functionsFunction argumentsMore about functionsEntering Formulas into Your WorksheetsEntering formulas manuallyEntering formulas by pointingPasting range names into formulasInserting functions into formulasFunction entry tipsEditing FormulasUsing Cell References in FormulasUsing relative, absolute, and mixed referencesChanging the types of your referencesReferencing cells outside the worksheetReferencing cells in other worksheetsReferencing cells in other workbooksUsing Formulas In TablesSummarizing data in a tableUsing formulas within a tableReferencing data in a tableCorrecting Common Formula ErrorsHandling circular referencesSpecifying when formulas are calculatedUsing Advanced Naming TechniquesUsing names for constantsUsing names for formulasUsing range intersectionsApplying names to existing referencesTips for Working with FormulasDon’t hard-code valuesUsing the Formula bar as a calculatorMaking an exact copy of a formulaConverting formulas to values
A Few Words About TextHow many characters in a cell?Numbers as textText FunctionsDetermining whether a cell contains textWorking with character codesThe CODE functionThe CHAR functionDetermining whether two strings are identicalJoining two or more cellsDisplaying formatted values as textDisplaying formatted currency values as textRepeating a character or stringCreating a text histogramPadding a numberRemoving excess spaces and nonprinting charactersCounting characters in a stringChanging the case of textExtracting characters from a stringReplacing text with other textFinding and searching within a stringSearching and replacing within a stringAdvanced Text FormulasCounting specific characters in a cellCounting the occurrences of a substring in a cellExtracting a filename from a path specificationExtracting the first word of a stringExtracting the last word of a stringExtracting all but the first word of a stringExtracting first names, middle names, and last namesRemoving titles from namesCreating an ordinal numberCounting the number of words in a cell
How Excel Handles Dates and TimesUnderstanding date serial numbersEntering datesUnderstanding time serial numbersEntering timesFormatting dates and timesProblems with datesExcel’s leap year bugPre-1900 datesInconsistent date entriesDate-Related FunctionsDisplaying the current dateDisplaying any dateGenerating a series of datesConverting a nondate string to a dateCalculating the number of days between two datesCalculating the number of work days between two datesOffsetting a date using only work daysCalculating the number of years between two datesCalculating a person’s ageDetermining the day of the yearDetermining the day of the weekDetermining the date of the most recent SundayDetermining the first day of the week after a dateDetermining the nth occurrence of a day of the week in a monthCalculating dates of holidaysNew Year’s DayMartin Luther King, Jr. DayPresidents’ DayMemorial DayIndependence DayLabor DayVeterans DayColumbus DayThanksgiving DayChristmas DayDetermining the last day of a monthDetermining whether a year is a leap yearDetermining a date’s quarterTime-Related FunctionsDisplaying the current timeDisplaying any timeCalculating the difference between two timesSumming times that exceed 24 hoursConverting from military timeConverting decimal hours, minutes, or seconds to a timeAdding hours, minutes, or seconds to a timeRounding time valuesWorking with non-time-of-day values
Counting and Summing Worksheet CellsBasic Counting FormulasCounting the total number of cellsCounting blank cellsCounting nonblank cellsCounting numeric cellsCounting text cellsCounting nontext cellsCounting logical valuesCounting error values in a rangeAdvanced Counting FormulasCounting cells by using the COUNTIF functionCounting cells by using multiple criteriaUsing And criteriaUsing Or criteriaCombining And and Or criteriaCounting the most frequently occurring entryCounting the occurrences of specific textEntire cell contentsPartial cell contentsTotal occurrences in a rangeCounting the number of unique valuesCreating a frequency distributionThe FREQUENCY functionUsing formulas to create a frequency distributionUsing the Analysis ToolPak to create a frequency distributionUsing a pivot table to create a frequency distributionSumming FormulasSumming all cells in a rangeComputing a cumulative sumSumming the “top n” valuesConditional Sums Using a Single CriterionSumming only negative valuesSumming values based on a different rangeSumming values based on a text comparisonSumming values based on a date comparisonConditional Sums Using Multiple CriteriaUsing And criteriaUsing Or criteriaUsing And and Or criteria
Introducing Lookup FormulasFunctions Relevant to LookupsBasic Lookup FormulasThe VLOOKUP functionThe HLOOKUP functionThe LOOKUP functionCombining the MATCH and INDEX functionsSpecialized Lookup FormulasLooking up an exact valueLooking up a value to the leftPerforming a case-sensitive lookupChoosing among multiple lookup tablesDetermining letter grades for test scoresCalculating a grade-point averagePerforming a two-way lookupPerforming a two-column lookupDetermining the cell address of a value within a rangeLooking up a value by using the closest match
The Time Value of MoneyLoan CalculationsWorksheet functions for calculating loan informationThe PMT functionThe PPMT functionThe IPMT FunctionThe RATE functionThe NPER functionThe PV FunctionA loan calculation exampleCredit-card paymentsCreating a loan amortization scheduleSummarizing loan options by using a data tableCreating a one-way data tableCreating a two-way data tableCalculating a loan with irregular paymentsInvestment CalculationsFuture value of a single depositCalculating simple interestCalculating compound interestCalculating interest with continuous compoundingFuture value of a series of depositsDepreciation Calculations
Understanding Array FormulasA multicell array formulaA single-cell array formulaCreating an array constantArray constant elementsUnderstanding the Dimensions of an ArrayOne-dimensional horizontal arraysOne-dimensional vertical arraysTwo-dimensional arraysNaming Array ConstantsWorking with Array FormulasEntering an array formulaSelecting an array formula rangeEditing an array formulaExpanding or contracting a multicell array formulaUsing Multicell Array FormulasCreating an array from values in a rangeCreating an array constant from values in a rangePerforming operations on an arrayUsing functions with an arrayTransposing an arrayGenerating an array of consecutive integersUsing Single-Cell Array FormulasCounting characters in a rangeSumming the three smallest values in a rangeCounting text cells in a rangeEliminating intermediate formulasUsing an array in lieu of a range reference
Working with Single-Cell Array FormulasSumming a range that contains errorsCounting the number of error values in a rangeSumming the n largest values in a rangeComputing an average that excludes zerosDetermining whether a particular value appears in a rangeCounting the number of differences in two rangesReturning the location of the maximum value in a rangeFinding the row of a value’s nth occurrence in a rangeReturning the longest text in a rangeDetermining whether a range contains valid valuesSumming the digits of an integerSumming rounded valuesSumming every nth value in a rangeRemoving non-numeric characters from a stringDetermining the closest value in a rangeReturning the last value in a columnReturning the last value in a rowRanking data with an array formulaWorking with Multicell Array FormulasReturning only positive values from a rangeReturning nonblank cells from a rangeReturning a list of unique items in a rangeDisplaying a calendar in a range
What Is a Chart?Understanding How Excel Handles ChartsEmbedded chartsChart sheetsCreating a ChartHands On: Creating and Customizing a ChartSelecting the dataChoosing a chart typeExperimenting with different layoutsTrying another view of the dataTrying other chart typesTrying other chart stylesWorking with ChartsMoving and resizing a chartCopying a chartDeleting a chartAdding chart elementsMoving and deleting chart elementsFormatting chart elementsPrinting ChartsUnderstanding Chart TypesChoosing a chart typeChart type examplesColumn chartsBar chartsLine chartsPie chartsXY (scatter) chartsArea chartsDoughnut chartsRadar chartsSurface chartsBubble chartsStock chartsLearning More
Understanding Chart CustomizationSelecting Chart ElementsSelecting with the mouseSelecting with the keyboardSelecting with the Chart Element controlUser Interface Choices for Modifying Chart ElementsUsing the Format dialog boxUsing the Ribbon and Mini ToolbarModifying the Chart AreaModifying the Plot AreaWorking with Chart TitlesWorking with the LegendWorking with GridlinesModifying the AxesValue axis optionsCategory axis optionsWorking with Data SeriesDeleting a data seriesAdding a new data series to a chartChanging data used by a seriesChanging the data range by dragging the range outlineUsing the Edit Series dialog boxEditing the Series formulaDisplaying data labels in a chartHandling missing dataAdding error barsAdding a trendlineModifying 3-D chartsCreating combination chartsDisplaying a data tableCreating Chart TemplatesLearning Some Chart-Making TricksCreating picture chartsCreating a thermometer chartCreating a gauge chartDisplaying conditional colors in a column chartCreating a comparative histogramCreating a Gantt chartPlotting mathematical functions with one variablePlotting mathematical functions with two variables
About Conditional FormattingSpecifying Conditional FormattingFormatting types you can applyMaking your own rulesConditional Formats That Use GraphicsUsing data barsA data bar exampleUsing data bars in lieu of a chartUsing color scalesA color scale exampleAn extreme color scale exampleUsing Icon SetsAn icon set exampleAnother icon set exampleDisplaying only one iconCreating Formula-Based RulesUnderstanding relative and absolute referencesConditional formatting formula examplesIdentifying weekend daysDisplaying alternate-row shadingCreating checkerboard shadingShading groups of rowsDisplaying a total only when all values are enteredWorking with Conditional FormatsManaging rulesCopying cells that contain conditional formattingDeleting conditional formattingFind and Replace limitationsLocating cells that contain conditional formatting
Using ShapesInserting a ShapeAdding text to a ShapeFormatting ShapesGrouping objectsAligning and spacing objectsReshaping ShapesPrinting objectsUsing SmartArtInserting SmartArtCustomizing SmartArtChanging the layoutChanging the StyleLearning more about SmartArtUsing WordArtWorking with Other Graphic TypesAbout graphics filesUsing the Clip Art task paneInserting graphics filesCopying graphics by using the ClipboardDisplaying a worksheet background image
About the QATAdding New Commands to the QATOther QAT ActionsBehind the Scenes
About Number FormattingAutomatic number formattingFormatting numbers by using the RibbonUsing shortcut keys to format numbersUsing the Format Cells dialog box to format numbersCreating a Custom Number FormatParts of a number format stringCustom number format codesCustom Number Format ExamplesScaling valuesDisplaying values in thousandsDisplaying values in hundredsDisplaying values in millionsAdding zeros to a valueDisplaying leading zerosDisplaying fractionsDisplaying a negative sign on the rightFormatting dates and timesDisplaying text with numbersSuppressing certain types of entriesFilling a cell with a repeating character
About Data ValidationSpecifying Validation CriteriaTypes of Validation Criteria You Can ApplyCreating a Drop-Down ListUsing Formulas for Data Validation RulesUnderstanding Cell ReferencesData Validation ExamplesAccepting text onlyAccepting a larger value than the previous cellAccepting nonduplicate entries onlyAccepting text that begins with AAccepting only a date that’s a MondayAccepting only values that don’t exceed a total
Introducing Worksheet OutlinesCreating an OutlinePreparing the dataCreating an outline automaticallyCreating an outline manuallyWorking with OutlinesDisplaying levelsAdding data to an outlineRemoving an outlineHiding the outline symbols
Linking WorkbooksCreating External Reference FormulasUnderstanding the link formula syntaxCreating a link formula by pointingPasting linksWorking with External Reference FormulasCreating links to unsaved workbooksOpening a workbook with external reference formulasChanging the startup promptUpdating linksChanging the link sourceSevering linksAvoiding Potential Problems with External Reference FormulasRenaming or moving a source workbookUsing the Save As commandModifying a source workbookIntermediary linksConsolidating WorksheetsConsolidating worksheets by using formulasConsolidating worksheets by using Paste SpecialConsolidating worksheets by using the Consolidate commandAn exampleRefreshing a consolidationMore about consolidation
Understanding How Excel Uses HTMLUnderstanding the Different Web FormatsCreating an HTML fileCreating a single file Web pageOpening an HTML FileWorking with HyperlinksInserting a hyperlinkUsing hyperlinksUsing Web Queries
Understanding Data SharingCopying and PastingCopying from Excel to WordPasting static informationPasting a linkEmbedding Objects in a WorksheetEmbedding Word documentsEmbedding other types of documentsEmbedding an Excel Workbook in a Word DocumentEmbedding a workbook in Word by copyingEmbedding a saved workbook in WordCreating a new Excel object in Word
Using Excel on a NetworkUnderstanding File ReservationsSharing WorkbooksUnderstanding shared workbooksDesignating a workbook as a shared workbookControlling the advanced sharing settingsTracking changesUpdating changesResolving conflicting changes between usersControlling the Include in Personal View settingsTracking Workbook ChangesTurning Track Changes on and offReviewing the changes
Types of ProtectionWorksheet ProtectionUnlocking cellsSheet protection optionsAssigning User PermissionsWorkbook ProtectionRequiring a password to open a workbookProtecting a workbook’s structureProtecting a workbook’s windowsVB Project ProtectionRelated TopicsSaving a workbook as a PDF fileMarking a workbook finalInspecting a workbookUsing a digital signatureGetting a digital IDSigning a workbook
Finding and Correcting Formula ErrorsMismatched parenthesesCells are filled with hash marksBlank cells are not blankExtra space charactersFormulas returning an error#DIV/0! errors#N/A errors#NAME? errors#NULL! errors#NUM! errors#REF! errors#VALUE! errorsAbsolute/relative reference problemsOperator precedence problemsFormulas are not calculatedActual versus displayed valuesFloating point number errors“Phantom link” errorsUsing Excel’s Auditing ToolsIdentifying cells of a particular typeViewing formulasTracing cell relationshipsIdentifying precedentsIdentifying dependentsTracing error valuesFixing circular reference errorsUsing background error-checking featureUsing Excel Formula EvaluatorSearching and ReplacingSearching for informationReplacing informationSearching for formattingSpell Checking Your WorksheetsUsing AutoCorrect
Understanding External Database FilesImporting Access TablesRetrieving Data with Query: An ExampleThe database fileThe taskUsing Query to get the dataSelecting a data sourceUsing the Query WizardQuery Wizard: Choosing the columnsQuery Wizard: Filtering dataQuery Wizard: Sort orderQuery Wizard: FinishSpecifying a location for the dataWorking with Data Returned by QueryAdjusting the external data range propertiesRefreshing a queryDeleting a queryChanging your queryUsing Query Without the WizardCreating a query manuallyUsing multiple database tablesAdding and editing records in external database tablesFormatting dataLearning More about Query
About Pivot TablesA pivot table exampleData appropriate for a pivot tableCreating a Pivot TableSpecifying the dataSpecifying the location for the pivot tableLaying out the pivot tableFormatting the pivot tableModifying the pivot tableMore Pivot Table ExamplesQuestion 1Question 2Question 3Question 4Question 5Question 6Learning More
Working with Non-Numeric DataGrouping Pivot Table ItemsA manual grouping exampleViewing grouped dataAutomatic grouping examplesGrouping by dateGrouping by timeCreating a Frequency DistributionCreating a Calculated Field or Calculated ItemCreating a calculated fieldInserting a calculated itemReferencing Cells within a Pivot TableCreating Pivot ChartsA pivot chart exampleMore about pivot chartsAnother Pivot Table ExampleProducing a Report with a Pivot Table
A What-If ExampleTypes of What-If AnalysesManual What-If AnalysisCreating Data TablesCreating a one-input data tableCreating a two-input data tableUsing Scenario ManagerDefining scenariosDisplaying scenariosModifying scenariosMerging scenariosGenerating a scenario report
What-If Analysis, in ReverseSingle-Cell Goal SeekingA goal-seeking exampleMore about Goal SeekingIntroducing SolverAppropriate problems for SolverA simple Solver exampleMore about SolverSolver ExamplesSolving simultaneous linear equationsMinimizing shipping costsAllocating resourcesOptimizing an investment portfolio
The Analysis ToolPak: An OverviewInstalling the Analysis ToolPak Add-inUsing the Analysis ToolsIntroducing the Analysis ToolPak ToolsThe Analysis of variance toolThe Correlation toolThe Covariance toolThe Descriptive Statistics toolThe Exponential Smoothing toolThe F-Test (two-sample test for variance) toolThe Fourier Analysis toolThe Histogram toolThe Moving Average toolThe Random Number Generation toolThe Rank and Percentile toolThe Regression toolThe Sampling toolThe t-Test toolThe z-Test (Two-Sample Test for Means) tool
Introducing VBA MacrosDisplaying the Developer tabAbout Macro SecuritySaving Workbooks That Contain MacrosTwo Types of VBA MacrosVBA Sub proceduresVBA functionsCreating VBA MacrosRecording VBA macrosRecording your actions to create VBA code: The basicsRecording a macro: A simple exampleExamining the macroTesting the macroEditing the macroAnother exampleExamining the macroTesting the macroMore about recording VBA macrosAbsolute versus relative recordingStoring macros in your Personal Macro WorkbookAssigning a macro to a shortcut keyAssigning a macro to a buttonWriting VBA codeThe basics: Entering and editing codeHow VBA worksObjects and collectionsPropertiesMethodsVariablesControlling executionThe If-Then constructFor-Next loopsThe With-End With constructThe Select Case constructA macro that can’t be recordedLearning More
Overview of VBA FunctionsAn Introductory ExampleA custom functionUsing the function in a worksheetAnalyzing the custom functionAbout Function ProceduresExecuting Function ProceduresCalling custom functions from a procedureUsing custom functions in a worksheet formulaFunction Procedure ArgumentsA function with no argumentA function with one argumentAnother function with one argumentA function with two argumentsA function with a range argumentDebugging Custom FunctionsInserting Custom FunctionsLearning More
Why Create UserForms?UserForm AlternativesThe InputBox functionThe MsgBox functionCreating UserForms: An OverviewWorking with UserFormsAdding controlsChanging the properties of a controlHandling eventsDisplaying a UserFormA UserForm ExampleCreating the UserFormTesting the UserFormCreating an event-handler procedureAnother UserForm ExampleCreating the UserFormTesting the UserFormCreating event-handler proceduresTesting the UserFormMaking the macro available from a worksheet buttonMaking the macro available on your Quick Access ToolbarMore on Creating UserFormsAdding accelerator keysControlling tab orderLearning More
Why Use Controls on a Worksheet?Using ControlsAdding a controlAbout design modeAdjusting propertiesCommon propertiesLinking controls to cellsCreating macros for controlsReviewing the Available ActiveX ControlsCheckBox controlComboBox controlCommandButton controlImage controlLabel controlListBox controlOptionButton controlScrollBar controlSpinButton controlTextBox controlsToggleButton control
Understanding EventsEntering event-handler VBA codeUsing Workbook-Level EventsUsing the Open eventUsing the SheetActivate eventUsing the NewSheet eventUsing the BeforeSave eventUsing the BeforeClose eventWorking with Worksheet EventsUsing the Change eventMonitoring a specific range for changesUsing the SelectionChange eventUsing the BeforeRightClick eventUsing Non-Object EventsUsing the OnTime eventUsing the OnKey event
Working with RangesCopying a rangeCopying a variable-size rangeSelecting to the end of a row or columnSelecting a row or columnMoving a rangeLooping through a range efficientlyPrompting for a cell valueDetermining the type of selectionIdentifying a multiple selectionCounting selected cellsWorking with WorkbooksSaving all workbooksSaving and closing all workbooksWorking with ChartsModifying the chart typeModifying chart propertiesApplying chart formattingVBA Speed TipsTurning off screen updatingPreventing alert messagesSimplifying object referencesDeclaring variable types
What Is an Add-In?Working with Add-InsWhy Create Add-Ins?Creating Add-InsAn Add-In ExampleSetting up the workbookProcedures in Module1About the UserFormTesting the workbookAdding descriptive informationProtecting the projectCreating the add-inCreating the user interface for your add-in macroInstalling the add-in
System RequirementsUsing the CDWhat’s on the CDeBook version of Excel 2007 BibleSample files for the Excel 2007 BibleChapter 01Chapter 03Chapter 05Chapter 06Chapter 07Chapter 11Chapter 12Chapter 13Chapter 14Chapter 15Chapter 16Chapter 18Chapter 19Chapter 20Chapter 21Chapter 24Chapter 25Chapter 26Chapter 27Chapter 28Chapter 33Chapter 34Chapter 35Chapter 36Chapter 37Chapter 38Chapter 39Chapter 40Chapter 41Chapter 42Chapter 43Chapter 44Chapter 45TroubleshootingCustomer Care
The Excel Help SystemMicrosoft Technical SupportSupport optionsMicrosoft Knowledge BaseMicrosoft Excel Home PageMicrosoft Office Home PageInternet NewsgroupsAccessing newsgroups by using a newsreaderAccessing newsgroups by using a Web browserSearching newsgroupsInternet Web SitesThe Spreadsheet PageDaily Dose of ExcelJon Peltier’s Excel PagePearson Software ConsultingStephen Bullen’s Excel PageDavid McRitchie’s Excel PagesMr. Excel

Content preview from Excel® 2007 Bible

Chapter 18. Performing Magic with Array Formulas

<feature><title>In This Chapter</title>

More examples of single-cell array formulas
More examples of multicell array formulas
Returning an array from a custom VBA function

</feature>

The preceding chapter provides an introduction to arrays and array formulas and presented some basic examples to whet your appetite. This chapter continues the saga and provides many useful examples that further demonstrate the power of this feature.

I selected the examples in this chapter to provide a good assortment of the various uses for array formulas. You can use most of them as-is. You will, of course, need to adjust the range names or references used. Also, you can modify many of the examples easily to work in a slightly different manner.

Working with Single-Cell Array Formulas

As I describe in the preceding chapter, you enter single-cell array formulas into a single cell (not into a range of cells). These array formulas work with arrays contained in a range or that exist in memory. This section provides some additional examples of such array formulas.

On the CD-ROM

The examples in this section are available on the companion CD-ROM. The file is named single-cell array formulas.xlsx.

Summing a range that contains errors

You may have discovered that the SUM function doesn’t work if you attempt to sum a range that contains one or more error values (such as #DIV/0! or #N/A). Figure 18.1 shows an example. The formula in cell C11 returns an error value because ...