CVV logo
विद्यया रक्षिता संस्कृतिः सर्वदा।
संस्कृतेर्मानवाः संस्कृता भूरिदा:।।
Knowledge protects culture forever
Cultured people share abundantly.Swami Tejomayananda Founder – Chinmaya Vishwavidyapeeth
CVV logo
L I B R A R Y   O P A C
Amazon cover image
Image from Amazon.com
Image from Google Jackets

Excel 2016 Formulas

By: Contributor(s): Material type: TextTextPublication details: New Delhi Wiley 2016Description: 782ISBN:
  • 9788126559879
DDC classification:
  • 005.369 M58211 E 103945
Contents:
Part I: Understanding Formula Basics Chapter 1: The Excel User Interface in a Nutshell The Workings of Workbooks Worksheets Chart sheets Macro sheets and dialog sheets The Excel User Interface The Ribbon Backstage View Shortcut menus and the mini toolbar Dialog boxes Customizing the UI Task panes Customizing onscreen display Numeric formatting Stylistic formatting Protection Options Securing access to the entire workbook Limiting access to specific worksheet ranges Protecting the workbook structure Chapter 2: Basic Facts About Formulas Entering and Editing Formulas Formula elements Entering a formula Pasting names Spaces and line breaks Formula limits Sample formulas Editing formulas Using Operators in Formulas Reference operators Sample formulas that use operators Operator precedence Nested parentheses Calculating Formulas Cell and Range References Creating an absolute or a mixed reference Referencing other sheets or workbooks Copying or Moving Formulas Making an Exact Copy of a Formula Converting Formulas to Values Hiding Formulas Errors in Formulas Dealing with Circular References Goal Seeking A goal seeking example More about goal seeking Chapter 3: Working with Names What's in a Name? A Name's Scope Referencing names Referencing names from another workbook Conflicting names The Name Manager Creating names Editing names Deleting names Shortcuts for Creating Cell and Range Names The New Name dialog box Creating names using the Name box Creating names from text in cells Naming entire rows and columns Names created by Excel Creating Multi sheet Names Working with Range and Cell Names Creating a list of names Using names in formulas Using the intersection operators with names Using the range operator with names Referencing a single cell in a multi cell named range Applying names to existing formulas Applying names automatically when creating a formula Unapplying names Names with errors Viewing named ranges Using names in charts How Excel Maintains Cell and Range Names Inserting a row or column Deleting a row or a column Cutting and pasting Potential Problems with Names Name problems when copying sheets Name problems when deleting sheets The Secret to Understanding Names Naming constants Naming text constants Using worksheet functions in named formulas Using cell and range references in named formulas Using named formulas with relative references Advanced Techniques That Use Names Using the INDIRECT function with a named range Using arrays in named formulas Creating a dynamic named formula Using an XLM macro in a named formula Part II: Leveraging Excel Functions Chapter 4: Introducing Worksheet Functions What is a Function? Simplify your formulas Perform otherwise impossible calculations Speed up editing tasks Provide decision-making capability More about functions Function Argument Types Names as arguments Full-column or full-row as arguments Literal values as arguments Expressions as arguments Other functions as arguments Arrays as arguments Ways to Enter a Function into a Formula Entering a function manually Using the Function Library commands Using the Insert Function dialog box More tips for entering functions Chapter 5: Manipulating Text A Few Words About Text How many characters in a cell? Numbers as text Text Functions Determining whether a cell contains text Working with character codes Determining whether two strings are identical Joining two or more cells Displaying formatted values as text Displaying formatted currency values as text Removing excess spaces and nonprinting characters Counting characters in a string Repeating a character or string Creating a text histogram Padding a number Changing the case of text Extracting characters from a string Replacing text with other text Finding and searching within a string Searching and replacing within a string Advanced Text Formulas Counting specific characters in a cell Counting the occurrences of a substring in a cell Removing trailing minus signs Expressing a number as an ordinal Determining a column letter for a column number Extracting a filename from a path specification Extracting the first word of a string Extracting the last word of a string Extracting all but the first word of a string Extracting first names, middle names and last names Removing titles from names Counting the number of words in a cell Chapter 6: Working with Dates and Times How Excel Handles Dates and Times Understanding date serial numbers Entering dates Understanding time serial numbers Entering times Formatting dates and times Problems with dates Date-Related Functions Displaying the current date Displaying any date with a function Generating a series of dates Converting a nondate string to a date Calculating the number of days between two dates Calculating the number of work days between two dates Offsetting a date using only work days Calculating the number of years between two dates Calculating a person's age Determining the day of the year Determining the day of the week Determining the week of the year Determining the date of the most recent Sunday Determining the first day of the week after a date Determining the nth occurrence of a day of the week in a month Counting the occurrences of a day of the week Expressing a date as an ordinal number Calculating dates of holidays Determining the last day of a month Determining whether a year is a leap year Determining a date's quarter Converting a year to roman numerals Time-Related Functions Displaying the current time Displaying any time using a function Calculating the difference between two times Summing times that exceed 24 hours Converting from military time Converting decimal hours, minutes or seconds to a time Adding hours, minutes or seconds to a time Converting between time zones Rounding time values Calculating Durations Chapter 7: Counting and Summing Techniques Counting and Summing Worksheet Cells Other Counting Methods Basic Counting Formulas Counting the total number of cells Counting blank cells Counting nonblank cells Counting numeric cells Counting text cells Counting non text cells Counting logical values Counting error values in a range Advanced Counting Formulas Counting cells with the COUNTIF function Counting cells that meet multiple criteria Counting the most frequently occurring entry Counting the occurrences of specific text Counting the number of unique values Creating a frequency distribution Summing Formulas Summing all cells in a range Summing a range that contains errors Computing a cumulative sum Summing the "top n" values Conditional Sums Using a Single Criterion Summing only negative values Summing values based on a different range Summing values based on a text comparison Summing values based on a date comparison Conditional Sums Using Multiple Criteria Using And criteria Using or criteria Using And and Or criteria Chapter 8: Using Lookup Functions What Is a Lookup Formula? Functions Relevant to Lookups Basic Lookup Formulas The VLOOKUP function The HLOOKUP function The LOOKUP function Combining the MATCH and INDEX functions Specialized Lookup Formulas Looking up an exact value Looking up a value to the left Performing a case-sensitive lookup Choosing among multiple lookup tables Determining letter grades for test scores Calculating a grade point average Performing a two-way lookup Performing a two-column lookup Determining the address of a value within a range Looking up a value by using the closest match Looking up a value using linear interpolation Chapter 9: Working with Tables and Lists Tables and Terminology A list example A table example Working with Tables Creating a table Changing the look of a table Navigating and selecting in a table Adding new rows or columns Deleting rows or columns Moving a table Removing duplicate rows from a table Sorting and filtering a table Working with the Total row Using formulas within a table Referencing data in a table Converting a table to a list Using Advanced Filtering Setting up a criteria range Applying an advanced filter Clearing an advanced filter Specifying Advanced Filter Criteria Specifying a single criterion Specifying multiple criteria Specifying computed criteria Using Database Functions Inserting Subtotals Chapter 10: Miscellaneous Calculations Unit Conversions Rounding Numbers Basic rounding formulas Rounding to the nearest multiple Rounding currency values Working with fractional dollars Using the INT and TRUNC functions Rounding to an even or odd integer Rounding to n significant digits Solving Right Triangles Area, Surface, Circumference and Volume Calculations Calculating the area and perimeter of a square Calculating the area and perimeter of a rectangle Calculating the area and perimeter of a circle Calculating the area of a trapezoid Calculating the area of a triangle Calculating the surface and volume of a sphere Calculating the surface and volume of a cube Calculating the surface and volume of a rectangular solid Calculating the surface and volume of a cone Calculating the volume of a cylinder Calculating the volume of a pyramid Solving Simultaneous Equations Working with Normal Distributions Part III: Financial Formulas Chapter 11: Borrowing and Investing Formulas The Time Value of Money Loan Calculations Worksheet functions for calculating loan information A loan calculation example Credit card payments Creating a loan amortization schedule Calculating a loan with irregular payments Investment Calculations Future value of a single deposit Present value of a series of payments Future value of a series of deposits Chapter 12: Discounting and Depreciation Formulas Using the NPV Function Definition of NPV NPV function examples Using the IRR Function Rate of return Geometric growth rates Checking results Irregular Cash Flows Net present value Internal rate of return Depreciation Calculations Chapter 13: Financial Schedules Creating Financial Schedules Creating Amortization Schedules A simple amortization schedule A dynamic amortization schedule Credit card calculations Summarizing Loan Options Using a Data Table Creating a one-way data table Creating a two-way data table Financial Statements and Ratios Basic financial statements Ratio analysis Creating Indices Part IV: Array Formulas Chapter 14: Introducing Arrays Introducing Array Formulas A multi cell array formula A single cell array formula Creating an array constant Array constant elements Understanding the Dimensions of an Array One dimensional horizontal arrays One dimensional vertical arrays Two dimensional arrays Naming Array Constants Working with Array Formulas Entering an array formula Selecting an array formula range Editing an array formula Expanding or contracting a multi cell array formula Using Multi cell Array Formulas Creating an array from values in a range Creating an array constant from values in a range Performing operations on an array Using functions with an array Transposing an array Generating an array of consecutive integers Using Single Cell Array Formulas Counting characters in a range Summing the three smallest values in a range Counting text cells in a range Eliminating intermediate formulas Using an array in lieu of a range reference Chapter 15: Performing Magic with Array Formulas Working with Single Cell Array Formulas Summing a range that contains errors Counting the number of error values in a range Summing the n largest values in a range Computing an average that excludes zeros Determining whether a particular value appears in a range Counting the number of differences in two ranges Returning the location of the maximum value in a range Finding the row of a value's nth occurrence in a range Returning the longest text in a range Determining whether a range contains valid values Summing the digits of an integer Summing rounded values Summing every nth value in a range Removing nonnumeric characters from a string Determining the closest value in a range Returning the last value in a column Returning the last value in a row Working with Multi cell Array Formulas Returning only positive values from a range Returning nonblank cells from a range Reversing the order of cells in a range Sorting a range of values dynamically Returning a list of unique items in a range Displaying a calendar in a range Part V: Miscellaneous Formula Techniques Chapter 16: Importing and Cleaning Data A Few Words About Data Importing Data Importing from a file Importing a text file into a specified range Copying and pasting data Data Cleanup Techniques Removing duplicate rows Identifying duplicate rows Splitting text Changing the case of text Removing extra spaces Removing strange characters Converting values Classifying values Joining columns Rearranging columns Randomizing the rows Matching text in a list Change vertical data to horizontal data Filling gaps in an imported report Spelling checking Replacing or removing text in cells Adding text to cells Fixing trailing minus signs A Data Cleaning Checklist Exporting Data Exporting to a text file Exporting to other file formats Chapter 17: Charting Techniques Understanding the SERIES Formula Using names in a SERIES formula Unlinking a chart series from its data range Creating Links to Cells Adding a chart title link Adding axis title links Adding text links Adding a linked picture to a chart Chart Examples Single data point charts Displaying conditional colors in a column chart Creating a comparative histogram Creating a Gantt chart Creating a box plot Plotting every nth data point Identifying maximum and minimum values in a chart Creating a Timeline Plotting mathematical functions Plotting a circle Creating a clock chart Creating awesome designs Working with trendlines Linear trendlines Working with nonlinear trendlines Summary of trendline equations Creating Interactive Charts Selecting a series from a drop down list Plotting the last n data points Choosing a start date and number of points Displaying population data Displaying weather data Chapter 18: Pivot Tables About Pivot Tables A Pivot Table Example Data Appropriate for a Pivot Table Creating a Pivot Table Automatically Creating a Pivot Table Manually Specifying the data Specifying the location for the pivot table Laying out the pivot table Formatting the pivot table Modifying the pivot table More Pivot Table Examples Question 1 Question 2 Question 3 Question 4 Question 5 Question 6 Question 7 Grouping Pivot Table Items A manual grouping example Viewing grouped data Automatic grouping examples Creating a Frequency Distribution Creating a Calculated Field or Calculated Item Creating a calculated field Inserting a calculated item Filtering Pivot Tables with Slicers Filtering Pivot Tables with a Timeline Referencing Cells Within a Pivot Table Another Pivot Table Example Using the Data Model Creating Pivot Charts A pivot chart example More about pivot charts Chapter 19: Conditional Formatting About Conditional Formatting Specifying Conditional Formatting Formatting types you can apply Making your own rules Conditional Formats That Use Graphics Using data bars Using color scales Using icon sets Creating Formula-Based Rules Understanding relative and absolute references Conditional formatting formula examples Working with Conditional Formats Managing rules Copying cells that contain conditional formatting Deleting conditional formatting Locating cells that contain conditional formatting Chapter 20: Using Data Validation About Data Validation Specifying Validation Criteria Types of Validation Criteria You Can Apply Creating a Drop Down List Using Formulas for Data Validation Rules Understanding Cell References Data Validation Formula Examples Accepting text only Accepting a larger value than the previous cell Accepting non duplicate entries only Accepting text that begins with a specific character Accepting dates by the day of the week Accepting only values that don't exceed a total Creating a dependent list Using Structured Table Referencing Chapter 21: Creating Megaformulas What Is a Megaformula? Creating a Megaformula: A Simple Example Megaformula Examples Using a megaformula to remove middle names Using a megaformula to return a string's last space character position Using a megaformula to determine the validity of a credit card number Using Intermediate Named Formulas Generating random names The Pros and Cons of Megaformulas Chapter 22: Tools and Methods for Debugging Formulas Formula Debugging? Formula Problems and Solutions Mismatched parentheses Cells are filled with hash marks Blank cells are not blank Extra space characters Formulas returning an error Absolute / relative reference problems Operator precedence problems Formulas are not calculated Actual versus displayed values Floating]point number errors Phantom link errors Logical value errors Circular reference errors Excel's Auditing Tools Identifying cells of a particular type Viewing formulas Tracing cell relationships Tracing error values Fixing circular reference errors Using background error checking Using Excel's Formula Evaluator Part VI: Developing Custom Worksheet Functions Chapter 23: Introducing
Reviews from LibraryThing.com:
Tags from this library: No tags from this library for this title. Log in to add tags.
Star ratings
    Average rating: 0.0 (0 votes)
Holdings
Item type Current library Collection Call number Status Barcode
Books Books Ubhayabharati General Stacks General 005.369 M58211 E 103945 (Browse shelf(Opens below)) Available 103945

Excel 2016 Formulas is fully updated to cover all of the tips, tricks, and techniques you need to maximize the power of Excel 2016 through the use of formulas

Part I: Understanding Formula Basics

Chapter 1: The Excel User Interface in a Nutshell

The Workings of Workbooks
Worksheets
Chart sheets
Macro sheets and dialog sheets
The Excel User Interface
The Ribbon
Backstage View
Shortcut menus and the mini toolbar
Dialog boxes
Customizing the UI
Task panes
Customizing onscreen display
Numeric formatting
Stylistic formatting
Protection Options
Securing access to the entire workbook
Limiting access to specific worksheet ranges
Protecting the workbook structure


Chapter 2: Basic Facts About Formulas

Entering and Editing Formulas
Formula elements
Entering a formula
Pasting names
Spaces and line breaks
Formula limits
Sample formulas
Editing formulas
Using Operators in Formulas
Reference operators
Sample formulas that use operators
Operator precedence
Nested parentheses
Calculating Formulas
Cell and Range References
Creating an absolute or a mixed reference
Referencing other sheets or workbooks
Copying or Moving Formulas
Making an Exact Copy of a Formula
Converting Formulas to Values
Hiding Formulas
Errors in Formulas
Dealing with Circular References
Goal Seeking
A goal seeking example
More about goal seeking


Chapter 3: Working with Names

What's in a Name?
A Name's Scope
Referencing names
Referencing names from another workbook
Conflicting names
The Name Manager
Creating names
Editing names
Deleting names
Shortcuts for Creating Cell and Range Names
The New Name dialog box
Creating names using the Name box
Creating names from text in cells
Naming entire rows and columns
Names created by Excel
Creating Multi sheet Names
Working with Range and Cell Names
Creating a list of names
Using names in formulas
Using the intersection operators with names
Using the range operator with names
Referencing a single cell in a multi cell named range
Applying names to existing formulas
Applying names automatically when creating a formula
Unapplying names
Names with errors
Viewing named ranges
Using names in charts
How Excel Maintains Cell and Range Names
Inserting a row or column
Deleting a row or a column
Cutting and pasting
Potential Problems with Names
Name problems when copying sheets
Name problems when deleting sheets
The Secret to Understanding Names
Naming constants
Naming text constants
Using worksheet functions in named formulas
Using cell and range references in named formulas
Using named formulas with relative references
Advanced Techniques That Use Names
Using the INDIRECT function with a named range
Using arrays in named formulas
Creating a dynamic named formula
Using an XLM macro in a named formula


Part II: Leveraging Excel Functions

Chapter 4: Introducing Worksheet Functions

What is a Function?
Simplify your formulas
Perform otherwise impossible calculations
Speed up editing tasks
Provide decision-making capability
More about functions
Function Argument Types
Names as arguments
Full-column or full-row as arguments
Literal values as arguments
Expressions as arguments
Other functions as arguments
Arrays as arguments
Ways to Enter a Function into a Formula
Entering a function manually
Using the Function Library commands
Using the Insert Function dialog box
More tips for entering functions


Chapter 5: Manipulating Text

A Few Words About Text
How many characters in a cell?
Numbers as text
Text Functions
Determining whether a cell contains text
Working with character codes
Determining whether two strings are identical
Joining two or more cells
Displaying formatted values as text
Displaying formatted currency values as text
Removing excess spaces and nonprinting characters
Counting characters in a string
Repeating a character or string
Creating a text histogram
Padding a number
Changing the case of text
Extracting characters from a string
Replacing text with other text
Finding and searching within a string
Searching and replacing within a string
Advanced Text Formulas
Counting specific characters in a cell
Counting the occurrences of a substring in a cell
Removing trailing minus signs
Expressing a number as an ordinal
Determining a column letter for a column number
Extracting a filename from a path specification
Extracting the first word of a string
Extracting the last word of a string
Extracting all but the first word of a string
Extracting first names, middle names and last names
Removing titles from names
Counting the number of words in a cell


Chapter 6: Working with Dates and Times

How Excel Handles Dates and Times
Understanding date serial numbers
Entering dates
Understanding time serial numbers
Entering times
Formatting dates and times
Problems with dates
Date-Related Functions
Displaying the current date
Displaying any date with a function
Generating a series of dates
Converting a nondate string to a date
Calculating the number of days between two dates
Calculating the number of work days between two dates
Offsetting a date using only work days
Calculating the number of years between two dates
Calculating a person's age
Determining the day of the year
Determining the day of the week
Determining the week of the year
Determining the date of the most recent Sunday
Determining the first day of the week after a date
Determining the nth occurrence of a day of the week in a month
Counting the occurrences of a day of the week
Expressing a date as an ordinal number
Calculating dates of holidays
Determining the last day of a month
Determining whether a year is a leap year
Determining a date's quarter
Converting a year to roman numerals
Time-Related Functions
Displaying the current time
Displaying any time using a function
Calculating the difference between two times
Summing times that exceed 24 hours
Converting from military time
Converting decimal hours, minutes or seconds to a time
Adding hours, minutes or seconds to a time
Converting between time zones
Rounding time values
Calculating Durations


Chapter 7: Counting and Summing Techniques

Counting and Summing Worksheet Cells
Other Counting Methods
Basic Counting Formulas
Counting the total number of cells
Counting blank cells
Counting nonblank cells
Counting numeric cells
Counting text cells
Counting non text cells
Counting logical values
Counting error values in a range
Advanced Counting Formulas
Counting cells with the COUNTIF function
Counting cells that meet multiple criteria
Counting the most frequently occurring entry
Counting the occurrences of specific text
Counting the number of unique values
Creating a frequency distribution
Summing Formulas
Summing all cells in a range
Summing a range that contains errors
Computing a cumulative sum
Summing the "top n" values
Conditional Sums Using a Single Criterion
Summing only negative values
Summing values based on a different range
Summing values based on a text comparison
Summing values based on a date comparison
Conditional Sums Using Multiple Criteria
Using And criteria
Using or criteria
Using And and Or criteria


Chapter 8: Using Lookup Functions

What Is a Lookup Formula?
Functions Relevant to Lookups
Basic Lookup Formulas
The VLOOKUP function
The HLOOKUP function
The LOOKUP function
Combining the MATCH and INDEX functions
Specialized Lookup Formulas
Looking up an exact value
Looking up a value to the left
Performing a case-sensitive lookup
Choosing among multiple lookup tables
Determining letter grades for test scores
Calculating a grade point average
Performing a two-way lookup
Performing a two-column lookup
Determining the address of a value within a range
Looking up a value by using the closest match
Looking up a value using linear interpolation


Chapter 9: Working with Tables and Lists

Tables and Terminology
A list example
A table example
Working with Tables
Creating a table
Changing the look of a table
Navigating and selecting in a table
Adding new rows or columns
Deleting rows or columns
Moving a table
Removing duplicate rows from a table
Sorting and filtering a table
Working with the Total row
Using formulas within a table
Referencing data in a table
Converting a table to a list
Using Advanced Filtering
Setting up a criteria range
Applying an advanced filter
Clearing an advanced filter
Specifying Advanced Filter Criteria
Specifying a single criterion
Specifying multiple criteria
Specifying computed criteria
Using Database Functions
Inserting Subtotals


Chapter 10: Miscellaneous Calculations

Unit Conversions
Rounding Numbers
Basic rounding formulas
Rounding to the nearest multiple
Rounding currency values
Working with fractional dollars
Using the INT and TRUNC functions
Rounding to an even or odd integer
Rounding to n significant digits
Solving Right Triangles
Area, Surface, Circumference and Volume Calculations
Calculating the area and perimeter of a square
Calculating the area and perimeter of a rectangle
Calculating the area and perimeter of a circle
Calculating the area of a trapezoid
Calculating the area of a triangle
Calculating the surface and volume of a sphere
Calculating the surface and volume of a cube
Calculating the surface and volume of a rectangular solid
Calculating the surface and volume of a cone
Calculating the volume of a cylinder
Calculating the volume of a pyramid
Solving Simultaneous Equations
Working with Normal Distributions


Part III: Financial Formulas

Chapter 11: Borrowing and Investing Formulas

The Time Value of Money
Loan Calculations
Worksheet functions for calculating loan information
A loan calculation example
Credit card payments
Creating a loan amortization schedule
Calculating a loan with irregular payments
Investment Calculations
Future value of a single deposit
Present value of a series of payments
Future value of a series of deposits


Chapter 12: Discounting and Depreciation Formulas

Using the NPV Function
Definition of NPV
NPV function examples
Using the IRR Function
Rate of return
Geometric growth rates
Checking results
Irregular Cash Flows
Net present value
Internal rate of return
Depreciation Calculations


Chapter 13: Financial Schedules

Creating Financial Schedules
Creating Amortization Schedules
A simple amortization schedule
A dynamic amortization schedule
Credit card calculations
Summarizing Loan Options Using a Data Table
Creating a one-way data table
Creating a two-way data table
Financial Statements and Ratios
Basic financial statements
Ratio analysis
Creating Indices


Part IV: Array Formulas

Chapter 14: Introducing Arrays

Introducing Array Formulas
A multi cell array formula
A single cell array formula
Creating an array constant
Array constant elements
Understanding the Dimensions of an Array
One dimensional horizontal arrays
One dimensional vertical arrays
Two dimensional arrays
Naming Array Constants
Working with Array Formulas
Entering an array formula
Selecting an array formula range
Editing an array formula
Expanding or contracting a multi cell array formula
Using Multi cell Array Formulas
Creating an array from values in a range
Creating an array constant from values in a range
Performing operations on an array
Using functions with an array
Transposing an array
Generating an array of consecutive integers
Using Single Cell Array Formulas
Counting characters in a range
Summing the three smallest values in a range
Counting text cells in a range
Eliminating intermediate formulas
Using an array in lieu of a range reference


Chapter 15: Performing Magic with Array Formulas

Working with Single Cell Array Formulas
Summing a range that contains errors
Counting the number of error values in a range
Summing the n largest values in a range
Computing an average that excludes zeros
Determining whether a particular value appears in a range
Counting the number of differences in two ranges
Returning the location of the maximum value in a range
Finding the row of a value's nth occurrence in a range
Returning the longest text in a range
Determining whether a range contains valid values
Summing the digits of an integer
Summing rounded values
Summing every nth value in a range
Removing nonnumeric characters from a string
Determining the closest value in a range
Returning the last value in a column
Returning the last value in a row
Working with Multi cell Array Formulas
Returning only positive values from a range
Returning nonblank cells from a range
Reversing the order of cells in a range
Sorting a range of values dynamically
Returning a list of unique items in a range
Displaying a calendar in a range


Part V: Miscellaneous Formula Techniques

Chapter 16: Importing and Cleaning Data

A Few Words About Data
Importing Data
Importing from a file
Importing a text file into a specified range
Copying and pasting data
Data Cleanup Techniques
Removing duplicate rows
Identifying duplicate rows
Splitting text
Changing the case of text
Removing extra spaces
Removing strange characters
Converting values
Classifying values
Joining columns
Rearranging columns
Randomizing the rows
Matching text in a list
Change vertical data to horizontal data
Filling gaps in an imported report
Spelling checking
Replacing or removing text in cells
Adding text to cells
Fixing trailing minus signs
A Data Cleaning Checklist
Exporting Data
Exporting to a text file
Exporting to other file formats


Chapter 17: Charting Techniques

Understanding the SERIES Formula
Using names in a SERIES formula
Unlinking a chart series from its data range
Creating Links to Cells
Adding a chart title link
Adding axis title links
Adding text links
Adding a linked picture to a chart
Chart Examples
Single data point charts
Displaying conditional colors in a column chart
Creating a comparative histogram
Creating a Gantt chart
Creating a box plot
Plotting every nth data point
Identifying maximum and minimum values in a chart
Creating a Timeline
Plotting mathematical functions
Plotting a circle
Creating a clock chart
Creating awesome designs
Working with trendlines
Linear trendlines
Working with nonlinear trendlines
Summary of trendline equations
Creating Interactive Charts
Selecting a series from a drop down list
Plotting the last n data points
Choosing a start date and number of points
Displaying population data
Displaying weather data


Chapter 18: Pivot Tables

About Pivot Tables
A Pivot Table Example
Data Appropriate for a Pivot Table
Creating a Pivot Table Automatically
Creating a Pivot Table Manually
Specifying the data
Specifying the location for the pivot table
Laying out the pivot table
Formatting the pivot table
Modifying the pivot table
More Pivot Table Examples
Question 1
Question 2
Question 3
Question 4
Question 5
Question 6
Question 7
Grouping Pivot Table Items
A manual grouping example
Viewing grouped data
Automatic grouping examples
Creating a Frequency Distribution
Creating a Calculated Field or Calculated Item
Creating a calculated field
Inserting a calculated item
Filtering Pivot Tables with Slicers
Filtering Pivot Tables with a Timeline
Referencing Cells Within a Pivot Table
Another Pivot Table Example
Using the Data Model
Creating Pivot Charts
A pivot chart example
More about pivot charts


Chapter 19: Conditional Formatting

About Conditional Formatting
Specifying Conditional Formatting
Formatting types you can apply
Making your own rules
Conditional Formats That Use Graphics
Using data bars
Using color scales
Using icon sets
Creating Formula-Based Rules
Understanding relative and absolute references
Conditional formatting formula examples
Working with Conditional Formats
Managing rules
Copying cells that contain conditional formatting
Deleting conditional formatting
Locating cells that contain conditional formatting


Chapter 20: Using Data Validation

About Data Validation
Specifying Validation Criteria
Types of Validation Criteria You Can Apply
Creating a Drop Down List
Using Formulas for Data Validation Rules
Understanding Cell References
Data Validation Formula Examples
Accepting text only
Accepting a larger value than the previous cell
Accepting non duplicate entries only
Accepting text that begins with a specific character
Accepting dates by the day of the week
Accepting only values that don't exceed a total
Creating a dependent list
Using Structured Table Referencing


Chapter 21: Creating Megaformulas

What Is a Megaformula?
Creating a Megaformula: A Simple Example
Megaformula Examples
Using a megaformula to remove middle names
Using a megaformula to return a string's last space character position
Using a megaformula to determine the validity of a credit card number
Using Intermediate Named Formulas
Generating random names
The Pros and Cons of Megaformulas


Chapter 22: Tools and Methods for Debugging Formulas

Formula Debugging?
Formula Problems and Solutions
Mismatched parentheses
Cells are filled with hash marks
Blank cells are not blank
Extra space characters
Formulas returning an error
Absolute / relative reference problems
Operator precedence problems
Formulas are not calculated
Actual versus displayed values
Floating]point number errors
Phantom link errors
Logical value errors
Circular reference errors
Excel's Auditing Tools
Identifying cells of a particular type
Viewing formulas
Tracing cell relationships
Tracing error values
Fixing circular reference errors
Using background error checking
Using Excel's Formula Evaluator


Part VI: Developing Custom Worksheet Functions

Chapter 23: Introducing

There are no comments on this title.

to post a comment.
Chinmaya Vishwa Vidyapeeth©2022.All rights reserved.
Supported by FOCUZINFOTECH.