Same value in multiple cells

Same value in multiple cells For some reason, you may have to write the same thing overand over again in cells in a worksheet. That's excruciating.Just click the entire set of cells, either by dragging your cursor,or by holding the Ctrl key as you click each one.Type...

read more

Count dates in a year

Count dates in a year To count dates in a given year, you can use the SUMPRODUCT and YEAR functions. In the example shown, the formula in E5 is: =SUMPRODUCT(--(YEAR(dates)=D5)) What is (--) in the SUMPRODUCT formula? Function (--) converts a return value of “TRUE”...

read more

Excel Formula Error Checklist

Excel Formula Error Checklist Use this checklist to quickly understand common formula errors, what they mean, when you would see them and how to fix them. Read on to know more about the errors. Error What it means? Most common reason How to fix it? #N/A Not Applicable...

read more

Calculating Interest

Calculating Interest Here I will show you how to calculate compound interest as well as normal interest. COMPOUND INTEREST To calculate compound interest in Excel, you can use the FV function. This example assumes that R1000 is invested for 10 years at an annual...

read more

Roman Numerals

Roman Numerals Have you ever needed to use Roman numerals and just typed them in manually? What happens when you need to type in a value of 100? Do you know the Roman numeral for that? There is a very simple solution: =ROMAN (number, [form]) number - Number (in Arabic...

read more

Time Difference

Time Difference Calculating the difference between two times in Excel can be tricky. Times are handled internally as numbers between 0 and 1. Have a look at the image to see how you can use different methods to display the times. The formulas for the corresponding...

read more

VLOOKUP multiple values

VLOOKUP multiple values You can use VLOOKUP and view an entire record of someone or something. Below I have a list of folks with some details. The corresponding functions I have highlighted with colour blocks. Each corresponding colour is the function that you type...

read more

Data Validation

Data Validation In this example, we restrict users to enter a whole number from 0 and 10. On the Data tab, click the Data Validation drop down and select Data Validation. Enter the minimum and maximum numbers. Select your title and input message. Select your error...

read more

SumProduct

SUMPRODUCT When you hear the name of SUMPRODUCT for the first time, it may sound like some useless formula that performs an ordinary sum of the products operation. But that definition does not show even a tiny fraction of what Excel SUMPRODUCT is capable of. In fact,...

read more

Multi line text in a cell

Multi line text in a cell When you have a lot of text in your Excel cells it can be a good idea to show it on more than one line. But how? Every time you enter text into a cell it longs to be on one line however long it is.Here is how you can insert more than one line...

read more

Comparing Data

Comparing Data To compare two columns in Excel row-by-row, write a usual IF formula that compares the first two cells. Enter the formula in some other column in the same row, and then copy it down to other cells by dragging the fill handle (a small square in the...

read more

Outlining Data

Outlining Data Outlining data makes your data easier to view. In this example we will total rows of related data and collapse a group of columns. We will be using the "Company" data column.  See screenshot below on the steps. You first need to sort the "Company"...

read more

SUMIF function

SUMIF function The Excel SUMIF function returns the sum of cells that meet a single condition. Criteria can be applied to dates, numbers, and text. The SUMIF function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.   range -...

read more

Simple IF

Simple IF Statement The Excel IF Statement can be used for many things. A good friend of mine, Arthur wanted something to automate different totals in different columns. By using the IF statement, it makes it easy. If you look in the "Comments" section of the...

read more

TRIM Function

TRIM Function The Excel TRIM function strips extra spaces from text, leaving only a single space between words and no space characters at the start or end of the text. TRIM strips extra spaces from text, leaving only single spaces between words and no space characters...

read more

Week Number

Week and Day number The Excel WEEKNUM function takes a date and returns a week number (1-52) that corresponds to the week of year. The WEEKNUM function starts counting with the week that contains January 1. By default, weeks begin on Sunday. .   The WEEKNUM...

read more

Div Error

#DIV/0! error The #DIV/0! error appears when a formula attempts to divide by zero, or a value equivalent to zero. Like other errors, the #DIV/0! is useful, because it tells you there is something missing or unexpected in a spreadsheet. You may see #DIV/0! errors  when...

read more

Create Hyperlink

Create Hyperlink The Excel HYPERLINK function returns a hyperlink from a given destination and "friendly name". You can use HYPERLINK to construct a clickable hyperlink with a formula. The HYPERLINK function can build links to workbook locations, pages on the...

read more

Min And Max Values

Minimum And Maximum Values. To get the minimum value from a set of numbers, use the MIN function =MIN(range) As you can see, we enter the MIN function in cell H3 and then copy it down all the way. The formula can be seen in the formula bar: =MIN(C3:E3). This will...

read more

Index and Match – Part 2

Index and Match - Part 2   Today I am going to show you how easy it is to get information from various columns and rows. This will also show you a custom error that you can use. Have a look at the formula below. It looks complicated, but take it step by step....

read more

Index and Match – Part 1

Index and Match - Part 1 How to use INDEX and MATCH Apart from VLOOKUP, INDEX and MATCH is the most widely used tool in Excel for performing lookups. The INDEX and MATCH combo is potent and flexible, and you'll see it used in all kinds of formulas, from basic to very...

read more

Flash Fill

Extract First Names and Last Names from a cell Suppose you have a series of names in the A column, and that the first and last names are all typed in the same cell: What you want to do, however, is to have the first names in the A column and the last names in the B...

read more

Copy a formula

Copy a formula You can copy any formula across a range of cells using your mouse. Once it’s entered, click on the cell, then click on the little box that appears in the lower right-hand corner. Drag it across the desired range of cells, and you will populate the range...

read more