IFS Function

Friends,

I hope you are well known with IF function, the most amazing logical function in Excel. Excel is improving itself day by day; it’s getting intelligent to provide its best service as per our requirement.

I assume and sure that you have used the nested IF function as, =IF(conditional checking (giving reference to certain cell), value if True, IF((conditional checking, value if True, (conditional checking, value if True, ….. and so on.

Now, this is how nested IF works – a condition inside a condition, inside a condition and inside a condition. If the first condition is true, then the following conditions will be ignored. If the first condition is false, then the next condition is waiting in the [value if false]. Like this, you can ask a series of conditions to get to the correct answer.


We use Nested IF to get the desired data logically; for example, if this thing proves to be wrong then do this, and so on.

Today we will discuss about such a function which can reduce your time as well as key-stroking in your PC or Laptop and reduces the chances of making mistakes also it is faster than IF function - the function is IFS.

The IFS function is built in such a manner that it just goes on checking for the first true result and only then calculate. This makes it much faster to implement.

Description

The Microsoft Excel IFS function lets you specify multiple IF conditions within one function call.

A function call means how many times you are writing  the function.

Syntax

The syntax for the IFS function in Microsoft Excel is:

=IFS(logical test 1, value_if_true 1 [,logical test 2, value_if_true 2] ... [,logical test 127, value_if_true 127] )

Parameters or Arguments

logical test 1, logical test 2, ... logical test 127

The condition that you want to test. There can be up to 127 
Conditions entered

value_if_true 1, value_if_true 2, ... value_if_true 127


The value that is returned if the corresponding condition is TRUE. 

All conditions are evaluated in the order that they are listed so once the function finds a condition that evaluates to TRUE, the IFS function will return the corresponding value and stop processing any further conditions.

Note : The maximum number of logical test can be performed by IFS function is 127.

Returns

The IFS function returns any datatype such as a string, numeric, date, etc.


Note : If none of the conditions evaluate to TRUE, the IFS function will return the #N/A error.

Difference between IF and IFS

To understand the difference between IF and IFS function, please follow the below image carefully.

Difference between IF and IFS function in Excel

Let us analyze the steps we do normally in case of nested IF function or multiple IF function.

Steps :-

1. We first determine what is our conditional checking part.

2. Next we type our first IF statement with a bracket

3. We write our condition and then either on the TRUE part or in the FALSE part we type the second IF statement and if this condition is returned to TRUE or FALSE then we put another IF function and so on.

Basically, these are the steps we follow while framing multiple IF function or nested IF function. Depending upon the output of IF and IFS function we can say there is NO DIFFERENCE in both these functions. In syntax, we will get difference.

The most important feature in IFS function is that you don’t need to write multiple IF function one after another. It reduces keystrokes and it is faster.

It only works on the TRUE part of the IF function, if the first condition is FALSE, it will stop.

If every output of IFS function returns FALSE, IFS function will return #N/A error.

Example :

How to use IFS function

In the below image, I have plotted 9 students’ name and the marks obtained in Subject 1, let suppose we need to derive the GRADE as per marks obtained by each student.

The marks required to obtain GRADE is give in the side of this image.

If I use IF function the formula becomes =IF(C10>=85,"A",IF(C10>=75,"B",IF(C10>=65,"C",IF(C10>=50,"D"))))

Here I have nested IF function with another IF function, the IF functions other than first one is based on the FALSE statement/output.

Whereas, when I use IFS function, the Excel IFS formula becomes
=IFS(C10>=85,"A",C10>=75,"B",C10>=65,"C",C10>=50,"D") on the same condition or criteria.

You can easily differentiate between the two function the IF and the IFS.

How to use IFS function

I think you have enjoied this article about IFS function.
Thank you for reading...

How to Lock or Protect and Hide Excel Cells Formulas or Sheets

So far we have discussed about various excel formulas, excel functions, we also discussed how to use PivotTable effectively etc., today we will discuss how to protect an excel sheet or protect excel cells and hide and lock formulas.

I hope you have seen few Excel Workbooks where you are only allowed to view some part and are authorize to edit few cells only, you cannot delete or see the formula in it. These excel files or cells are locked by passwords which you cannot edit or delete.

Why we need to protect an Excel workbook?

There are many reasons why we need to protect excel cell or excel formulas. Suppose your report needs to be ‘viewed’ by third party, not directly related to your organization or let suppose you have written such a ‘formula’ which would not to be wise to displayed to anyone except you. The reasons are many why one should protect or freeze Excel Cell containing formula or data, now, let us learn how to lock excel formula.

How to lock Cells in Excel? Or, how to protect or freeze Excel cell?

Please remember, by default all cells in an Excel Sheet are LOCKED.

How to know whether cells are locked or not?

It’s very simple, select any cell or range of cells in an Excel Sheet, right click and in the popup menu select “Format Cells…” or you can click on the small arrow button (as shown in the below image) in the Home Tab under Font section.

Format Cell Option in the Ribbon in Microsoft Excel

This will open ‘Format Cells’ window, now click on the Protection Tab. In this tab two Options are AVAILABLE, first LOCKED which is checked and the second is HIDE which is unchecked. 

Note that in the beginning of this article I told you that by default every cell is LOCKED in every Excel sheet. Please have a look at the below diagram.

Format Cells window Protection Tab in Excel

Excel allows us to lock all or specific cells and sheets or the whole workbook to be locked by password.

You will find PROTECT SHEET option in the REVIEW Tab under 'Changes' menu as shown in the below image.

Password Protection - Protect Sheet Option in Microsoft Excel

If you click in this button a new window will appear asking for password, if you enter password and click OK it will ask to confirm the password again. Enter the password again and the Excel cells are locked.

In this process, each cell in the Excel Sheet will be LOCKED, no one can edit or delete any cell in this protected Excel sheet. Why? 

Because I have instructed Excel to LOCK the 'LOCKED' cells and by default all cells are LOCKED.

But this is not our requirement, our requirement is to lock few cells and allow the other cells or allow few  cells to edit and rest cells to be locked

How to lock specific cells in Excel Sheet?

Steps :

1.       Select the cells you don’t want to lock

2.       Right click to get the Format Cells option from here (shown in the below image)

Format Cell Option by right Click in Microsoft Excel

Or you can click on the small arrow in the 'Font' menu.

Format Cell Option in the Ribbon in Microsoft Excel

3.       'Format cells...' window will appear, click on the PROTECTION tab

4.       Uncheck the Locked option.

5.       Click OK

6.       Go to Review Tab, Click on Protect Sheet button.

7.       A small window will appear named Protect Sheet asking for password.

8.       Type your password (please don’t forget it)

9.       Click OK, another window will appear asking Confirm Password, reenter your password.

10.   Click OK… and Save…

Now in your Excel Sheet, cells are password protected or locked by password, the cells you selected and unlocked by unchecking ‘LOCKED’ can be Edited or Deleted but other cells are freezed.

Protect Sheet Option to enter Password

Protect Sheet Option to Confirm Password

Suppose I have an Excel table as shown below and I need to lock the cells EXCEPT highlighted in yellow (which includes a single cell ‘H6’).

How to hide excel formula 

How to unlock or unprotect two different cells separated by one or more locked cells?

To complete this task the steps are same as above only you need to UNLOCK the single cells also and then implement Locking in the Sheet from Review à Protect Sheet à Password à Confirm Password.

Note : Please UNLOCK the cells you don’t want to protect and do the reverse if you want to lock them.

How to hide and lock excel formula?

In the above picture, you can see the formula in the formula bar what to do if I want to HIDE this formula from viewing.

Steps :

1.       Select the Cell(s) to Hide

2.       Right click and click on ‘Format Cells…’ option

3.       Go to Protection Tab

4.       Check the Hidden check box (as shown below)

5.       Protect the sheet by password using above methods.

6.       The formula will not be displayed in the formula bar, its’ now hidden from viewing

How to hide excel formula 

How to lock a column or row?

To lock a single or multiple COLUMN or ROW all the steps are to be followed, the only exception is that instead of selecting the range of cells, select the whole COLUMN or ROW and then apply the Password Protection steps. If you hide these columns and rows before password protection, these columns or rows will also be locked, user will not be able to unhide these columns.

How to allow user to use filter in Protected Excel Sheet or Locked Cell?

To allow specific jobs like allowing to use AutoFilter can be done in a protected Excel Sheet, you should stop before entering password in the Protect Sheet option, check the AutoFiler option in the Protect Sheet window.

If you look carefully the Protect Sheet window you will realize that it is like a control panel to provide specific authorization to the user what they can use or not.

For example, if you want to allow the user to use FILTER in the protected sheet, you must first apply filter in the column and then TICK “Use AutoFilter” option and then protect the sheet by password as shown in the below image.

How to Use AutoFilter or Filter in Excel Protected Sheet

In the same way, you can give permission to Insert columns or rows or delete columns or rows in a password protected Excel Sheet.

How to unlock a password protected Excel Sheet or cells?

Unlocking a password protected Excel Sheet is very simple, go to Review à Unprotect Sheet à Enter password in the popup window, click OK.

I would like to share you one thing regarding password protection in Excel, although Excel Cells or Sheets are protected using this method but this password protection can also be broken using some technique, I will show you this process in another article, till then keep reading and keep learning.

I hope this article is informative regarding protection of excel formula, cell & sheet.

Thank you for reading… If you have any question to ask feel free to ask me in the comment section… and please share this article if you like this article…

Thanks again.

How to Count the Number of Spaces or any Character in a text

Friends, 

In this article, we will learn how to count the spaces or any special character or alphabet in a text in any cell in Microsoft Excel.



How to count the number of Characters in Excel?

LEN function is the answer. But LEN function alone will not help you to count how many spaces are there in a text string or in a sentence.

There is no specific Function in Microsoft Excel which can do this task. Therefore, we must use some trick to develop an Excel 
Formula which will do it for us. So, let’s do it. 

Here we will use two nested function to do the job, one LEN function to count the number of characters in a text and second SUBSTITUTE function which is used to replace specific text in a text, I have discussed in my last article.

How to Count the Number of Spaces or Alphabet or any Character in a text?

As we already know that SUBSTITUTE function replaces specific character and then it displays the modified text. For example, suppose replace the spaces by nothing (only opening and closing double quotes) between the words in the text “The quick brown fox”, the resultant text will be “Thequickbrownfox”. The spaces will be removed.

Now if we count the number of characters in this text using LEN function and deduct this number from the old text number (i.e., including spaces), we will get the number of spaces.

In the below image, I have calculated the number of spaces in a text step by step.

How to Count the number of Spaces in any Text in a cell

Step 1 : Get the number of characters in the given text using LEN function.

Step 2 : Replace the spaces using SUBSTITUTE function.

Step 3 : Count the number of characters in the text which is the output of SUBSTITUTE function.

Step 4 :  Now deduct the numbers and the difference is the number of spaces in that text.

The excel formula to count spaces or any other character or alphabet is =LEN(B3)-LEN(SUBSTITUTE(B3," ",""))

How to Count number of words in a text in excel?

Using the above excel formula we can also count the number of words in a text string. This is a tricky part, as we know in a sentence there are spaces between words, therefore, if we count the number of spaces and then add one with this number will be the number of words in a text (provided no double spaces are entered in the text).

So, the formula will be =LEN(B3)-LEN(SUBSTITUTE(B3," ",""))+1

You can further modify the formula by using TRIM function to remove the spaces before and after the text.

Now the formula will be =TRIM(LEN(B3)-LEN(SUBSTITUTE(B3," ",""))+1)


Hope you have enjoyed this article "how to count the number of spaces in a text in Excel".
Thank you for reading…

SUBSTITUTE

Today in this article I will discuss about SUBSTITUTE function in Microsoft Excel.

As the name of this function indicates, SUBSTITUTE is used to replace an old text by new text within a text or string, for example, suppose in any cell a text is written as “Excel” now you want to replace the letter ‘x’ by ‘Y’; this change can be done using SUBSTITUTE function. You can also find and replace cell reference in excel formula or find and replace part of  formula in excel.

SUBSTITUTE function, if used with some other function(s) i.e., nested it can solve difficult tasks which I will show you in my next article.

Let us discuss about SUBSTITUTE function in detail. In my next article I will discuss how to use SUBSTITUTE function. Generally this function is used to replace a particular character or number or special character.

Syntax

The syntax for the SUBSTITUTE function in Microsoft Excel is...

=SUBSTITUTE( text, old_text, new_text, [instance_num] )

Parameters or Arguments

text
The original string to use to perform the substitution operation.

old_text

The existing characters to replace.

new_text

The new characters to replace old_text with.

instance_num

Optional. It is the nth appearance of old_text that you wish to replace. If this parameter is omitted, then every occurrence of old_text will be replaced with new_text.

Returns

The SUBSTITUTE function returns a string/text value.

Note : SUBSTITUTE function is case sensitive. This function first searches for the provided text and then if found it replaces that character or string with the new text. Therefore, while writing this formula, please do not change the case of the alphabets otherwise this function will not work or it will give you an error or the replacement will not be proper. 

Example :

How to use SUBSTITUTE function?

As shown in the below picture, let suppose I have a text as  “Excel-Solutions-Basic-and-Advanced” and I have to replace the dash(-) with a blank space, it can easily be done using “Find and Replace” option in Microsoft Excel by pressing Ctrl+F but using a function is wonderful.

How to use SUBSTITUTE function in Excel formula

So, the excel formula of SUBSTITUTE function will be =SUBSTITUTE(B3,"-"," ").

Here SUBSTITUTE function omits the DASH (-) and inserts a blank space and the resultant text now is "Excel Solutions Basic and Advanced".

Hope you have enjoyed this article, in my next article I will show you how beautifully we can use this very basic function to resolve difficult situations.

Thank you for reading…

Fill Blank Cells With Value Above or Below the Cell or Zero

Hi Friends,

I hope most of us has faced a typical problem in Microsoft Excel regarding filling the blank cells in any report. Although filling up empty cells with the value above or below the current cell makes Excel reports clumsy but to fill the blank cells with the value above or below is also very useful in matching the data column wise or row wise (using CONCAT and VLOOKUP) and lastly we fill it by Copy & Paste, a tedious and monotonous job.

Today, one of my friends requested me to show the trick to fill the blank cells by the above value or value below the cell.

Today I will show you few tricks to fill the blank cells by the value above the cell or below the cell step by step in any table in Excel and in PivotTables.

To fill the blank cells, we need to first detect and select the empty cells in Excel table then we will fill these cells by the value.

Let suppose we have a table (for this example I have created a small table) as shown below.

How to Fill Blank cells in Excel

Now the task is to fill the blank cells in the column ‘D’ by the value above a blank cell and the second task is to fill the ‘F’ column by the value below the blank cell.

To complete the above task, first we must find and select the blank cell in this table.

The steps to select the blank cells in Excel Table.

1. Select the entire table or one particular column to be filled.

2. Press Ctrl+G or F5 (Function key) which will open a new window 'Go To' as shown below.

Go To Window to Select Blank Cells

3. Click on the ‘Special…’ button shown in the above image by red rectangle.

4. Another new window will appear, ‘Go To Special’, select the Blank radio button as show in the below picture.

Go To Window to Select Blank Cells

5. Click OK.

6. You can notice that the blank cells are now selected as shown in the below image.

Blank Cells are Selected

7. Now the trick is, type ‘=’ (do not select any cell, it is already selected) and press ‘UP’ arrow to select the above cell with value and press Ctrl+Enter.

8. It’s done.

Blank Cells are Filled in Excel

But did you noticed that the second task is not accomplished!!! 

So, what to do?

For the first problem, to fill the blank cells by the value above a cell we should select the ‘D’ column only then apply the above steps it will be done.


For the second task, select the column ‘F’ and press ENTER button unless the cursor moves above the cell which is filled by data and follow the same steps.

Blank Cells are filled using Cell Reference

Note : All the cells we filled are in reference to another cell, therefore, please don’t forget to use paste special to remove the formula.

How to fill blank cells by zero or any other number or text?

To do this follow the below steps.

1. Select the column or row.

2. Press Ctrl+G or F5 (Function key) which will open a new window Go To as shown below.

3. Click on the ‘Special…’ button shown in the above image by red rectangle.

4. Another new window will appear, ‘Go To Special’, select the Blank radio button as show in the above picture.

5. Click OK.

6. You can notice that the blank cells are now selected as shown in the below image.

7. Now Type your TEXT or NUMBER or ZERO and press Ctrl+Enter.

8. And you are done.

Blank Cells Filled with Text or Numbers or Zero

Isn't it awesome!!!

How to fill Blank cells in Pivot Table?

Now let us learn how to fill blank cells in a PivotTable. I have made a Pivot Table from the above table as shown in the below picture. You can notice that there are blanks below the Product Category and Employee Name column, we need to fill these blank cells by the same value in the above cell.

How to Fill Pivot Table Blank cells

Very simple steps are available in Pivot Table, please follow the below steps.

1. Select any blank cell in PivotTable where you want the items to be repeated.

2. Go to Analyze

3. In the Active Field menu, you will see an option, Field Settings.

4. Click this button, show below.

PivotTable Field Settings Option

5. One new window ‘Field Settings’ will appear

6. Select the second Tab, Layout & Print.

7. Tick the Checkbox, Repeat the item labels.

8. Click OK, its’ done.

Fill Blank Cells by repeating data in Pivot Table

Blank cells filled in Pivot Table

I hope this article is informative & you enjoyed this article.
Thank you for reading…