Microsoft Excel Functions and Formulas E-Book

Mercury Learning and Information

• Herausgeber: Packt Publishing
• Kategorie: Lebensstil
• Sprache: Englisch

Seitenzahl: 408

Veröffentlichungsjahr: 2024

Beschreibung

In this updated edition for Excel 2021, Microsoft 365, and previous versions, this book reveals the secrets of Excel through practical examples in a quick reference format. It includes new chapters on data analytics and shortcut keys, making it an essential resource for both beginners and experienced users. The book is designed to help you understand, create, and apply formulas efficiently.
The journey begins with an introduction to formulas, followed by chapters on logical, text, date, and time functions. You will explore statistical, mathematical, and financial functions, along with database, lookup, and reference functions. The book also delves into conditional formatting, dynamic array formulas, special solutions, and user-defined functions, providing step-by-step instructions and ready-to-use Excel screenshots.
By the end of the course, you will have mastered Excel's advanced formulas and functions, enabling you to perform complex data analysis and enhance your productivity. Companion files include video tutorials, over 250 worksheet examples, and all figures from the text, ensuring a comprehensive learning experience.

Leseprobe

MICROSOFT® EXCEL® FUNCTIONS AND FORMULAS

SIXTH EDITION

LICENSE, DISCLAIMER OF LIABILITY, AND LIMITED WARRANTY

By purchasing or using this book (the “Work”), you agree that this license grants permission to use the contents contained herein, but does not give you the right of ownership to any of the textual content in the book or ownership to any of the information or products contained in it. This license does not permit uploading of the Work onto the Internet or on a network (of any kind) without the written consent ofthe Publisher. Duplication or dissemination of any text, code, simulations, images, etc. contained herein is limited to and subject to licensing terms for the respective products, and permission must be obtained from the Publisher or the owner of the content, etc., in order to reproduce or network any portion of the textual material (in any media) that is contained in the Work.

MERCURY LEARNING AND INFORMATION (“MLI” or “the Publisher”) and anyone involved in the creation, writing, or production of the companion disc, accompanying algorithms, code, or computer programs (“the software”), and any accompanying Web site or software of the Work, cannot and do not warrant the performance or results that might be obtained by using the contents of the Work. The author, developers, and the Publisher have used their best efforts to insure the accuracy and functionality of the textual material and/or programs contained in this package; we, however, make no warranty of any kind, express or implied, regarding the performance of these contents or programs. The Work is sold “as is” without warranty (except for defective materials used in manufacturing the book or due to faulty workmanship).

The author, developers, and the publisher of any accompanying content, and anyone involved in the composition, production, and manufacturing of this work will not be liable for damages of any kind arising out of the use of (or the inability to use) the algorithms, source code, computer programs, or textual material contained in this publication. This includes, but is not limited to, loss of revenue or profit, or other incidental, physical, or consequential damages arising out of the use of this Work.

The sole remedy in the event of a claim of any kind is expressly limited to replacement of the book, and only at the discretion of the Publisher. The use of “implied warranty” and certain “exclusions” vary from state to state, and might not apply to the purchaser of this product.

Companion files for this text are available for download by writing to the publisher at [e-mail].

MICROSOFT® EXCEL® FUNCTIONS AND FORMULAS

With Excel 2021 / Microsoft 365

SIXTH EDITION

BRIAN MORIARTYBERND HELDTHEODOR RICHARDSON

MERCURY LEARNING AND INFORMATIONDulles, VirginiaBoston, MassachusettsNew Delhi

Copyright ©2022 by MERCURY LEARNING AND INFORMATION LLC. All rights reserved.

This publication, portions of it, or any accompanying software may not be reproduced in any way, stored in a retrieval system of any type, or transmitted by any means, media, electronic display or mechanical display, including, but not limited to, photocopy, recording, Internet postings, or scanning, without prior permission in writing from the publisher.

Publisher: David PallaiMERCURY LEARNING AND INFORMATION22841 Quicksilver DriveDulles, VA [e-mail][phone]

B. Moriarty, B. Held, and T. Richardson. Microsoft®Excel® Functions and Formulas, Sixth EditionISBN: 978-1-683928-539

Microsoft, Excel, Visual Basic, and Windows are registered trademarks of Microsoft Corporation in the U.S. and other countries.

The publisher recognizes and respects all marks used by companies, manufacturers, and developers as a means to distinguish their products. All brand names and product names mentioned in this book are trademarks or service marks of their respective companies. Any omission or misuse (of any kind) of service marks or trademarks, etc. is not an attempt to infringe on the property of others.

Library of Congress Control Number: 2022935272

222324321 This book is printed on acid-free paper in the United States of America.

Our titles are available for adoption, license, or bulk purchase by institutions, corporations, etc. Digital versions of this title are available atwww.academiccourseware.comand most digital vendors. Companion disc files are available for downloading by [e-mail]. For additional information, please contact the Customer Service Dept. at [phone] (toll free).

The sole obligation of MERCURY LEARNING AND INFORMATION to the purchaser is to replace the disc, based on defective materials or faulty workmanship, but not based on the operation or functionality of the product.

CONTENTS

Acknowledgments

Introduction

Chapter 1: Formulas in Excel

Calculate production per hour

Calculate the age of a person in days

Calculate a price reduction

Convert currency

Convert from hours to minutes

Determine fuel consumption

Calculate your ideal and recommended weights

The quick calendar

Design your own to-do list

Increment row numbers

Convert negative values to positive

Calculate sales taxes

Combine text and numbers

Combine text and date

Combine text and time

Generate a special ranking list

Determine average output

Determine stock gains and losses

Evaluate profitability

Determine percentage of completion

Convert miles per hour to kilometers per hour

Convert feet per minute to meters per second

Convert liters to barrels, gallons, quarts, and pints

Convert from Fahrenheit to Celsius

Convert from Celsius to Fahrenheit

Calculate total with percentage

Monitor the daily production plan

Calculate the number of hours between two dates

Determine the price per pound

Determine how many pieces to put in a box

Calculate the number of employees required for a project

Distribute sales

Calculate your net income

Calculate the percentage of price reduction

Divide and double every three hours

Calculate the average speed

Calculate number of characters in a string

Chapter 2: Logical Functions

Use the AND function to compare two columns

Use the AND function to show sales for a specific period of time

Use the OR function to check cells for text

Use the OR function to check cells for numbers

Use the XOR function to check for mutually exclusive conditions

Use the IF function to compare columns and return a specific result

Use the IF function to check for larger, equivalent, or smaller values

Combine IF with AND to check several conditions

Use the IF function to determine the quarter of a year

Use the IF function to check cells in worksheets and workbooks

Use the IF function to calculate with different tax rates

Use the IF function to calculate the commissions for individual sales

Use the IFS function to calculate the commissions for individual sales

Use the IF function to compare two cells

Use the IFS function to compare two cells

Use the SWITCH function to compare two cells

Use the INT function with the IF function

Use the TYPE function to check for invalid values

Use nested IF functions to cover multiple possibilities

Use IFS function to cover multiple possibilities

Use SWITCH function to cover multiple possibilities

Use the IF function to check whether a date is in the past or the future

Use the IF function to create your own timesheet

Use the IFERROR function to display a default

Chapter 3: Text Functions

Use the LEFT and RIGHT functions to separate a text string of numbers

Use the LEFT function to convert invalid numbers to valid numbers

Use the SEARCH function to separate first name from last name

Use the MID function to separate last name from first name

Use the MID function to sum the digits of a number

Use the EXACT function to compare two columns

Use the SUBSTITUTE function to substitute characters

Use the SUBSTITUTE function to substitute parts of a cell

Use the SUBSTITUTE function to convert numbers to words

Use the SUBSTITUTE function to remove word wrapping in cells

Use the SUBSTITUTE function to combine and separate columns

Use the REPLACE function to replace and calculate

Use the FIND function to combine text and date

Use the UPPER function to convert text from lowercase to uppercase

Use the LOWER function to convert text from uppercase to lowercase

Use the PROPER function to convert initial characters from lowercase to uppercase

Use the FIXED function to round and convert numbers to text

Use the TRIM function to delete spaces

Use the TRIM function to convert “text-numbers” to real numbers

Use the CLEAN function to remove all non-printable characters

Use the REPT function to show data in graphic mode

Use the REPT function to show data in a chart

Use the CHAR function to check your fonts

Use the CHAR function to determine special characters

Use the CODE function to determine the numeric code of a character

Use the UNICHAR function to determine the Unicode character from a number

Use the UNICODE function to determine the numeric Unicode value of a character

Use the DOLLAR function to convert numbers to currency in text format

Use the T function to check for valid numbers

Use the TEXT function to combine and format text

Use CONCATENATE function to combine text

Use CONCAT function to combine text

Use TEXTJOIN function to combine text

Use ARRAYTOTEXT function to combine text

Use VALUETOTEXT function to display text from any cell value

Chapter 4: Date and Time Functions

Use custom formatting to display the day of the week

Use the WEEKDAY function to determine the weekend

Use the TODAY function to check for future dates

Use the TEXT function to calculate with the TODAY function

Use the NOW function to show the current time

Use the NOW function to calculate time

Use the DATE function to combine columns with date parts

Use the LEFT, MID, and RIGHT functions to extract date parts

Use the TEXT function to extract date parts

Use the DATEVALUE function to recalculate dates formatted as text

Use the YEAR function to extract the year part of a date

Use the MONTH function to extract the month part of a date

Use the DAY function to extract the day part of a date

Use the MONTH and DAY functions to sort birthdays by month

Use the DATE function to add months to a date

Use the EOMONTH function to determine the last day of a month

Use the DAYS360 function to calculate with a 360-day year

Use the WEEKDAY function to calculate with different hourly pay rates

Use the WEEKNUM function to determine the week number

Use the EDATE function to calculate months

Use the WORKDAY function to calculate workdays

Use the NETWORKDAYS function to determine the number of workdays

Use the YEARFRAC function to calculate ages of employees

Use the DATEDIF function to calculate ages of employees

Use the WEEKDAY function to calculate the weeks of Advent

Use the TIMEVALUE function to convert text to time

Use a custom format to create a time format

Use the HOUR function to calculate with 100-minute hours

Use the TIME function to combine single time parts

Chapter 5: Basic Statistical Functions

Use the MAX function to determine the largest value in a range

Use the MIN function to discover the lowest sales volume for a month

Use the MINIFS function to discover the lowest sales volume for a month based on criteria

Use the MAXIFS function to discover the highest sales volume for a month based on criteria

Use the MIN function to detect the smallest value in a column

Use the SMALL function to find the smallest values in a list

Use the LARGE function to find the highest values

Use the INDEX, MATCH, and LARGE functions to determine and locate the best salesperson

Use the SMALL function to compare prices and select the cheapest offer

Use the AVERAGE function to calculate the average output

Use the SUBTOTAL function to sum a filtered list

Use the COUNT function to count cells containing numeric data

Use the COUNTA function to count cells containing data

Use the COUNTA function to count cells containing text

Use the COUNTBLANK function to count empty cells

Use the COUNTA function to determine the last filled row

Use the SUBTOTAL function to count rows in filtered lists

Use the RANK, RANK.EQ, RANK.AVG functions to determine the rank of sales

Use the MEDIAN function to calculate the median sales

Use the QUARTILE, QUARTILE.INC, QUARTILE.EXC functions to calculate quartiles

Use the STDEV, STDEV.S, STDEV.P functions to determine the standard deviation

Use the FORECAST.LINEAR function to determine future values

Use the FORECAST.ETS function to determine future values

Use the FORECAST.ETS.CONFINT function to determine confidence in future values

Use the FORECAST.ETS.SEASONALITY function to future value patterns

Use the CORREL function to determine data correlation

Use the AVERAGE, GEOMEAN, HARMEAN, TRIMMEAN to determine meaningful averages

Chapter 6: Mathematical Functions

Use the SUM function to sum a range

Use the SUM function to sum several ranges

Use the SUMIF function to determine sales of a team

Use the SUMIF function to sum costs higher than $1,000

Use the SUMIF function to sum costs up to a certain date

Use the COUNTIF function to count phases that cost more than $1,000

Use the COUNTIF function to calculate an attendance list

Use the SUMPRODUCT function to calculate the value of the inventory

Use the SUMPRODUCT function to sum sales of a team

Use the SUMPRODUCT function to multiply and sum at the same time

Use the ROUND function to round numbers

Use the ROUNDDOWN function to round numbers down

Use the ROUNDUP function to round numbers up

Use the ROUND function to round time values to whole minutes

Use the ROUND function to round time values to whole hours

Use the MROUND function to round prices to 5 or 25 cents

Use the MROUND function to round values to the nearest multiple of 10 or 50

Use the CEILING/CEILING.MATH functions to round up prices to the nearest $100

Use the FLOOR/FLOOR.MATH function to round down prices to the nearest $100

Use the PRODUCT function to multiply values

Use the PRODUCT function to multiply conditional values

Use the QUOTIENT function to return the integer portion of a division

Use the POWER function to calculate square and cube roots

Use the POWER function to calculate interest

Use the MOD function to extract the remainder of a division

Modify the MOD function for divisors larger than the number

Use the ROW function to mark every other row

Use the SUBTOTAL function to perform several operations

Use the SUBTOTAL function to count all visible rows in a filtered list

Use the RAND function to generate random values

Use the RANDBETWEEN function to generate random values in a specified range

Use the RANDARRAY function to generate random values in a range of rows and columns

Use the EVEN and ODD functions to determine the nearest even/odd value

Use the ISEVEN and ISODD functions to check if a number is even or odd

Use the ISODD and ROW functions to determine odd rows

Use the ISODD and COLUMN functions to determine odd columns

Use the ROMAN function to convert Arabic numerals to Roman numerals

Use the ARABIC function to convert Roman numerals to Arabic numerals

Use the BASE function to convert decimal numbers to binary numbers

Use the DECIMAL function to convert binary numbers to decimal numbers

Use the SIGN function to check for the sign of a number

Use the SUMSQ function to determine the square sum

Use the GCD function to determine the greatest common divisor

Use the LCM function to determine the least common multiple

Use the SUMIFS function to determine sales of a team and gender of its members

Use the COUNTIFS function to count phases that cost more than $1,000 within a certain duration

Compare the INT, TRUNC, and ROUND functions

Use the ABS function to return only positive differences between numbers

Use the SQRT function to determine the hypotenuse of a right triangle

Use the COMBIN function to determine the number of combinations in a lottery

Use the FACT function to determine the number of combinations in a lottery

Use the LET and IFS functions to determine sales bonuses

Use the SEQUENCE function to generate a sequential list of dates in a row, column, or an array

Chapter 7: Basic Financial Functions

Use the SYD function to calculate depreciation

Use the SLN function to calculate straight-line depreciation

Use the PV function to decide an amount to invest

Use the PV function to compare investments

Use the DDB function to calculate using the double-declining balance method

Use the PMT function to determine the payment of a loan

Use the FV function to calculate total savings account balance

Use the RATE function to calculate interest rate

Use the INTRATE function to calculate interest over an entire period

Use the NOMINAL function to calculate real interest rate given an annual rate

Chapter 8: Database Functions

Use the DCOUNT function to count special cells

Use the DCOUNT function to count cells in a range between x and y

Use the DCOUNTA function to count all cells beginning with the same character

Use the DGET function to search for a product by number

Use the DMAX function to find the most expensive product in a category

Use the DMIN function to find the least expensive product in a category

Use the DMIN function to find the oldest person on a team

Use the DSUM function to sum sales for a period

Use the DSUM function to sum all prices in a category that are above a particular level

Use the DAVERAGE function to determine the average price in a category

Chapter 9: Lookup and Reference Functions

Use the ADDRESS, MATCH, and MAX functions to find the position of the largest number

Use the ADDRESS, MATCH, and MIN functions to find the position of the smallest number

Use the ADDRESS, MATCH, and TODAY functions to sum sales up to today’s date

Use the XMATCH function to find who sold cookie boxes at or near the goal

Use the VLOOKUP function to look up and extract data from a database

Use the XLOOKUP function to return a row of information

Use the VLOOKUP function to compare offers from different suppliers

Use the HLOOKUP function to determine sales and costs for a team

Use the HLOOKUP function to determine sales for a particular day

Use the HLOOKUP function to generate a list for a specific month

Use the LOOKUP function to get the directory of a store

Use the LOOKUP function to get the indicator for the current temperature

Use the INDEX function to search for data in a sorted list

Use the INDIRECT function to play “Battleship”

Use the INDIRECT function to copy cell values from different worksheets

Use the INDEX function to determine the last number in a column

Use the INDEX and COUNTA functions to determine the last number in a row

Use the OFFSET function to sum sales for a specified period

Use the OFFSET function to consolidate sales for a day

Use the OFFSET function to filter every other column

Use the OFFSET function to filter every other row

Use the HYPERLINK function to jump directly to a cell inside the current worksheet

Use the HYPERLINK function to link to the Internet

Use the CHOOSE function to lookup values

Use the ROW and COLUMN functions to determine how many rows and columns are in a worksheet

Use the FORMULATEXT function to display a formula in a cell

Use the TRANSPOSE function to rearrange information on a worksheet

Use the FILTER function to filter data from a range

Use the SORT function to sort an existing range into another range

Use the SORTBY function to sort an existing range into another range

Use the UNIQUE function to return unique rows of data into another range

Chapter 10: Conditional Formatting with Formulas

Use the WEEKDAY function to determine weekends and shade them

Use the TODAY function to show completed sales

Use conditional formats to indicate unavailable products

Use the TODAY function to shade a specific column

Use the WEEKNUM and MOD functions to shade every other Tuesday

Use the MOD and ROW functions to shade every third row

Use the MOD and COLUMN functions to shade every third column

Use the MAX function to find the largest value

Use the LARGE function to find the three largest values

Use the MIN function to find the month with the worst performance

Use the MIN function to search for the lowest nonzero number

Use the COUNTIF function to mark duplicate input automatically

Use the COUNTIF function to check whether a number exists in a range

Use conditional formatting to control font styles in a specific range

Use a user-defined function to detect cells with formulas

Use a user-defined function to detect cells with valid numeric values

Use the EXACT function to perform a case-sensitive search

Use the SUBSTITUTE function to search for text

Use conditional formatting to shade project steps with missed deadlines

Use conditional formatting to create a Gantt chart in Excel

Use the OR function to indicate differences higher than 5% and lower than −5%

Use the CELL function to detect unlocked cells

Use the COUNTIF function to shade matching numbers in column B

Use the ISERROR function to mark errors

Use the DATEDIF function to determine all friends younger than 30

Use the MONTH and TODAY functions to find birthdays in the current month

Use conditional formatting to border summed rows

Use the LEFT function in a product search

Use the AND function to detect empty rows in a range

Use the COUNTIFS function to determine value based on multiple filters

Chapter 11: Working with Dynamic Array Formulas

Use the ADDRESS, MAX, and ROW functions to determine the last cell used

Use the INDEX, MAX, ISNUMBER, and ROW functions to find the last number in a column

Use the INDEX, MAX, ISNUMBER, and COLUMN functions to find the last number in a row

Use the MAX, IF, and COLUMN functions to determine the last column used in a range

Use the MIN and IF functions to find the lowest nonzero value in a range

Use the AVERAGE and IF functions to calculate the average of a range, taking zero values into consideration

Use the SUM and IF functions to sum values with several criteria

Use the INDEX and MATCH functions to search for a value that matches two criteria

Use the SUM function to count values that match two criteria

Use the SUM function to count values that match several criteria

Use the SUM function to count numbers from x to y

Use the SUM and DATEVALUE functions to count today’s sales of a specific product

Use the SUM function to count today’s sales of a specific product

Use the SUM, OFFSET, MAX, IF, and ROW functions to sum the last row in a dynamic list

Use the SUM, MID, and COLUMN functions to count specific characters in a range

Use the SUM, LEN, and SUBSTITUTE functions to count the occurrences of a specific word in a range

Use the SUM and LEN functions to count all digits in a range

Use the MAX, INDIRECT, and COUNT functions to determine the largest gain/loss of shares

Use the SUM and COUNTIF functions to count unique records in a list

Use the UNIQUE function to list unique records in a list

Use the AVERAGE and LARGE functions to calculate the average of the x largest numbers

Use the TRANSPOSE and OR functions to determine duplicate numbers in a list

Use the MID, MATCH, and ROW functions to extract numeric values from text

Use the MAX and COUNTIF functions to determine whether all numbers are unique

Use the TRANSPOSE function to copy a range from vertical to horizontal or vice versa

Use the FREQUENCY function to calculate the number of products sold for each group

Use the FILTER function to determine wins for a specific division

Use the SORT function to list an existing table of data by a certain column

Use the SORTBY function to list an existing table of data by a certain column

Use the RANDARRAY function to return a list of random numbers across any number of rows and columns

Use the SEQUENCE function to return a list of a sequence of numbers across any number of rows and columns

Chapter 12: Special Solutions with Formulas

Use the COUNTIF function to prevent duplicate input through validation

Use the EXACT function to allow only uppercase characters

Use data validation to allow data input by a specific criterion

Use data validation to limit data to a specific list

Use controls with formulas

Use Goal Seek as a powerful analysis tool

Use a custom function to shade all cells containing formulas

Use a custom function to change all formulas in cells to values

Use a custom function to document and display all cells containing formulas

Use a custom function to delete external links in a worksheet

Use a custom function to delete external links in a workbook

Use a custom function to enter all formulas into an additional worksheet

Chapter 13: User-Defined Functions

Use a user-defined function to copy the name of a worksheet into a cell

Use a user-defined function to copy the name of a workbook into a cell

Use a user-defined function to get the path of a workbook

Use a user-defined function to get the full name of a workbook

Use a user-defined function to determine the current user of Windows or Excel

Use a user-defined function to display formulas of a specific cell

Use a user-defined function to check whether a cell contains a formula

Use a user-defined function to check whether a cell contains data validation

Use a user-defined function to find all comments

Use a user-defined function to sum all shaded cells

Use a user-defined function to sum all cells with a colored font

Use a user-defined function to delete leading zeros for specified cells

Use a user-defined function to delete all letters in specified cells

Use a user-defined function to delete all numbers in specified cells

Use a user-defined function to determine the position of the first number

Use a user-defined function to calculate the cross sum of a cell

Use a user-defined function to sum each cell’s cross sum in a range

Use a user-defined function to check whether a worksheet is empty

Use a user-defined function to check whether a worksheet is protected

Use a user-defined function to create your own AutoText

Chapter 14: Examples

Calculate average fuel consumption

Calculate net and corresponding gross prices

Determine the economic value of a product

Calculate the final price of a product, taking into account rebates and price reductions

Search for data that meets specific criteria

Separate cities from zip codes

Eliminate specific characters

Combine text, dates, and timestamps

Determine the last day of a month

Determine the number of available workdays

Determine a person’s exact age

Determine the number of values in a specific range

Determine the weekly sales for each department

Round a value to the nearest 5 cents

Determining inventory value

Determine the top salesperson for a month

Determine the three highest values in a list

Determine amount to invest

Determine how many items are in various categories

Find a specific value in a complex list

Dynamically show costs and sales per day

Extract every fourth value from a list

Display names in a list or in an array

Filter for certain job positions

Display the mile time for a runner

Create a list of random telephone numbers

Create a list of sequential Friday dates

Sort a list of names

Sort a list of names to include all their data

List unique data based on names and city

Retrieve student’s math and history scores

Retrieve student’s total score

Chapter 15: Other Features

Insert Icons

Draw Freestyle

Smart Lookup

Share Files

Flash Fill

Quick Analysis Tool

Map Chart

Funnel Chart

Data Types – Stocks

Data Types – Geography

View workbook objects using the navigation pane

Review workbook statistics

Smooth scrolling when cells cross over viewable pages

Unhiding multiple worksheets at once

Resizing the conditional formatting window

Duplicating a conditional formatting rule

Chapter 16: Data Analytics Using Excel

Activate “Analysis ToolPak”

Display a Histogram to view the frequency of Sales

Display many statistics describing a data set

Reveal how numbers correlate to each other

Smooth a natural progression of numbers

Determine a moving average for a data set

Create a list of random numbers

Rank a list of clients obtained in a month

Determine an accurate house price

Select four random customers to receive a prize

Chapter 17: Shortcut Keys

Go to cells or highlight cell ranges

Other Common Shortcuts

Appendix: Excel Interface Guide

Anatomy of Excel

Microsoft 365 vs 2021

Microsoft Excel 2021

Microsoft Excel 2021 for Macintosh

Microsoft Excel Web App

Index

ACKNOWLEDGMENTS

I appreciate the patience and understanding my wonderful wife and adorable daughter inured while I diverted time from them to express my knowledge of Excel to you.

I would also like to thank the dedicated individuals at Mercury Learning and Information who labored in producing this book for their indefatigable work and generous commitment to quality, informational books.

INTRODUCTION

Microsoft Excel is the well-known standard spreadsheet application that allows you to easily perform calculations and recalculations of data by using numerous built-in functions and formulas. Although you may be familiar with simple functions such as SUM, this is just one of the many Excel functions and formulas that can help you simplify the process of entering calculations. Because there are so many other useful and versatile functions and formulas inside Excel that most users have yet to discover, this book was written to help readers uncover and use its wide range of tools.

For each function or formula, we started with a simple task that can be solved with Excel in an efficient way. We added tips and tricks and additional features as well to provide deeper knowledge and orientation. After you have stepped through all the lessons, you will have a great toolbox to assist you with your projects and make many everyday workbook tasks much easier. The most notable changes from Excel 2019 to 2021 (and Microsoft 365) are additional functions. We added 11 functions to this edition – there are more but only included the more common functions. Some of these functions are array formulas that can provide multiple calculations dynamically displayed in multiple rows and columns. In other words, one formula in one cell can provide a table of information. This edition also includes previous features such as Stock and Geography data types along with widely used formulas that have been around since 2016 such as IFERROR, COUNTIFS, CHOOSE, and COLUMN. Some functions that are not available in 2021 but are available with a Microsoft 365 subscription are noted throughout the book.

The content of the book is as follows:

Chapter 1 describes practical tasks that can be solved by using formulas.

In Chapter 2, you learn the usage of logical functions that are often used in combination with other functions.

Chapter 3 shows how text functions are used. You will often need these functions when working with text in tables or if the text needs to be changed or adapted, especially when it is imported into Excel from other applications.

In Chapter 4, you learn about the date and time functions in Excel. Times and dates are automatically converted inside Excel to the number format, which makes it easier to perform calculations.

With Chapter 5, you delve into the secrets of working with statistics in Excel.

Chapter 6 describes the most commonly used functions for mathematics and trigonometry, along with easy-to-follow tasks. The most common function here is the SUM function, with which you may already be familiar. However, you may be surprised about the additional possibilities shown.

If you want to learn more about functions for financial mathematics, study Chapter 7. Here you will find examples of how to calculate depreciation of an asset and how long it takes to pay back a loan using different interest rates.

With Chapter 8, you get into the secrets of database functions. There are a variety of functions explained that can be used for evaluation of data, especially when using different criteria.

Chapter 9 is about lookup and reference functions inside Excel. With these functions, you can address data in various ranges and look up values in a reference.

Chapter 10 goes into the depth of conditional formatting. Even though this feature has been available since Excel 97, there are new features that allow you to express information without programming.

Chapter 11 introduces dynamic array formulas. The way that Excel now manages arrays is vastly different than how it has managed them in previous versions. There is less work on your part compared with legacy array formulas requiring ‘Ctrl+Shift+Enter’ (CSE). With these you learn how to perform multiple calculations and then return either a single result or multiple results.

Chapter 12 shows special solutions with formulas, such as creating a function to color all cells containing formulas inside an Excel spreadsheet.

Chapter 13 goes even deeper into user-defined functions with examples that use Visual Basic for Applications (VBA) inside Excel. This chapter will show you how to solve problems even when Excel cannot calculate an answer.

With Chapter 14, we present some examples of tasks that combine several functions shown in the previous chapters. Use these to get more experience. Read the description of the task first and try to determine the functions that are needed to get the desired result. Compare your solution to the one shown beneath the task.

Chapter 15 details a few features that will enhance how you develop, test, and present the Excel products you create for efficiency.

Chapter 16 provides functions pertaining to data analytics. This chapter displays rudimentary examples of how to report on and select from data.

Chapter 17 guides you through some of the more common shortcut keys. These save time and allow you to get around Excel with less keystrokes.

Appendix A provides an overview of the current versions of Excel. This includes Excel 2021 for Windows, the primary version used for the images and examples in the text. The interface for Macintosh is also covered; the appearance of this version is different, but it can perform the same calculations. The Excel Web App available as part of the Microsoft OneDrive and Microsoft 365 is also demonstrated in this appendix; it has limited functionality compared with the complete installations, but it still has significant capacity for performing calculations.

Have fun reading the book and exploring the many useful functions, formulas, and features you will discover here.

CHAPTER 1

FORMULAS IN EXCEL

CALCULATE PRODUCTION PER HOUR

Data for some employees is recorded in a worksheet. They work a varied number of hours each day to produce clocks. By calculating the number of pieces each employee produces per hour, it can be determined who is the most productive employee.

 To see who the most productive employee is:

1. In a worksheet, enter your own data or the data shown in Figure 1–1.

2. Select cells D2:D7.

3. Enter the following formula: C2/(B2∗24).

4. Press <Ctrl+Enter> to fill the selected cell range with the current entry.

5. From the toolbar select Home and go to Number.

6. Click the dropdown arrow and select Format Cells.

7. Select the Number tab and then select Number from the Category list.

8. Set Decimal places to 2.

9. Click OK.

Beckham is the most productive. He produces an average of just below 22 clocks per hour.

FIGURE 1–1

CALCULATE THE AGE OF A PERSON IN DAYS

A worksheet lists the names of friends in column A and their birth dates in column B. To calculate the number of days each person has been alive, enter the current date in cell B1 and perform the following steps:

 To calculate the age of a person in days:

1. In a worksheet, enter your own data or the data shown in Figure 1–2.

2. Select cells C5:C9.

3. Enter the following formula: $B$1-B5.

4. Press <Ctrl+Enter>.

5. From the toolbar select Home and go to Number.

6. Click the dropdown arrow and select Format Cells.

7. Select the Number tab and then select General from the Category list.

8. Click OK.

FIGURE 1–2

NOTE

The formula must have an absolute reference to cell B1, which is available by going to the formula bar, highlighting the cell reference, and pressing F4 until the appropriate reference appears or you can enter a “$” before the “B” and the “1.” This tells excel not to change either the “B” or the “1” when copying the formula to another cell.

CALCULATE A PRICE REDUCTION

All prices in a price list need to be reduced by a certain percentage. The amount of the price reduction is 15%; this is entered in cell C1.

 To reduce all prices by a certain percentage:

1. In a worksheet, enter your own data or the data shown in Figure 1–3.

2. Select cell C1 and type −15%.

3. Select cells C4:C8.

4. Enter the following formula: B4+(B4∗$C$1).

5. Press <Ctrl+Enter>.

FIGURE 1–3

NOTE

Please note that the formula must have an absolute reference to cell C1. Also, columns B and C are formatted with the Currency style, which is available by clicking on the $ button in the Home ribbon toolbar.

CONVERT CURRENCY

In a worksheet, currency need to be converted from dollars (column B) to euros (column C). The rate of exchange from dollars to euros is placed in cell C1; here we use 0.747.

 To convert currency:

1. In a worksheet, enter your own data or the data shown in Figure 1–4.

2. Select cells C4:C8.

3. Enter the following formula: B4∗$C$1.

4. Press <Ctrl+Enter>.

5. Press <Ctrl+1> to show the dialog Format Cells.

6. Select the Number tab and then select Currency from the Category list.

7. Choose the required € Euro format.

8. Click OK.

FIGURE 1–4

NOTE

To convert euros back to dollars, use the following formula: C4/$C$1.

CONVERT FROM HOURS TO MINUTES

As a task, time in a timesheet needs to be converted from hours to minutes.

 To convert time to minutes:

1. In a worksheet, enter your own data or the data shown in Figure 1–5.

2. Select cells B4:B8.

3. Enter the following formula: A4∗24∗60.

4. Press <Ctrl+Enter>.

5. Format cells B4:B8 as general by pressing <Ctrl+1> and then selecting the Number tab and then General from the Category list.

6. Click OK.

FIGURE 1–5

NOTE

To convert a minutes format to an hours-and-minutes format, use the formula B4/24/60. Remember to format the cells with a time format, as shown in cell C4 in Figure 1–5.

DETERMINE FUEL CONSUMPTION

In a worksheet, fuel consumption data is recorded. Each time you refill your gas tank, record the following data: date, miles traveled, and gallons purchased. Then reset the mileage counter. To calculate the fuel consumption of your vehicle, perform the following steps:

 To determine fuel consumption:

1. In a worksheet, enter your own data or the data shown in Figure 1–6.

2. Select cells D5:D10.

3. Enter the following formula: B5/C5.

4. Press <Ctrl+Enter>.

FIGURE 1–6

CALCULATE YOUR IDEAL AND RECOMMENDED WEIGHTS

THE QUICK CALENDAR

To create a simple calendar, use the Fill command in combination with a formula.

 To create a quick calendar:

1. Select cell A1 and type the following formula: TODAY().

2. Select cell A2 and type the following formula: A1+1.

3. Select cells B1:G1.

4. From the Edit menu, select Fill and Right.

5. In cell A2, type A1.

6. Drag the bottom-right corner of cell A2 with the mouse cursor rightward through cell F2.

7. Press <Ctrl+1> to show the dialog Format Cells.

8. Select Custom under Category.

9. Enter the custom format ddd and press OK.

FIGURE 1–8

DESIGN YOUR OWN TO-DO LIST

Generate your own to-do list by entering the hours of the day in column A and making space for your daily tasks in column B.

 To generate your own to-do list:

1. Select cell B1 and type TODAY().

2. Select cell A3 and type 7:00 a.m.

3. Select cell A4 and type the following formula in the Formula Bar: A3+(1/24).

4. Select cells A4:A13.

5. Go to the Editing group and choose the boxed downward arrow.

6. Click on Down.

FIGURE 1–9

NOTE

To get increments of half an hour, use the formula A3+(1/48). To display the time in column A as shown in Figure 1–9, select Cells from the Home tab, click the Number group, select Time from the Category list, select 1:30 p.m., and click OK.

INCREMENT ROW NUMBERS

Standard row numbering in Excel is often used, but you can also create your own numbering system in a table, such as incrementing by 10 as described below.

 To increment row numbers by 10:

1. Select cell A2 and type 0.

2. Select cell A3 and type the following formula: A2+10.

3. Select cells A3:A12.

4. Select Editing from the ribbon, choose the downward button, and select Down.

FIGURE 1–10

NOTE

If the value of cell A2 is changed, the values in all the other cells change, too.

CONVERT NEGATIVE VALUES TO POSITIVE

A worksheet contains negative values. To convert all the negative values to positive values, perform the following steps.

 To convert negative values to positive values:

1. Enter a series of negative values in cells B1:B10.

2. Select cell C1 and type -1.

3. Copy this cell.

4. Select cells B1:B10.

5. In the Home tab, in the Clipboard group, click Paste, and then click Paste Special.

6. In the Paste Special dialog box, under Paste, select Multiply.

7. Click OK.

FIGURE 1–11

NOTE

After this, C1 can be cleared.

CALCULATE SALES TAXES

In this exercise, tax on an item needs to be calculated. We can also find the original price, given the tax rate and the final price.

 To calculate the price with tax:

1. Select cell A2 and type 8%.

2. Select cell B2 and type 120.

3. Select cell C2 and type the following formula: B2+(B2∗A2).

 To calculate the original price:

1. Select cell A4 and type 8%.

2. Select cell C4 and type 129.60.

3. Select cell B4 and type the following formula: C4/(1+A4).

FIGURE 1–12

COMBINE TEXT AND NUMBERS

In this example, we want to combine text and numbers. Use the & operator to accomplish this.

 To combine cells containing text and numbers:

1. Select cell B1 and type computers.

2. Select cell B2 and type 5.

3. Select cell B4 and type the following formula in the Formula Bar: "You ordered " & B2 & " " & B1 & " today!".

FIGURE 1–13

NOTE

Each cell reference must be placed between & operators, and additional text must be surrounded by quotation marks.

COMBINE TEXT AND DATE

Excel has a problem combining cells that contain text and dates. This results in the date’s showing up as a number value, because Excel has lost the format. To get the desired result, use the following workaround.

 To combine text and date:

1. Select cell A1 and type actual status.

2. Select cell D1 and type the following formula: TODAY().

3. Select cell A3 and type the following formula: A1& " " &TEXT(D1,"MM/DD/YYYY").

FIGURE 1–14

COMBINE TEXT AND TIME

This example shows how to combine text and time successfully.

 To combine text and time:

1. Select cell A1 and type Shutdown.

2. Select cell D1 and press <Ctrl+Shift+:> to insert the current time.

3. Select cell A3 and type the following formula: " Today " & A1 & " at " & TEXT(D1,"hh:mm").

FIGURE 1–15

GENERATE A SPECIAL RANKING LIST

You can use Excel to generate a special ranking list. Let us say a worksheet contains a few values, some of which are repeated. To rank the list in a particular order, follow these instructions.

 To rank a list in a particular order:

1. Select cell A1 and type Value.

2. In cells A2:A12 enter a selection of values from 10 to 20.

3. Select cell A2.

4. In the Home tab, click on the AZ icon in the Editing group.

5. Select Sort Smallest to Largest.

6. Select cell B1 and type Rank.

7. Select cell B2 and type 1.

8. Select cells B3:B12 and type the following formula: B2+(A2<A3).

9. Press <Ctrl+Enter>.

FIGURE 1–16

DETERMINE AVERAGE OUTPUT

In a worksheet, the start and end production dates of a machine’s operation are given, as well as its output during this period. How do you calculate the average daily production?

 To calculate the average daily production:

1. Select cell B1 and type 10/18/2004.

2. Select cell B2 and type 11/13/2002.

3. Type 55900 in cell B3.

4. Select cell B5 and type the following formula: B3/(B1-B2).

FIGURE 1–17

DETERMINE STOCK GAINS AND LOSSES

Imagine your stocks have fallen 11.5% in value in one day. What is the percentage of gain that will be needed the next day to compensate for the loss?

 To determine the gain/loss of a stock:

1. Select cell C2 and type 1000.

2. Select cell B3 and type 11.50%.

FIGURE 1–18

3. Select cell C3 and type the following formula: C2-(C2∗$B$3).

4. Select cell B4 and type the following formula: B3/(1-B3).

5. Select cell C4 and type the following formula: C3+(C3∗$B$4).

6. Be sure to format column C as Currency.

EVALUATE PROFITABILITY

You have some products for sale, and you want to know which one is the most profitable. Use conditional formatting for this purpose.

 To determine the most profitable product:

1. In a new worksheet, type the cost of each product in column B and the corresponding price in column C.

2. Select cells D2:D6 and type the following formula: 1-(B2/C2).

3. Press <Ctrl+Enter>.

4. In the Home tab, in the Styles group, click the arrow next to Conditional Formatting.

5. Select New Rule and select the options shown to highlight the top 1 value.

6. Click Format, select the Fill tab, choose a color, and click OK.

FIGURE 1–19

FIGURE 1–20

NOTE

Product pr04 has the greatest profit margin as calculated in column D. The conditional formatting highlights the cell automatically.

DETERMINE PERCENTAGE OF COMPLETION

To manage a project, it is necessary to determine the percentage of completion. This can be accomplished with the following calculation.

 To calculate percentage of completion:

1. In a worksheet, enter data in columns A, B, and D as shown in Figure 1–21.

2. Select cell E2 and type B2+B3.

3. Select cell E3 and enter the target value of 200.

4. In cell E5, type the formula E3-E2 to get the difference between the target and the number already produced.

5. Calculate the percentage of missing products in cell E6 with this formula: 1-E2/E3.

6. Select cell E8 and calculate the percentage of production by using this formula: 100%-E6.

FIGURE 1–21

CONVERT MILES PER HOUR TO KILOMETERS PER HOUR

A worksheet contains speed in miles per hour. To convert the data to kilometers per hour, use the following calculation.

 To convert miles per hour to kilometers per hour:

1. In a worksheet, enter the data shown or use data of your own creation in Figure 1–22.

2. Select cell D1 and enter the conversion value 0.621371.

3. Select cells B2:B8 and type the following formula: A2/$D$1.

4. Press <Ctrl+Enter>.

FIGURE 1–22

NOTE

To convert the other way around, from kilometers per hour to miles per hour, use the formula B2∗$D$1.

CONVERT FEET PER MINUTE TO METERS PER SECOND

A worksheet contains speed data. To convert feet per minute to meters per second, use the calculation described as follows.

 To convert feet per minute to meters per second:

1. In a worksheet, enter the data shown in Figure 1–22, or use your own data.

2. Select cell D1 and enter the conversion value 196.858144.

3. Select cells B2:B10 and type the following formula: A2/$D$1.

4. Press <Ctrl+Enter>.