Advertisement

Your Excel formulas cheat sheet: 15 tips for calculations and common tasks

Many of us fell in love with Excel as we delved into its deep and sophisticated formula features. Because there are multiple ways to get results, you can decide which method works best for you. For example, there are several ways to enter formulas and calculate numbers in Excel.

Five ways to enter formulas

1. Manually enter Excel formulas:
Long Lists: =SUM(B4:B13)
Short Lists: =SUM(B4,B5,B6,B7); =SUM(B4+B5+B6+B7). Or, place your cursor in the first empty cell at the bottom of your list (or any cell, really) and press the plus sign, then click B4; press the plus sign again and click B5; and so on to the end; then press Enter. Excel adds/totals this list you just “pointed to:” =+B4+B5+B6+B7.

2. Click the Insert Function button
Use the Insert Function button under the Formulas tab to select a function from Excel’s menu list:
=COUNT(B4:B13) Counts the numbers in a range (ignores blank/empty cells).
=COUNTA(B3:B13) Counts all characters in a range (also ignores blank/empty cells).
3. Select a function from a group (Formulas tab)
Narrow your search a bit and choose a formula subset for Financial, Logical, or Date/Time, for example.
=TODAY() Inserts today’s date.
4. The Recently Used button
Click the Recently Used button to show functions you've used recently. It's a welcome timesaver, especially when wrestling with an extra-hairy spreadsheet.
=AVERAGE(B4:B13) adds the list, divides by the number of values, then provides the average.
5. Auto functions under the AutoSum button
Auto functions are my editor's personal favourite, because they're so fast. Select a cell range and a function, and your result appears with no muss or fuss. Here are a few examples:
=MAX(B4:B13) returns the highest value in the list.
=MIN(B4:B13) returns the lowest value in the list.
JD SARTAIN
Use the AutoSum button to calculate basic formulas such as SUM, AVERAGE, COUNT, etc.
Note: If your cursor is positioned in the empty cell just below your range of numbers, Excel determines that this is the range you want to calculate and automatically highlights the range, or enters the range cell addresses in the corresponding dialog boxes.
Bonus tip: With basic formulas, the AutoSum button is the top choice. It’s faster to click AutoSum>SUM (notice that Excel highlights the range for you) and press Enter.
Another bonus tip: The quickest way to add/total a list of numbers is to position your cursor at the bottom of the list and press Alt+ = (press the Alt key and hold, press the equal sign, release both keys), then press Enter. Excel highlights the range and totals the column.
Five handy formulas for common tasks
The five formulas below may have somewhat inscrutable names, but their functions save time and data entry on a daily basis.
Note: Some formulas require you to input the single cell or range address of the values or text you want calculated. When Excel displays the various cell/range dialog boxes, you can either manually enter the cell/range address, or cursor and point to it. Pointing means you click the field box first, then click the corresponding cell over in the worksheet. Repeat this process for formulas that calculate a range of cells (e.g., beginning date, ending date, etc.)
1. =DAYS
This is a handy formula to calculate the number of days between two dates (so there’s no worries about how many days are in each month of the range).
Example: End Date October 12, 2015 minus Start Date March 31, 2015 = 195 days
Formula: =DAYS(A30,A29)
2. =NETWORKDAYS
This similar formula calculates the number of workdays (i.e., a five-day workweek) within a specified timeframe. It also includes an option to subtract the holidays from the total, but this must be entered as a range of dates.
Example: Start Date March 31, 2015 minus End Date October 12, 2015 = 140 days
Formula: =NETWORKDAYS(A33,A34)
3. =TRIM
TRIM is a lifesaver if you’re always importing or pasting text into Excel (such as from a database, website, word processing software, or other text-based program). So often, the imported text is filled with extra spaces scattered throughout the list. TRIM removes the extra spaces in seconds. In this case, just enter the formula once, then copy it down to the end of the list.
Example: =TRIM plus the cell address inside parenthesis.
Formula: =TRIM(A39)

4. =CONCATENATE
This is another keeper if you import a lot of data into Excel. This formula joins (or merges) the contents of two or more fields/cells into one. For example: In databases; dates, times, phone numbers, and other multiple data records are often entered in separate fields, which is a real inconvenience. To add spaces between words or punctuation between fields, just surround this data with quotation marks.
Example: =CONCATENATE plus (month,”space”,day,”comma space”,year) where month, day, and year are cell addresses and the info inside the quotation marks is actually a space and a comma.
Formula: For dates enter: =CONCATENATE(E33,” “,F33,”, “,G33)
Formula: For phone numbers enter: =CONCATENATE(E37,”-“,F37,”-“,G37)
5. =DATEVALUE
DATEVALUE converts the above formula into an Excel date, which is necessary if you plan to use this date for calculations. This one is easy: Select DATEVALUE from the formula list. Click the Date_Text field in the dialog box, click the corresponding cell on the spreadsheet, then click OK, and copy down. The results are Excel serial numbers, so you must choose Format>Format Cells>Number>Date, and then select a format from the list.
Formula: =DATEVALUE(H33)
Three more formula tips
As you work with formulas more, keep these bonus tips in mind to avoid confusion:
Tip 1: You don’t need another formula to convert formulas to text or numbers. Just copy the range of formulas and then paste as Special>Values. Why bother to convert the formulas to values? Because you can’t move or manipulate the data until it’s converted. Those cells may look like phone numbers, but they’re actually formulas, which cannot be edited as numbers or text.
Tip 2: If you use Copy and Paste>Special>Values for dates, the result will be text and cannot be converted to a real date. Dates require the DATEVALUE formula to function as actual dates.

Tip 3: Formulas are always displayed in uppercase; however, if you type them in lowercase, Excel converts them to uppercase. Also notice there are no spaces in formulas. If your formula fails, check for spaces and remove them.

PDF FILE TO YOUR EMAIL IMMEDIATELY PURCHASE NOTES & PAPER SOLUTION. @ Rs. 50/- each (GST extra)

SUBJECTS

HINDI ENTIRE PAPER SOLUTION

MARATHI PAPER SOLUTION

SSC MATHS I PAPER SOLUTION

SSC MATHS II PAPER SOLUTION

SSC SCIENCE I PAPER SOLUTION

SSC SCIENCE II PAPER SOLUTION

SSC ENGLISH PAPER SOLUTION

SSC & HSC ENGLISH WRITING SKILL

HSC ACCOUNTS NOTES

HSC OCM NOTES

HSC ECONOMICS NOTES

HSC SECRETARIAL PRACTICE NOTES

2019 Board Paper Solution

HSC ENGLISH SET A 2019 21st February, 2019

HSC ENGLISH SET B 2019 21st February, 2019

HSC ENGLISH SET C 2019 21st February, 2019

HSC ENGLISH SET D 2019 21st February, 2019

SECRETARIAL PRACTICE (S.P) 2019 25th February, 2019

HSC XII PHYSICS 2019 25th February, 2019

CHEMISTRY XII HSC SOLUTION 27th, February, 2019

OCM PAPER SOLUTION 2019 27th, February, 2019

HSC MATHS PAPER SOLUTION COMMERCE, 2nd March, 2019

HSC MATHS PAPER SOLUTION SCIENCE 2nd, March, 2019

SSC ENGLISH STD 10 5TH MARCH, 2019.

HSC XII ACCOUNTS 2019 6th March, 2019

HSC XII BIOLOGY 2019 6TH March, 2019

HSC XII ECONOMICS 9Th March 2019

SSC Maths I March 2019 Solution 10th Standard11th, March, 2019

SSC MATHS II MARCH 2019 SOLUTION 10TH STD.13th March, 2019

SSC SCIENCE I MARCH 2019 SOLUTION 10TH STD. 15th March, 2019.

SSC SCIENCE II MARCH 2019 SOLUTION 10TH STD. 18th March, 2019.

SSC SOCIAL SCIENCE I MARCH 2019 SOLUTION20th March, 2019

SSC SOCIAL SCIENCE II MARCH 2019 SOLUTION, 22nd March, 2019

XII CBSE - BOARD - MARCH - 2019 ENGLISH - QP + SOLUTIONS, 2nd March, 2019

HSC Maharashtra Board Papers 2020

(Std 12th English Medium)

HSC ECONOMICS MARCH 2020

HSC OCM MARCH 2020

HSC ACCOUNTS MARCH 2020

HSC S.P. MARCH 2020

HSC ENGLISH MARCH 2020

HSC HINDI MARCH 2020

HSC MARATHI MARCH 2020

HSC MATHS MARCH 2020


SSC Maharashtra Board Papers 2020

(Std 10th English Medium)

English MARCH 2020

HindI MARCH 2020

Hindi (Composite) MARCH 2020

Marathi MARCH 2020

Mathematics (Paper 1) MARCH 2020

Mathematics (Paper 2) MARCH 2020

Sanskrit MARCH 2020

Sanskrit (Composite) MARCH 2020

Science (Paper 1) MARCH 2020

Science (Paper 2)

Geography Model Set 1 2020-2021


MUST REMEMBER THINGS on the day of Exam

Are you prepared? for English Grammar in Board Exam.

Paper Presentation In Board Exam

How to Score Good Marks in SSC Board Exams

Tips To Score More Than 90% Marks In 12th Board Exam

How to write English exams?

How to prepare for board exam when less time is left

How to memorise what you learn for board exam

No. 1 Simple Hack, you can try out, in preparing for Board Exam

How to Study for CBSE Class 10 Board Exams Subject Wise Tips?

JEE Main 2020 Registration Process – Exam Pattern & Important Dates


NEET UG 2020 Registration Process Exam Pattern & Important Dates

How can One Prepare for two Competitive Exams at the same time?

8 Proven Tips to Handle Anxiety before Exams!

BUY FROM PLAY STORE

DOWNLOAD OUR APP

HOW TO PURCHASE OUR NOTES?

S.P. Important Questions For Board Exam 2022

O.C.M. Important Questions for Board Exam. 2022

Economics Important Questions for Board Exam 2022

Chemistry Important Question Bank for board exam 2022

Physics – Section I- Important Question Bank for Maharashtra Board HSC Examination

Physics – Section II – Science- Important Question Bank for Maharashtra Board HSC 2022 Examination

Important-formula



THANKS