Category Archives: Functions

Formula to find next Monday’s date

Here’s a formula that you can use to generate next Monday’s date: =TODAY()+8-WEEKDAY(TODAY(),2)

Posted in Functions | Leave a comment

How to make VLOOKUP search from the bottom to the top of the list

Normally, VLOOKUP searches data from top to bottom. But when you have a long list of data and you want to get the latest data at the bottom of the list (instead of the older data at the top), you can’t use VLOOKUP because it always returns the first one it sees from the top, […]

Posted in Functions | 1 Response

Alternative to VLOOKUP: INDEX & MATCH

VLOOKUP and HLOOKUP take up a lot of memory and processing power, especially if you have a large data set. A great alternative to VLOOKUPs and HLOOKUPs are the INDEX & MATCH functions. Below is an example from Mr. Excel. The Problem One day, you have a situation where you have the employee name, but […]

Posted in Functions | 38 Responses

How to add or insert a line break or carriage return in a cell in Excel

Want to add insert line breaks and carriage returns from a cell in your Excel file? Let’s say, we have the following example: John Smith But now we need these two words to appear on two separate lines instead of a single line, just like this: John Smith There are two options to get the […]

Posted in Functions | Leave a comment

How to remove line breaks and carriage returns in cells in Excel

Want to remove line breaks and carriage returns from a cell in your Excel file without having to do it manually? The good news is, you can use the “find and replace” function on Excel to get this job done. Let’s say, we have the following example: In this case, the author has used the […]

Posted in Functions | 6 Responses

How to remove line breaks from Excel

Want to remove a line break in Excel? Follow the steps below! In Excel, choose Edit>Replace Click the “Find what” box Hold the Alt key, and (on the number keypad), type “0010”. This represents a line break in Excel. You won’t see anything in the “Find What” box In the “Replace with” box, type the characters that […]

Also posted in General tips | Leave a comment

How to remove the last character in a cell in Excel

For example, you have the following the text in a cell “A1” and you’d like to remove the last character “s”: “I want to remove the last character of this cells” To remove the last character “s” from A1, use the following formula: =LEFT(A1,LEN(A1)-1) The result: I want to remove the last character of this […]

Posted in Functions | 6 Responses

How to capitalize the first letter in a cell in Excel

If you’d like to capitalize all letters in a cell, it’s easy – just use the following formula (assuming cell “A1” has the text you want to capitalize). =PROPER(A1) But what if you just want to capitalize the first word in the cell “A1”? For example, you have the following  text in a cell “A1” […]

Posted in Functions | 46 Responses

How to convert time format into decimals in Excel

Looking to convert data in time format into decimals on Excel? Try this formula: =A1*1,440/60 where A1 is a cell containing a time value If A1= 12:35, it returns a value of 12.58.

Posted in Functions | Leave a comment

How to display A-Z without typing them manually in Excel

If we want to display a number series, say 1-100, in column A, we can simply drag the cursor to expand the series. But if we  need to display an alphabetic series, say we require A-Z, in column A, unfortunately dragging will not work. One option is to key each alphabet manually. That works, but […]

Posted in Functions | Leave a comment