Excel Advanced Formulas Pdf

It’s easy to use Excel for many of your day-to-day number-crunching tasks, like determining your business’s average sale, computing classroom grades, or forecasting college expenses. Use this handy Cheat Sheet to discover great functions and tips to help you get the most out of Excel.

Page 4 Advanced Excel Tips & Tricks Figure 3 Figure 4 Note: In this example, a new Column C was inserted prior to beginning the steps to split the cell data. The result of using the Text to Columns command is that the split data is saved into cells to the right of the original cell. Page 9 Advanced Excel Tips & Tricks Features of an Excel List Every Excel Table has several prominent features, which can be used to manage large lists of data. AutoFilter: You can use AutoFilter to filter or sort your list. Ledger lines. Actually, these are Table Styles to help make table data easier to see. Nishtha August 27, 2014 at 12:31 AM. I recently came accross your blog and have been reading along. I thought I would leave my first comment. I dont know what to say except that I have enjoyed reading.

Excel Functions You’re Likely to Use

Some Excel functions apply to specific subject areas, but others are general and apply to all needs. The following list shows an array of Excel functions used by one and all. Check here for a quickie reference to the purpose of each Excel function.

Pdf
Excel FunctionDescription
SUMCalculates the sum of a group of values
AVERAGECalculates the mean of a group of values
COUNTCounts the number of cells in a range that contains
numbers
INTRemoves the decimal portion of a number, leaving just the
integer portion
ROUNDRounds a number to a specified number of decimal places or
digit positions
IFTests for a true or false condition and then returns one value
or another
NOWReturns the system date and time
TODAYReturns the system date, without the time
SUMIFCalculates a sum from a group of values, but just of values
that are included because a condition is met
COUNTIFCounts the number of cells in a range that match a
criteria

Excel Text Functions You’ll Find Helpful

Excel’s text functions are very helpful when you’re working with names, addresses, customer lists, or any other text-based data. Here is list of Excel functions associated with text, along with a description of what each function does:

FunctionDescription
LEFTExtracts one or more characters from the left side of a text
string
RIGHTExtracts one or more characters from the right side of a text
string
MIDExtracts characters from the middle of a text string; you
specify which character position to start from and how many
characters to include
CONCATENATEAssembles two or more text strings into one
REPLACEReplaces part of a text string with other text
LOWERConverts a text string to all lowercase
UPPERConverts a text string to all uppercase
PROPERConverts a text string to proper case
LENReturns a text string’s length (number of
characters)

Excel Order of Operations to Keep in Mind

Mathematics dictates a protocol of how formulas are interpreted, and Excel follows that protocol. The following is the order in which mathematical operators and syntax are applied both in Excel and in general mathematics. You can remember this order by memorizing the mnemonic phrase, “Please excuse my dear aunt Sally.”

  1. Parentheses

  2. Exponents

  3. Multiplication and division

  4. Addition and subtraction

Microsoft Excel Advanced Formulas

Excel Cell References Worth Remembering

In Excel formulas, you can refer to other cells either relatively or absolutely. When you copy and paste a formula in Excel, how you create the references within the formula tells Excel what to change in the formula it pastes. The formula can either change the references relative to the cell where you’re pasting it (relative reference), or it can always refer to a specific cell.

Excel Advanced Formulas Pdf

You can also mix relative and absolute references so that, when you move or copy a formula, the row changes but the column does not, or vice versa.

Preceding the row and/or column designators with a dollar sign ($) specifies an absolute reference in Excel.

ExampleComment
=A1Complete relative reference
=$A1The column is absolute; the row is relative
=A$1The column is relative; the row is absolute
=$A$1Complete absolute reference

Excel Error Messages to Get to Know

Formulas For Excel Advanced Pdf

If you create a formula in Excel that contains an error or circular reference, Excel lets you know about it with an error message. A handful of errors can appear in a cell when a formula or function in Excel cannot be resolved. Knowing their meaning helps correct the problem.

Excel All Advanced Formulas Pdf

ErrorMeaning
#DIV/0!Trying to divide by 0
#N/A!A formula or a function inside a formula cannot find the
referenced data
#NAME?Text in the formula is not recognized
#NULL!A space was used in formulas that reference multiple ranges; a
comma separates range references
#NUM!A formula has invalid numeric data for the type of
operation
#REF!A reference is invalid
#VALUE!The wrong type of operand or function argument is used