✅ Excel formula ⭐️⭐️⭐️⭐️⭐

Table of Contents

5/5 - (1 vote)

Basic Excel Formulas Guide

Mastering the basic Excel formulas is critical for beginners to become highly proficient in financial analysis. Microsoft Excel is considered the industry standard piece of software in data analysis. Microsoft’s spreadsheet program also happens to be one of the most preferred software by investment bankers and financial analysts in data processing, financial modeling, and presentation. This guide will provide an overview and list of basic Excel functions.

Basic Terms in Excel

There are two basic ways to perform calculations in Excel: Formulas and Functions.

1. Formulas

In Excel, a formula is an expression that operates on values in a range of cells or a cell. For example, =A1+A2+A3, which finds the sum of the range of values from cell A1 to cell A3.

2. Functions

Functions are predefined formulas in Excel. They eliminate laborious manual entry of formulas while giving them human-friendly names. For example: =SUM(A1:A3). The function sums all the values from A1 to A3.

Five Time-saving Ways to Insert Data into Excel

When analyzing data, there are five common ways of inserting basic Excel formulas. Each strategy comes with its own advantages. Therefore, before diving further into the main formulas, we’ll clarify those methods, so you can create your preferred workflow earlier on.

1. Simple insertion: Typing a formula inside the cell

Typing a formula in a cell or the formula bar is the most straightforward method of inserting basic Excel formulas. The process usually starts by typing an equal sign, followed by the name of an Excel function.

Excel is quite intelligent in that when you start typing the name of the function, a pop-up function hint will show. It’s from this list you’ll select your preference. However, don’t press the Enter key. Instead, press the Tab key so that you can continue to insert other options. Otherwise, you may find yourself with an invalid name error, often as ‘#NAME?’. To fix it, just re-select the cell, and go to the formula bar to complete your function.

2. Using Insert Function Option from Formulas Tab

If you want full control of your functions insertion, using the Excel Insert Function dialogue box is all you ever need. To achieve this, go to the Formulas tab and select the first menu labeled Insert Function. The dialogue box will contain all the functions you need to complete your financial analysis.

3. Selecting a Formula from One of the Groups in Formula Tab

This option is for those who want to delve into their favorite functions quickly. To find this menu, navigate to the Formulas tab and select your preferred group. Click to show a sub-menu filled with a list of functions. From there, you can select your preference. However, if you find your preferred group is not on the tab, click on the More Functions option – it’s probably just hidden there.

4. Using AutoSum Option

For quick and everyday tasks, the AutoSum function is your go-to option. So, navigate to the Home tab, in the far-right corner, and click the AutoSum option. Then click the caret to show other hidden formulas. This option is also available in the Formulas tab first option after the Insert Function option.

5. Quick Insert: Use Recently Used Tabs

If you find re-typing your most recent formula a monotonous task, then use the Recently Used menu. It’s on the Formulas tab, a third menu option just next to AutoSum.

Seven Basic Excel Formulas For Your Workflow

Since you’re now able to insert your preferred formulas and function correctly, let’s check some fundamental Excel functions to get you started.

1. SUM

The SUM function is the first must-know formula in Excel. It usually aggregates values from a selection of columns or rows from your selected range.

=SUM(number1, [number2], …)

Example:

=SUM(B2:G2) – A simple selection that sums the values of a row.

=SUM(A2:A8) – A simple selection that sums the values of a column.

=SUM(A2:A7, A9, A12:A15) – A sophisticated collection that sums values from range A2 to A7, skips A8, adds A9, jumps A10 and A11, then finally adds from A12 to A15.

=SUM(A2:A8)/20 – Shows you can also turn your function into a formula.

2. AVERAGE

The AVERAGE function should remind you of simple averages of data such as the average number of shareholders in a given shareholding pool.

=AVERAGE(number1, [number2], …)

Example:

=AVERAGE(B2:B11) – Shows a simple average, also similar to (SUM(B2:B11)/10)

3. COUNT

The COUNT function counts all cells in a given range that contain only numeric values.

=COUNT(value1, [value2], …)

Example:

COUNT(A:A) – Counts all values that are numerical in A column. However, you must adjust the range inside the formula to count rows.

COUNT(A1:C1) – Now it can count rows.

4. COUNTA

Like the COUNT function, COUNTA counts all cells in a given rage. However, it counts all cells regardless of type. That is, unlike COUNT that only counts numerics, it also counts dates, times, strings, logical values, errors, empty string, or text.

=COUNTA(value1, [value2], …)

Example:

COUNTA(C2:C13) – Counts rows 2 to 13 in column C regardless of type. However, like COUNT, you can’t use the same formula to count rows. You must make an adjustment to the selection inside the brackets – for example, COUNTA(C2:H2) will count columns C to H

5. IF

The IF function is often used when you want to sort your data according to a given logic. The best part of the IF formula is that you can embed formulas and function in it.

=IF(logical_test, [value_if_true], [value_if_false])

Example:

=IF(C2<D3, ‘TRUE,’ ‘FALSE’) – Checks if the value at C3 is less than the value at D3. If the logic is true, let the cell value be TRUE, else, FALSE

=IF(SUM(C1:C10) > SUM(D1:D10), SUM(C1:C10), SUM(D1:D10)) – An example of a complex IF logic. First, it sums C1 to C10 and D1 to D10, then it compares the sum. If the sum of C1 to C10 is greater than the sum of D1 to D10, then it makes the value of a cell equal to the sum of C1 to C10. Otherwise, it makes it the SUM of C1 to C10.

6. TRIM

The TRIM function makes sure your functions do not return errors due to unruly spaces. It ensures that all empty spaces are eliminated. Unlike other functions that can operate on a range of cells, TRIM only operates on a single cell. Therefore, it comes with the downside of adding duplicated data in your spreadsheet.

=TRIM(text)

Example:

TRIM(A2) – Removes empty spaces in the value in cell A2.

7. MAX & MIN

The MAX and MIN functions help in finding the maximum number and the minimum number in a range of values.

=MIN(number1, [number2], …)

Example:

=MIN(B2:C11) – Finds the minimum number between column B from B2 and column C from C2 to row 11 in both columns B and C.

=MAX(number1, [number2], …)

Example:

=MAX(B2:C11) – Similarly, it finds the maximum number between column B from B2 and column C from C2 to row 11 in both columns B and C.

10 Advanced Excel Formulas You Must Know

1. INDEX MATCH

Formula: =INDEX(C3:E9,MATCH(B13,C3:C9,0),MATCH(B14,C3:E3,0))

This is an advanced alternative to the VLOOKUP or HLOOKUP formulas (which have several drawbacks and limitations).  INDEX MATCH is a powerful combination of Excel formulas that will take your financial analysis and financial modeling to the next level.

INDEX returns the value of a cell in a table based on the column and row number.

MATCH returns the position of a cell in a row or column.

Here is an example of the INDEX and MATCH formulas combined together.  In this example, we look up and return a person’s height based on their name.  Since name and height are both variables in the formula, we can change both of them!

2. IF combined with AND / OR

Formula: =IF(AND(C2>=C4,C2<=C5),C6,C7)

Anyone who’s spent a great deal of time doing various types of financial models knows that nested IF formulas can be a nightmare.  Combining IF with the AND or the OR function can be a great way to keep formulas easier to audit and easier for other users to understand.  In the example below, you will see how we used the individual functions in combination to create a more advanced formula.

For a detailed breakdown of how to perform this function in Excel please see our free guide on how to use IF with AND / OR.

3. OFFSET combined with SUM or AVERAGE

Formula: =SUM(B4:OFFSET(B4,0,E2-1))

The OFFSET function on its own is not particularly advanced, but when we combine it with other functions like SUM or AVERAGE we can create a pretty sophisticated formula.  Suppose you want to create a dynamic function that can sum a variable number of cells.  With the regular SUM formula, you are limited to a static calculation, but by adding OFFSET you can have the cell reference move around.

How it works:  To make this formula work, we substitute the ending reference cell of the SUM function with the OFFSET function.  This makes the formula dynamic and the cell referenced as E2 is where you can tell Excel how many consecutive cells you want to add up. Now we’ve got some advanced Excel formulas!

Below is a screenshot of this slightly more sophisticated formula in action.

As you see, the SUM formula starts in cell B4, but it ends with a variable, which is the OFFSET formula starting at B4 and continuing by the value in E2 (“3”), minus one.  This moves the end of the sum formula over 2 cells, summing 3 years of data (including the starting point).  As you can see in cell F7, the sum of cells B4:D4 is 15, which is what the offset and sum formula gives us.

4. CHOOSE

Formula: =CHOOSE(choice, option1, option2, option3)

The CHOOSE function is great for scenario analysis in financial modeling.  It allows you to pick between a specific number of options, and return the “choice” that you’ve selected.  For example, imagine you have three different assumptions for revenue growth next year: 5%, 12%, and 18%.  Using the CHOOSE formula you can return 12% if you tell Excel you want choice #2.

5. XNPV and XIRR

Formula: =XNPV(discount rate, cash flows, dates)

If you’re an analyst working in investment banking, equity research, financial planning & analysis (FP&A), or any other area of corporate finance that requires discounting cash flows, then these formulas are a lifesaver!

Simply put, XNPV and XIRR allow you to apply specific dates to each individual cash flow that’s being discounted.  The problem with Excel’s basic NPV and IRR formulas is that they assume the time periods between cash flow are equal.  Routinely, as an analyst, you’ll have situations where cash flows are not timed evenly, and this formula is how you fix that.

6. SUMIF and COUNTIF

Formula: =COUNTIF(D5:D12,”>=21″)

These two advanced formulas are great uses of conditional functions.  SUMIF adds all cells that meet certain criteria, and COUNTIF counts all cells that meet certain criteria. For example, imagine you want to count all cells that are greater than or equal to 21 (the legal drinking age in the U.S.) to find out how many bottles of champagne you need for a client event.  You can use COUNTIF as an advanced solution, as shown in the screenshot below.

7. PMT and IPMT

Formula: =PMT(interest rate, # of periods, present value)

If you work in commercial banking, real estate, FP&A or any financial analyst position that deals with debt schedules, you’ll want to understand these two detailed formulas.

The PMT formula gives you the value of equal payments over the life of a loan.  You can use it in conjunction with IPMT (which tells you the interest payments for the same type of loan), then separate principal and interest payments.

Here is an example of how to use the PMT function to get the monthly mortgage payment for a $1 million mortgage at 5% for 30 years.

8. LEN and TRIM

Formulas: =LEN(text) and =TRIM(text)

The above formulas are a little less common, but certainly very sophisticated ones.  They are great for financial analysts who need to organize and manipulate large amounts of data.  Unfortunately, the data we get is not always perfectly organized and sometimes, there can be issues like extra spaces at the beginning or end of cells.

The LEN formula returns a given text string as the number of characters, which is useful when you want to count how many characters there are in some text.

In the example below, you can see how the TRIM formula cleans up the Excel data.

9. CONCATENATE

Formula: =A1&” more text”

Concatenate is not really a function on its own – it’s just an innovative way of joining information from different cells and making worksheets more dynamic.  This is a very powerful tool for financial analysts performing financial modeling (see our free financial modeling guide to learn more).

In the example below, you can see how the text “New York” plus “, “ is joined with “NY” to create “New York, NY”. This allows you to create dynamic headers and labels in worksheets. Now, instead of updating cell B8 directly, you can update cells B2 and D2 independently.  With a large data set, this is a valuable skill to have at your disposal.

10. CELL, LEFT, MID and RIGHT functions

These advanced Excel functions can be combined to create some very advanced and complex formulas to use.  The CELL function can return a variety of information about the contents of a cell (such as its name, location, row, column, and more).  The LEFT function can return text from the beginning of a cell (left to right), MID returns text from any start point of the cell (left to right), and RIGHT returns text from the end of the cell (right to left).

Below is an illustration of the three formulas in action.

To see how they can be combined in a powerful way with the CELL function, we break it down for you step by step in our advanced Excel formulas class.

Excel Formulas =TODAY

You might find yourself needing to time stamp a spreadsheet each time it is viewed.

Instead of typing in the date manually, use =TODAY(). That’s right – you don’t even have to put a value in the parenthesis, and each time the spreadsheet is opened it will update with the current date.

Excel Formulas =PROPER

Excel isn’t just for data analysis; it is also a good platform for organizing and sorting information. When typing large amounts of text into Excel, =PROPER is a great formula to have in your pocket because it converts a cell of text to proper case, where the first letter of each word is capitalized, and the rest of the letters are lowercase.

In the example, quite a few names in Column A are not capitalized. Instead of clicking in each cell, deleting the first letter of each name and typing in a capitalised letter (which is a lot of extra clicks and time), =PROPER does it instantly, as you can see in Column D.

Use =Proper(SELECT A CELL) for this formula.

Excel Formulas =EVEN & =ODD

If you’re working with data that has a lot of decimals, this formula comes in handy. =EVEN rounds a number up to the nearest even number, and =ODD rounds a number up to the nearest odd number. If you’re working with negative numbers, these formulas still work, rounding down to the nearest even or odd number.

In this example, Column D is using the =EVEN formula and column E is using the =ODD formula.

Use =EVEN(SELECT A CELL) and =ODD(SELECT A CELL) for this formula.

Excel Formulas END OF MONTH

=EOMONTH can be used to find the last day of the current month, or upcoming months. Instead of going back and forth between a calendar and the spreadsheet, use =EOMONTH(START DATE, 0). Take this formula a step further and calculate the next month by adding =EOMONTH(start-date, 1).

Notice in the example how future months can be calculated by increasing the number at the end of the formula.

One more thing about this formula: when entering the start date, be sure to use the DATE function (2019,1,8) is January 8, 2019 so that the formula works currently. If the formula returns a #NUM! error, chances are that the date is not in the correct format.

Excel Formulas RANDOM NUMBER GENERATOR

A spreadsheet can easily be used to select random numbers using =RANDBETWEEN(SELECT VALUES).

Use this formula to choose numbers within data in the spreadsheet, as shown in the example to the left.

Another way to use this formula is to choose a winner from a list of 100 names, by having excel choose the winning row.

Percentage

To perform the percentage formula in Excel, enter the cells you’re finding a percentage for in the format, =A1/B1. To convert the resulting decimal value to a percentage, highlight the cell, click the Home tab, and select “Percentage” from the numbers dropdown.

There isn’t an Excel “formula” for percentages per se, but Excel makes it easy to convert the value of any cell into a percentage so you’re not stuck calculating and reentering the numbers yourself.

The basic setting to convert a cell’s value into a percentage is under Excel’s Home tab. Select this tab, highlight the cell(s) you’d like to convert to a percentage, and click into the dropdown menu next to Conditional Formatting (this menu button might say “General” at first). Then, select “Percentage” from the list of options that appears. This will convert the value of each cell you’ve highlighted into a percentage. See this feature below.

Keep in mind if you’re using other formulas, such as the division formula (denoted =A1/B1), to return new values, your values might show up as decimals by default. Simply highlight your cells before or after you perform this formula, and set these cells’ format to “Percentage” from the Home tab — as shown above.

Subtraction

To perform the subtraction formula in Excel, enter the cells you’re subtracting in the format, =SUM(A1, -B1). This will subtract a cell using the SUM formula by adding a negative sign before the cell you’re subtracting. For example, if A1 was 10 and B1 was 6, =SUM(A1, -B1) would perform 10 + -6, returning a value of 4.

Like percentages, subtracting doesn’t have its own formula in Excel either, but that doesn’t mean it can’t be done. You can subtract any values (or those values inside cells) two different ways.

  • Using the =SUM formula. To subtract multiple values from one another, enter the cells you’d like to subtract in the format =SUM(A1, -B1), with a negative sign (denoted with a hyphen) before the cell whose value you’re subtracting. Press enter to return the difference between both cells included in the parentheses. See how this looks in the screenshot above.
  • Using the format, =A1-B1. To subtract multiple values from one another, simply type an equals sign followed by your first value or cell, a hyphen, and the value or cell you’re subtracting. Press Enter to return the difference between both values.

Multiplication

To perform the multiplication formula in Excel, enter the cells you’re multiplying in the format, =A1*B1. This formula uses an asterisk to multiply cell A1 by cell B1. For example, if A1 was 10 and B1 was 6, =A1*B1 would return a value of 60.

You might think multiplying values in Excel has its own formula or uses the “x” character to denote multiplication between multiple values. Actually, it’s as easy as an asterisk — *.

To multiply two or more values in an Excel spreadsheet, highlight an empty cell. Then, enter the values or cells you want to multiply together in the format, =A1*B1*C1 … etc. The asterisk will effectively multiply each value included in the formula.

Press Enter to return your desired product. See how this looks in the screenshot above.

Division

To perform the division formula in Excel, enter the cells you’re dividing in the format, =A1/B1. This formula uses a forward slash, “/,” to divide cell A1 by cell B1. For example, if A1 was 5 and B1 was 10, =A1/B1 would return a decimal value of 0.5.

Division in Excel is one of the simplest functions you can perform. To do so, highlight an empty cell, enter an equals sign, “=,” and follow it up with the two (or more) values you’d like to divide with a forward slash, “/,” in between. The result should be in the following format: =B2/A2, as shown in the screenshot below.

Hit Enter, and your desired quotient should appear in the cell you initially highlighted.

DATE

The Excel DATE formula is denoted =DATE(year, month, day). This formula will return a date that corresponds to the values entered in the parentheses — even values referred from other cells. For example, if A1 was 2018, B1 was 7, and C1 was 11, =DATE(A1,B1,C1) would return 7/11/2018.

Creating dates in the cells of an Excel spreadsheet can be a fickle task every now and then. Luckily, there’s a handy formula to make formatting your dates easy. There are two ways to use this formula:

  • Create dates from a series of cell values. To do this, highlight an empty cell, enter “=DATE,” and in parentheses, enter the cells whose values create your desired date — starting with the year, then the month number, then the day. The final format should look like this: =DATE(year, month, day). See how this looks in the screenshot below.
  • Automatically set today’s date. To do this, highlight an empty cell and enter the following string of text: =DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())). Pressing enter will return the current date you’re working in your Excel spreadsheet.

In either usage of Excel’s date formula, your returned date should be in the form of “mm/dd/yy” — unless your Excel program is formatted differently.

Array

An array formula in Excel surrounds a simple formula in brace characters using the format, {=(Start Value 1:End Value 1)*(Start Value 2:End Value 2)}. By pressing ctrl+shift+center, this will calculate and return value from multiple ranges, rather than just individual cells added to or multiplied by one another.

Calculating the sum, product, or quotient of individual cells is easy — just use the =SUM formula and enter the cells, values, or range of cells you want to perform that arithmetic on. But what about multiple ranges? How do you find the combined value of a large group of cells?

Numerical arrays are a useful way to perform more than one formula at the same time in a single cell so you can see one final sum, difference, product, or quotient. If you’re looking to find total sales revenue from several sold units, for example, the array formula in Excel is perfect for you. Here’s how you’d do it:

  1. To start using the array formula, type “=SUM,” and in parentheses, enter the first of two (or three, or four) ranges of cells you’d like to multiply together. Here’s what your progress might look like: =SUM(C2:C5
  2. Next, add an asterisk after the last cell of the first range you included in your formula. This stands for multiplication. Following this asterisk, enter your second range of cells. You’ll be multiplying this second range of cells by the first. Your progress in this formula should now look like this: =SUM(C2:C5*D2:D5)
  3. Ready to press Enter? Not so fast … Because this formula is so complicated, Excel reserves a different keyboard command for arrays. Once you’ve closed the parentheses on your array formula, press Ctrl+Shift+Enter. This will recognize your formula as an array, wrapping your formula in brace characters and successfully returning your product of both ranges combined.

In revenue calculations, this can cut down on your time and effort significantly. See the final formula in the screenshot above.

VLOOKUP

This one is an oldie, but a goodie — and it’s a bit more in depth than some of the other formulas we’ve listed here. But it’s especially helpful for those times when you have two sets of data on two different spreadsheets, and want to combine them into a single spreadsheet.

My colleague, Rachel Sprung — whose “How to Use Excel” tutorial is a must-read for anyone who wants to learn — uses a list of names, email addresses, and companies as an example. If you have a list of people’s names next to their email addresses in one spreadsheet, and a list of those same people’s email addresses next to their company names in the other, but you want the names, email addresses, and company names of those people to appear in one place — that’s where VLOOKUP comes in.

Note: When using this formula, you must be certain that at least one column appears identically in both spreadsheets. Scour your data sets to make sure the column of data you’re using to combine your information is exactly the same, including no extra spaces.

  • The formula: VLOOKUP(lookup value, table array, column number, [range lookup])
    • Lookup Value: The identical value you have in both spreadsheets. Choose the first value in your first spreadsheet. In Sprung’s example that follows, this means the first email address on the list, or cell 2 (C2).
    • Table Array: The range of columns on Sheet 2 you’re going to pull your data from, including the column of data identical to your lookup value (in our example, email addresses) in Sheet 1 as well as the column of data you’re trying to copy to Sheet 1. In our example, this is “Sheet2!A:B.” “A” means Column A in Sheet 2, which is the column in Sheet 2 where the data identical to our lookup value (email) in Sheet 1 is listed. The “B” means Column B, which contains the information that’s only available in Sheet 2 that you want to translate to Sheet 1.
    • Column Number: The table array tells Excel where (which column) the new data you want to copy to Sheet 1 is located. In our example, this would be the “House” column, the second one in our table array, making it column number 2.
    • Range Lookup: Use FALSE to ensure you pull in only exact value matches.
  • The formula with variables from Sprung’s example below: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)

In this example, Sheet 1 and Sheet 2 contain lists describing different information about the same people, and the common thread between the two is their email addresses. Let’s say we want to combine both datasets so that all the house information from Sheet 2 translates over to Sheet 1. Here’s how that would work:

HLOOKUP

Similar to VLOOKUP, we have another function called HLOOKUP() or horizontal lookup. The function HLOOKUP looks for a value in the top row of a table or array of benefits. It gives the value in the same column from a row you specify.

Below are the arguments for the HLOOKUP function:

  • lookup_value – This indicates the value to lookup.
  • table – This is the table from which you have to retrieve data.
  • row_index – This is the row number from which to retrieve data.
  • range_lookup – [optional] This is a boolean to indicate an exact match or approximate match. The default value is TRUE, meaning an approximate match.

Given the below table, let’s see how you can find the city of Jenson using HLOOKUP.

Here, H23 has the lookup value, i.e., Jenson, G1:M5 is the table array, 4 is the row index number, 0 is for an approximate match.

Once you hit enter, it will return “New York”.

RANDOMIZE

There’s a great article that likens Excel’s RANDOMIZE formula to shuffling a deck of cards. The entire deck is a column, and each card — 52 in a deck — is a row. “To shuffle the deck,” writes Steve McDonnell, “you can compute a new column of data, populate each cell in the column with a random number, and sort the workbook based on the random number field.”

In marketing, you might use this feature when you want to assign a random number to a list of contacts — like if you wanted to experiment with a new email campaign and had to use blind criteria to select who would receive it. By assigning numbers to said contacts, you could apply the rule, “Any contact with a figure of 6 or above will be added to the new campaign.”

  • The formula: RAND()
    • Start with a single column of contacts. Then, in the column adjacent to it, type “RAND()” — without the quotation marks — starting with the top contact’s row.
  • For the example below: RANDBETWEEN(bottom,top)
    • RANDBETWEEN allows you to dictate the range of numbers that you want to be assigned. In the case of this example, I wanted to use one through 10.
    • bottom: The lowest number in the range.
    • top: The highest number in the range,
  • Formula in below example: =RANDBETWEEN(1,10)

Excel Keyboard Shortcuts

1. Quickly select rows, columns, or the whole spreadsheet.

Perhaps you’re crunched for time. I mean, who isn’t? No time, no problem. You can select your entire spreadsheet in just one click. All you have to do is simply click the tab in the top-left corner of your sheet to highlight everything all at once.

Just want to select everything in a particular column of row? That’s just as easy with these shortcuts:

For Mac:

  • Select Column = Command + Shift + Down/Up
  • Select Row = Command + Shift + Right/Left

For PC:

  • Select Column = Control + Shift + Down/Up
  • Select Row = Control + Shift + Right/Left

This shortcut is especially helpful when you’re working with larger data sets, but only need to select a specific piece of it.

Quickly open, close, or create a workbook.

Need to open, close, or create a workbook on the fly? The following keyboard shortcuts will enable you to complete any of the above actions in less than a minute’s time.

For Mac:

  • Open = Command + O
  • Close = Command + W
  • Create New = Command + N

For PC:

  • Open = Control + O
  • Close = Control + F4
  • Create New = Control + N

Format numbers into currency.

Have raw data that you want to turn into currency? Whether it be salary figures, marketing budgets, or ticket sales for an event, the solution is simple. Just highlight the cells you wish to reformat, and select Control + Shift + $.

The numbers will automatically translate into dollar amounts — complete with dollar signs, commas, and decimal points.

Note: This shortcut also works with percentages. If you want to label a column of numerical values as “percent” figures, replace “$” with “%”.

Insert current date and time into a cell.

Whether you’re logging social media posts, or keeping track of tasks you’re checking off your to-do list, you might want to add a date and time stamp to your worksheet. Start by selecting the cell to which you want to add this information.

Then, depending on what you want to insert, do one of the following:

  • Insert current date = Control + ; (semi-colon)
  • Insert current time = Control + Shift + ; (semi-colon)
  • Insert current date and time = Control + ; (semi-colon), SPACE, and then Control + Shift + ; (semi-colon).

Other Excel Tricks

1. Customize the color of your tabs.

If you’ve got a ton of different sheets in one workbook — which happens to the best of us — make it easier to identify where you need to go by color-coding the tabs. For example, you might label last month’s marketing reports with red, and this month’s with orange.

Simply right click a tab and select “Tab Color.” A popup will appear that allows you to choose a color from an existing theme, or customize one to meet your needs.

Add a comment to a cell.

When you want to make a note or add a comment to a specific cell within a worksheet, simply right-click the cell you want to comment on, then click Insert Comment. Type your comment into the text box, and click outside the comment box to save it.

Cells that contain comments display a small, red triangle in the corner. To view the comment, hover over it.

Copy and duplicate formatting.

If you’ve ever spent some time formatting a sheet to your liking, you probably agree that it’s not exactly the most enjoyable activity. In fact, it’s pretty tedious.

For that reason, it’s likely that you don’t want to repeat the process next time — nor do you have to. Thanks to Excel’s Format Painter, you can easily copy the formatting from one area of a worksheet to another.

Select what you’d like to replicate, then select the Format Painter option — the paintbrush icon — from the dashboard. The pointer will then display a paintbrush, prompting you to select the cell, text, or entire worksheet to which you want to apply that formatting, as shown below:

Identify duplicate values.

In many instances, duplicate values — like duplicate content when managing SEO — can be troublesome if gone uncorrected. In some cases, though, you simply need to be aware of it.

Whatever the situation may be, it’s easy to surface any existing duplicate values within your worksheet in just a few quick steps. To do so, click into the Conditional Formatting option, and select Highlight Cell Rules > Duplicate Values

Using the popup, create the desired formatting rule to specify which type of duplicate content you wish to bring forward.

In the example above, we were looking to identify any duplicate salaries within the selected range, and formatted the duplicate cells in yellow.

In marketing, the use of Excel is pretty inevitable — but with these tricks, it doesn’t have to be so daunting. As they say, practice makes perfect. The more you use these formulas, shortcuts, and tricks, the more they’ll become second nature.

SUBTOTAL

Moving ahead, let’s now understand how the subtotal function works. The SUBTOTAL() function returns the subtotal in a database. Depending on what you want, you can select either average, count, sum, min, max, min, and others. Let’s have a look at two such examples.

Fig: Subtotal function in Excel

In the example above, we have performed the subtotal calculation on cells ranging from A2 to A4. As you can see, the function used is “=SUBTOTAL(1, A2: A4), in the subtotal list “1” refers to average. Hence, the above function will give the average of A2: A4 and the answer to it is 11, which is stored in C5.

Similarly, “=SUBTOTAL(4, A2: A4)” selects the cell with the maximum value from A2 to A4, which is 12. Incorporating “4” in the function provides the maximum result. 

Fig: Count function in Excel

MODULUS

The MOD() function works on returning the remainder when a particular number is divided by a divisor. Let’s now have a look at the examples below for better understanding.

  • In the first example, we have divided 10 by 3. The remainder is calculated using the function “=MOD(A2,3)”. The result is stored in B2. We can also directly type “=MOD(10,3)” as it will give the same answer. 
Fig: Modulus function in Excel

Similarly, here, we have divided 12 by 4. The remainder is 0 is, which is stored in B3. 

Fig: Modulus function in Excel

POWER

The function “Power()” returns the result of a number raised to a certain power. Let’s have a look at the examples shown below:

Fig: Power function in Excel

As you can see above, to find the power of 10 stored in A2 raised to 3, we have to type “= POWER (A2,3)”. This is how power function works in Excel.

CEILING

Next, we have the ceiling function. The CEILING() function rounds a number up to its nearest multiple of significance.

Fig: Ceiling function in Excel

The nearest highest multiple of 5 for 35.316 is 40.

FLOOR

Contrary to the Ceiling function, the floor function rounds a number down to the nearest multiple of significance.

Fig: Floor function in Excel

The nearest lowest multiple of 5 for 35.316 is 35.

REPLACE

As the name suggests, the REPLACE() function works on replacing the part of a text string with a different text string. 

The syntax is “=REPLACE(old_text, start_num, num_chars, new_text)”. Here, start_num refers to the index position you want to start replacing the characters with. Next, num_chars indicate the number of characters you want to replace. 

Let’s have a look at the ways we can use this function.

  • Here, we are replacing A101 with B101 by typing “=REPLACE(A15,1,1,”B”)”. 
Fig: Replace function in Excel

Next, we are replacing A102 with A2102 by typing “=REPLACE(A16,1,1, “A2”)”. 

Fig: Replace function in Excel

Finally, we are replacing Adam with Saam by typing “=REPLACE(A17,1,2, “Sa”)”. 

Fig: Replace function in Excel

Let’s now move to our next function.

SUBSTITUTE

The SUBSTITUTE() function replaces the existing text with a new text in a text string. 

The syntax is “=SUBSTITUTE(text, old_text, new_text, [instance_num])”.

Here, [instance_num] refers to the index position of the present texts more than once. 

Given below are a few examples of this function:

  • Here, we are substituting “I like” with “He likes” by typing “=SUBSTITUTE(A20, “I like”,”He likes”)”. 
Fig: Substitute function in Excel

Next, we are substituting the second 2010 that occurs in the original text in cell A21 with 2016 by typing “=SUBSTITUTE(A21,2010, 2016,2)”.

Fig: Substitute function in Excel

Now, we are replacing both the 2010s in the original text with 2016 by typing “=SUBSTITUTE(A22,2010,2016)”.

Fig: Substitute function in Excel

That was all about the substitute function, let’s now move on to our next function. 

UPPER, LOWER

The UPPER() function converts any text string to uppercase. In contrast, the LOWER() function converts any text string to lowercase. The PROPER() function converts any text string to proper case, i.e., the first letter in each word will be in uppercase, and all the other will be in lowercase.

Let’s understand this better with the following examples:

  • Here, we have converted the text in A6 to a full uppercase one in A7.
Fig: Upper function in Excel

Now, we have converted the text in A6 to a full lowercase one, as seen in A7.

Fig: Lower function in Excel

NOW()

The NOW() function in Excel gives the current system date and time.

The result of the NOW() function will change based on your system date and time.

TIME()

The TIME() function converts hours, minutes, seconds given as numbers to an Excel serial number, formatted with a time format.

HOUR, MINUTE, SECOND

The HOUR() function generates the hour from a time value as a number from 0 to 23. Here, 0 means 12 AM and 23 is 11 PM.

The function MINUTE(), returns the minute from a time value as a number from 0 to 59.

The SECOND() function returns the second from a time value as a number from 0 to 59.

DATEDIF

The DATEDIF() function provides the difference between two dates in terms of years, months, or days.

Below is an example of a DATEDIF function where we calculate the current age of a person based on two given dates, the date of birth and today’s date.

Now, let’s skin through a few critical advanced functions in Excel that are popularly used to analyze data and create reports.

Conclusion

Excel is a really powerful spreadsheet application for data analysis and reporting. After reading this article, you would have learned the important Excel formulas and functions that will help you perform your tasks better and faster. We looked at numeric, text, data-time, and advanced Excel formulas and functions. Needless to say, Excel knowledge goes a long way in shaping many careers.

Be the first to comment

Leave a Reply

Your email address will not be published.


*