This blog post is about replacing a cell background color with other, using the Find and Replace tool in Excel. Let’s say, I want to replace the Yellow color in the following data with Green. in the Home tab of the Excel ribbon > Find and Select > Replace The Find and Replace dialog is…
By default, the references in Excel are Relative. This means when a formula is copied from one cell to other, the references in the formula will update according to the position of the cell in to which the formula is pasted. We have the following formula in the cell C4 of a worksheet. =C2*C3 When…
We can create a forecast based on Historical data using the Forecast Sheet option in Excel. This option is available from Excel 2016 and is placed in the Data tab of Excel ribbon Let’s see how to forecast the price of Petrol in India for the next few years, based on on the historical data…
Following are 7 different methods to combine data from Multiple columns or cells in Excel. Using & sign The & Sign, also known as the Concatenation operator can be used to join data in Excel. =”Christopher”&”Nolan” will return ChristopherNolan To introduce space in between the text strings, we need to wrap it with a pair…
While dealing with tabular data in Excel, quite often you will need the same formula in multiple cells. Following are 3 quick methods to apply the same formula to an Entire Column in Excel. Using Keyboard Shortcut Ctrl + Enter is the keyboard shortcut to enter the same value or formula into multiple cells. So…
Following are 3 different methods to reverse text strings in Excel. Dynamic Array Formula to reverse Text The following formula combines the MID, TEXTJOIN, LEN and the SEQUENCE function to reverse the text string supplied into it. =TEXTJOIN(“”,1,MID(B3,SEQUENCE(LEN(B3),,LEN(B3),-1),1)) Let’s see what happens when we supply the text ‘Hana’ into the above formula. =TEXTJOIN(“”,1,MID(“Hana”,SEQUENCE(LEN(“Hana”),,LEN(“Hana”),-1),1)) Length of…
Mr.Excel.com Bill Jelen aka Mr. Excel is a 17 times Microsoft Excel MVP award winner. His website mrexcel.com answers over 30,000 Excel questions a year. Bill Jelen also manages a YouTube channel in the same name and has done more 2,000 videos on Excel. Whenever Microsoft releases a new feature in Excel, you can expect…
Following are 3 different ways to segregate Positive and Negative values from list of numbers in Excel. MAX and MIN Function to separate Positive and Negative values The MAX function in Excel will return the largest among 2 or more numbers supplied into it. This property of MAX function is used below to check whether…
This blog post is about 10 different ways to find the Sum in Excel. We have a list of 5 numbers in the cells B3, B4, B5, B6 and B7 as shown below. Now let me show you the most common and uncommon methods for finding Sum in Excel. Using Addition Operator ‘+’ =B3+B4+B5+B6+B7 SUM…
This blog post is about combining the NOW function with the Conditional Formatting feature in Excel to highlight a cell or cells corresponding to Current Time. The following is a very common spreadsheet template used at working environments for recording manhours. Let’s see how to spot the cell corresponding to 10:23 A.M (Current Time) on…