Loading...

Follow Excel Solutions - Basic and Advanced on Feedspot

Continue with Google
Continue with Facebook
or

Valid

In this article, we will discuss about the search methods in 'Excel FIND & REPLACE'. The FIND & REPLACE option in Excel can do lot of things not only to FIND and then (if necessary) REPLACE but also you can search your ‘search items’ by their color, cell formatting, you can even use wild characters like ? or *.





The shortcut is Ctrl+F to open the FIND window and Ctrl+H to open the FIND & REPLACE window, the title of both the window is FIND & REPLACE.

You can also get this window from menus in the ribbon, you need to click in the Home tab, under Editing you will see an icon of Magnifying Glass, click in this icon, a drop-down menu will open and you will find these two options, FIND and REPLACE.

Find and Replace from Menu
Find and Replace from Menu


You will get the window of FIND & REPLACE as below.

Find and Replace Window


How to use FIND & REPLACE ?

As described above use either shortcut or from the menu click get the window FIND & REPLACE, if you want to find some item, type the item in the input box named as ‘Find what:’, press enter or click Find Next button. 

As soon as you click the Find Next button, the search term if available will be selected i.e., the cell containing this search term will be selected, if not available a warning window will appear saying item not found.

Error Message when Search Term not found






To replace a string or any other character we use the Replace tab in the Find and Replace window, you can get this option by the  using shortcut Ctrl+H or from menu or by clicking the Replace tab available in the FIND and REPLACE window. It displays two inputs boxes, ‘Find what:’ & ‘Replace with:’

In the first input box type that you want to replace, in the second input box type the replacement of it, now to replace one by one click Replace button and to replace all the matching text or characters at a time click Replace All button.

We all know these methods very well, I hope, but in the next paragraphs you will find the real power of FIND & REPLACE option in Excel.

The OPTIONS Button in the FIND & REPLACE window : Advanced Search
You might notice that there is an Options button above the Close button. If you click this button, the FIND & REPLACE window will expand and will show you few more options as shown in the below picture.

Options Button in Find and Replace


The available options are :

v     Match Case : Check (tick) this option if you want to find the cells that matches with the search term provided, it might be a part of the cell content.

v     Match entire cell content : Check this option to find the cell that has exactly same data as provided by the search term

v     Within : Drop down menu : By default Workbook is selected, if you want to search in the current Sheet where you are working, select from the drop down menu.

v     Search : Drop down menu : Here you implicitly instructs Excel to search by columns or rows, we rarely use this option, for very large and big data we may use it to reduce the search time.

v     Formulas : Drop down menu : This is most important part because it relates to FORMULAS. There are three options in this drop down menu, Formulas, Values & Comments.

Ø     Formulas : We use simple to complex formula in Excel to process data, sometimes we need to find or replace part of formula. If you select this option and try to search for example, $D - which is generally a part of formula the cell containing $D will be selected. Now you can change your formula easily using this option. For example, suppose we need to replace the $D by $E in a sum function as =SUM($D:$G), it can be replaced using this option.

Ø     Values : The FIND will work on the value of the cell ignoring what formula is there in the backend.

          Sometimes you may be surprised why you cannot find the search string using FIND and Replace option although the data is existing. This is one of the reasons behind the not getting your search term although exists, therefore, always check what option is selected in the Search option, if you are searching for a value and Search option is set as Formulas, this will not work.

Ø     Comments : If you want to find something from the comment section in the Excel, this option must be selected. I hope you know what is a comment in Excel, I will discuss it latter. A cell is marked in red in a corner of a cell if any comment is present and normally the is hidden, it displays when we hoover over that red triangle.

v     Format button (in the right upper corner) : You will find Format button in Find option but two Format button in Find & Replace option, this option is used to find the data based upon the formatting of the cell. Therefore, you can guess that if you click on this button a new window will appear, the well-known Format window, only the name if Find Format. Select any format that you want to search by the format already formatted.






Let us have some examples with pictures to make things clear.

How to use Find and Replace ?

Press Ctrl+F, type your search term, click on Find Next button.

How to use Find and Replace 


How to useFind and Replace to find all the occurrences of given Search term ?

Same as the above process, only you need to click on the Find All button and a list of occurrences will appear in the below list, if more than one row is visible click on the link that cell will be selected. In the below example two times the name is coming Donald Trump and two lines are visible in the box highlighted by Blue Color.

Multiple Search Result in Find and Replace


How to Find Data or String using Cell Color or Format ?

Click on the Options button, now click on the Format button which will open another window Find Format, select the format as per your search format, for example, the cell color is yellow containing the name Donald Trump, so I select the yellow color from the Fill option in the Find Format window and clicked Find Next, the cell is highlighted as shown below.

Search by Format


Note : Find by color will take more time than normal search.

Search by Cell Color or Format


When you want to replace your string you can follow the same method the only difference is that you must use Replace window by pressing Ctrl+H and use your replacement text or data. The rest is same, so I will not discuss it here, if you find problem please comment on the COMMENT box, I will clarify.

How to Search using Wild Card Character ?

There are two wildcard characters available ‘?’ and ‘*’.

v ‘?’ is used for any single character, For example, sm?th finds "smith" and "smyth"

v ‘*’ is used for any number of characters, For example, *east finds "Northeast" and "Southeast"

WildCard Search


Now the obvious question is how to search for these ‘?’ and ‘*’ wildcard characters?

Lets’ discuss about it.

Search for Wild Card Characters

Did you ever try to find ‘?’ or ‘*’ the Wild Card characters’ using Find and Replace ? If yes, then you have noticed that it gives nothing !!! It just moves selected cells by one; like you are pressing Enter key in Excel. As you know these (‘?’ or ‘*’) are wild card characters, you can use these characters to find ‘partially known’ search terms.

How to search for Wild Card Characters ?

To search for ‘?’ or ‘*’ you must use a tiled (~) before these characters as shown in the below picture; when tiled sign is used before ‘?’ or ‘*’, Excel treats these as normal characters.

Search for Wildcard Characters


Hope you have enjoyed this article.
Thanks for reading…
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Hi Friends,


In the last article, we learnt how to create Charts and the basic uses of the ‘Chart tools’ in the right-side panel, in this article we will discuss how to select the proper chart for your data and how to use trend line. We will discuss how you can change the chart data source without effecting the existing Chart. We will also discuss how to format a chart to make it presentable, more styles and colors to make it attractive in any presentation.






Before going to the discussion, I would like to inform you, please note that from the technical point of view every chart works in same manner except few charts like PIE chart. I will discuss about PIE chart in another article because improper use of PIE chart will impact your data analysis.

So far we know that to make a chart, we need to go to the INSERT menu and we select a Chart from Charts group after selecting the Data and the chart is displayed.


What chart to select or choose for your data?
The most confusing thing in preparing Charts for the MIS people or the data analyst is which chart type to be selected or which chart type is the perfect Chart for your data. Friends, it solely depends upon your data, how the data is arranged, the type of data you are using etc. Just think before selecting the chart type and I am sure your chart selection will be the best.

Remember, you should always choose that chart which describes your data more logical and analytical. For example, if you need to display the percentage share of Sales made in the cities like New York, Los Angeles, Chicago, Houston, Philadelphia, Phoenix and San Diego, you must use a PIE Chart, because PIE chart will not only display the percentage but also it will show the area it covers in a circle and a sudden drop of sale or growth of sale will be prominent because the area will either be relatively low or big area.


PIE Chart showing Market Potentiality with percentage share


Now let suppose you want to display how Sales are performing with respect to potentiality; you should use a BAR Chart or a COLUMN Chart because BARS or COLUMNS will be positioned side by side. If you want to compare between the Sales made in the last month last year versus current year last month or the target taken you may use bar charts with a TREND line. (I will show you how to add a trend line.)





As soon as you select a chart after selecting your data range, the chart is displayed, with colored lines (for Line Chart) or bars (for BAR or COLUMN Chart) i.e., the pictorial representation of your data, here you will find a Legend box, which signifies the used colors in the chart and the data it represents e.g., blue line for potentiality and saffron colored line for sales quantity, Right Click on chart a drop-down menu will appear, at the bottom you will find an option Format Legend…, you can align the position of this legend in the right tool box under Format Legend option.


Right Click Drop Down Menu Format Legend


This is an important drop-down menu because you can use it for many purposes, like delete or position the Legend, Change the chart type etc., you can also change the font of the Chart and you can change the data.


How to Change Chart Type of an Existing Chart?
If you click on the Change Chart Type option, a new window will appear and you can change your chart from BAR chart to Pie Chart or scatter chart etc.


How to Change Chart Type of an Existing Chart


You can format the AXIS (X or Y axis) of the chart, click on the AXIS box and in the right tool box you can change it or you can right click on it and the option Format Axis will appear where you can align or give text directions etc.







Format Chart Axis



How to Change Data Source of an Existing Chart without effecting Chart Design?
Right Click on the Chart, from the drop-down menu click on Select Data Source, a new window will appear as Select Data Source.


How to Change Data Source of an Existing Chart without effecting Chart Design


In this window, you can very quickly and easily change the data range, first remove the data range in Chart data range textbox then select the data in the Excel sheet, click on OK and you are done.
Excel provides options to display the selective data range, in the Legend Entries (Series) just below the Chart data range textbox you can select what to display by unchecking tick mark in Select Data Source window.

Double click on the chart, two new menus will appear in the ribbon (top of the Excel) as Design and Format where you will get lots of options to make your chart visually beautiful.


Chart Menu - Design & Format


For example, in the design window you can choose the desired chart style which is predefined. You can Switch between rows and columns if you click the Switch Row/Column button. Here you get the Select data button which we already get from the drop-down menu.

Note : Except PIE chart everything is very similar to different types of charts, if you know how to format a BAR chart, you can also format the LINE Chart or Column chart.


How to add a Trend Line in Charts?
Except PIE Chart, you can add TREND line to every chart, be it a Line chart or a BAR chart or any other chart type.

To insert a Trend Line, you just click on the line or the bar in the graph or chart to select the line or column and then right click over it, from the drop-down menu select Add Trendline… and you will notice instantly a line is created and this line is the TREND Line which shows the trends of the given data.


How to add a Trend Line in Charts


Friends, that’s all for this article, thank you for reading this article.
Take care…  

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
"A picture is worth a thousand words" – is also true in Data Analysis. Today we will discuss about pictures which expresses the data, the Excel Charts or Graphs.

Why draw Chart or Graph ?







The goal of data preparation using functions or excel formula is to analyze that data and to get productive and growth oriented management decisions.

If you are linked with data analysis (even you make single report based on some data) then you will understand the importance of Charts in Excel after this example. Let suppose there are 365 rows in a column as sales value for the entire financial year day after day and you need to find out the deviations throughout the financial year. I mean to say 'On which days the Sales volume was low ?' or 'On which days the Sales volume was High or at peak ?'   Obviously, we can use Excel’s inbuilt functions or complex Excel formulas but a chart can solve this in few minutes and we can avoid complex excel formulas.


Besides in Power Point Presentations, Charts are very important as the actual status or performance will better be understood using charts.

Today I will discuss about Charts in excel, the basic and then how to use it. If you know how to create or use charts you can skip this article but reading a topic again might give you some new idea.

There are few types of charts,  

The available Charts in Microsoft Excel are as below.

1.       Column or Bar Chart

2.       Line or Area Chart

3.       Pie or Doughnut Chart




4.       Hierarchy Chart

5.       Statistic Chart

6.       Scatter or Bubble Chart

7.       Waterfall or Stock Chart

8.       Combo Chart

9.       Surface or Rader Chart

And finally

10.   PivotChart.


In this article, we will discuss about those charts which we use very frequently and later I will discuss about the rest charts.

Let us first Know the names of the Charts to identify the charts by its' name, please follow the below images, for the shake of an example I have taken few City names in United States like New York, Los Angeles, Chicago, Houston, Philadelphia, Phoenix, San Diego and fictitious Potentiality and Sales.

Pie or Doughnut Chart : 

A pie chart (or a circle chart) is a circular statistical graphic which is divided into slices to illustrate numerical proportion. In a pie chart, the arc length of each slice (and consequently its central angle and area), is proportional to the quantity it represents.





Pie or Doughnut Chart
Column Chart or Bar Chart : Column charts are used to compare values across categories by using vertical bars.


Column Chart

Column Chart

Line chart : A line chart is a graph that shows a series of data points connected by straight lines.


Line Chart

Area Chart : An area chart is a line chart with the areas below the lines filled with colors. Use a stacked area chart to display the contribution of each value to a total over time.


Area Chart
Combo Chart : A Combo or Combination chart is a chart that combines two or more chart types in a single char e.g., Column or Bar Chart with a Line Chart.


Combo Chart



How to insert a Chart?

v  To insert a Chart, you need to move the menu INSERT, you will find Charts Group as shown in the picture.

Chart Menu Option in Ribbon

v  Select the entire data or you can select specific columns to get a chart, we will discuss later.

Potentiality & Sales Table

v  Now choose any chart (to understand the basic chart functionality, later we will use the most appropriate chart).


v  For example, I choose the Column Chart and the first Chart, as shown below which is a very basic chart, we need to ‘decorate’ it.

Default Excel Chart

v  This chart is very basic without a Chart Title (always give Title to your chart), to give a title to your chart just click on the Letter CHART TITLE, a text box will appear

v  You can now remove the default title and put your own title

Quick Format Buttons
v  If you click on the chart, you will find three buttons at the right side of the chart, these are the quick chart formatting tools, you can even change the Chart Type.






v  But let us concentrate on the New Helper window at the right side named as Format Chart Area. In this area, you can format your chart, for example, you can give border, border color, border type and you can change the corner to rounded corner, you can fill the space in the charts etc.

Format Shape of a Chart

Format Shape of a Chart


v  In the above image, there are three options, Fill & Line, Effects & Size & Properties. Using these tools, you can easily modify your chart.

v  If you right click your mouse selecting the chart, a drop-down menu opens as shown in the below image, from here you can also modify your charts, the options are self-explanatory.

Right Click Menu

v  Two additional new menu opens in the ribbon, if any chart is selected, as shown in the below image, one is Design menu and the other is Format menu. You can choose any design and format Charts very quickly.



These are the basic of Chart or Graphs and their formatting, in the next article I will show you some advanced use of Charts in Excel as well as in PowerPoint Presentations.


I hope you have enjoyed this article.
Thank you for reading… Take care…
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Hi friends,

Splitting cell content was a challenge but using Text to Columns and using Flash Fill we can now split the cell content in the next columns.

It was easy, isn’t it?

But sometimes it is the situation’s call that we must use formula to split the cell content (or better to say to extract specific data) and spread it over the next columns.

Honestly speaking, if you want to master Microsoft Excel, you must learn to solve the problems using formulas.

In this tutorial or article we will discuss how to split cell content in the next columns.

How to separate text in excel using formula?

or
How to separate first name, last name and date of birth in excel?


Suppose we got a table containing few names and their birthdays like ‘Jacob Elordi - June 26, 1997’ and in next row ‘Joey king - July 30, 1999’ and so on and we need to get the first name, last name and their date of births in the next columns.

Note : Separators can be commas, dots, hashes etc, in place of spaces but the excel formula will be the same, just replace space by that character. 

How to do it?

Let us solve this problem Step by Step.

1.     If we analyze the first row containing “Jacob Elordi - June 26, 1997”, we can understand that there are two separators, spaces and hyphen

2.     If we can identify the position (number) of the spaces and the hyphen, then we can get the desired texts easily using some TEXT functions.

What functions to use to split the cell text and spread over the next columns?

3.     We will use functions, SEARCH function and LEFT function, RIGHT function and MID function


Here SEARCH function will give us the position of the space and hyphen.


How to get First Name using formula in Excel?


Steps :

1.     What is the output of SEARCH function? It gives us the position of a specific character in a text or string, like =SEARCH(" ",C3,1); output is ‘6’ for the first row. (Here we are searching for space.)

2.     Now we will use LEFT function and we need only one number, the position of first “space”, but =SEARCH(" ",C3,1) formula gives us the position of the first occurrence of space, we need to less one to get the number of characters in first name, therefore, our excel formula becomes =LEFT(C3,SEARCH(" ",C3,1)-1) the output is ‘Jacob’, the first name in the First Name column.


We did the first task !


Split Cell Content using Excel Formula - Functions LEFT & SEARCH



How to get Last Name using formula in Excel?


This is a little complex, but think logically and enter the formula step by step, you will get the Last name.


Steps :


1.     We got the position of first space as 6 in =SEARCH(" ",C3,1) formula. The next task is to find out the second occurrence of space. How to get the next occurrence of space?


2.     Use nested SEARCH function as

=SEARCH(" ",C3, SEARCH(" ",C3,1)).

Explanation :

The first SEARCH function is searching for space but the starting number in this function should be 6 (the position of first space), this number is provided by the next SEARCH function colored in RED and we used it in our previous task. If we less 1 from this output, we will get the number of characters in the last name i.e., SEARCH(" ",C3,SEARCH(" ",C3,1)-1) ; output is 6.


3.     Now we know the starting position and the number of character in the last name part. We can use MID function to extract the last name because MID function requires text, start number and number of characters and our final formula using MID function is,
=MID(C3,SEARCH(" ",C3,1)+1,SEARCH(" ",C3,SEARCH(" ",C3,1)-1))

In this formula, ‘+1’ is used to get the position of the first character in the last name after first space and ‘-1’ is used to get the number of characters’ present in the last name before second space.


Split Cell Content using Excel Formula - Functions MID & SEARCH



How to get Date of Birth using formula in Excel?


This is easy, very similar to the method we use to get the first name, the difference is that we will use RIGHT function, as it is logical and easy to get the data of birth from the right side rather than traversing from left side.


Steps :


1.     We will search for hyphen in the given text using = SEARCH("-",C3,1), which gives us the position of hyphen from the left side, therefore, we will less one (as ‘-1’) to get the number of characters in the date of birth.


2.     Our excel formula to get the date of birth from the text is =RIGHT(C3,SEARCH("-",C3,1)-1)


Split Cell Content using Excel Formula - Functions RIGHT & SEARCH



I hope you have enjoyed this article.
Thank you for reading… Take care…

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Hello friends,


Today I will discuss about Flash Fill in Excel, a wonderful tool introduced in Excel 2013 and 2016 to separate the data in a cell.

In my last article, I had discussed about Text to Columns, generally we use to split cells and spread the data over the next columns. In this method, I hope you have noticed that when applied text to a certain column, the column also split and the original column disappeared. But sometimes we need to keep the original column.

How Flash Fill works?

Flash Fill analyzes the data you are entering and it tries to figure out what result you are trying to achieve. I told you, Microsoft Excel is getting wiser!!!

How to Use Flash Fill in Excel?

Let us explain this tool with an example, suppose as shown in the below image we have few names with their birthdays and we need to separate it by first name, last name and birthdays. You can split these by months or days or by years but for simplicity we will break it by first name, last name and birthdays.

We need the table as under from the second table.

Table - 1
How to Use Flash Fill in Excel

Table - 2

Use Flash Fill to Split Excel Cell



In the first row, I have “Jacob Elordi - June 26, 1997”, (a movie actor,) I desire to get the text to be split by ‘Jacob’ then in the next column ‘Elordi’ then ‘June 26, 1997’.

Let’s use Flash Fill Step by Step :

     1. First of type the header of the table as First Name, Last Name & Date of Birth.

     2. Type the first name in the column named First Name e.g., Jacob

     3. Press Enter, then select the above cell again where you typed Jacob

     4. Go to Data then in the Data Tools group click Flash Fill.

Flash Fill Option in Excel


     5. Now you will see all the column is filled with the first names

     6. Again, type the last name in the next column labeled as Last Name and repeat the steps.

     7. Apply the same procedure to the Date of Birth column.


The final table structure will be as under. Isn’t it amazing tool provided by Microsoft Excel!!!

After Using Flash Fill in Excel

The Shortcut for this tool is Ctrl+E.

Note : Flash Fill is turned off by default, you need to activate it from File, Options then Click on the Advanced and select Automatically Flash Fill.

Flash Fill Option in Excel



I hope you have enjoyed this article regarding Flash Fill.
Thank you for reading…

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 


Hello friends,


In any organization, the data is stored in Databases, like Oracle, SQL, SQLite, MySQL, Sybase etc., and we collect or download these data from e-commerce software or ERPs like SAP, CRM, ERP5, ERP Next etc., or directly from the database, sometimes you might have noticed that these files are with “ *.CSV ” extension or other extensions which gives us all data in a single row in one Cell! 

But to analyze these data we need to separate these data from one Excel cell to more than one cell in the next columns.
We can split these data using Excel’s Text to Column option. Today we will discuss about Text to column in Excel.

Let us have a look at the below image, which is combination of numbers, texts, special characters and dates.



Excel Split Cells by Text to Column Option


To analyze such complex data, we first need to spread it over the next columns by the data type, like numbers in a column then texts in the next column and so on.

How to split one column into multiple columns in excel?

Let me show you step by step and with related images.


Steps :-


1.     Select the cells row wise

2.     Click on the Data tab


3.     In the Data Tools group, click on the Button ‘Text to Columns’


Text to Columns Option in the Ribbon in Excel


4.     A new window will appear, ‘Convert Text to Columns Wizard – Step 1 of 3’, as shown in the below image


Excel Split Cells by Text to Column Option


5.     Here we get two Options, Delimited & Fixed Width.

a.     Delimited : If we select this option Excel will offer us to split the cell by ‘specified’ criteria, like comma, semicolon, space, tab etc., in the next window. In  this window if you have some special delimiter (e.g., @, !, #, $, * etc.,) besides these in built option (Tab, Semicolon, Comma & Space) you can use the Other option and put your special delimiter to split the cell by this delimiter.

b.     Fixed Width : If we select this option, Excel will allow us to split the cell by fixed width, i.e., character count will be fixed for all cells in that column.

6.     Select your appropriate option and click Next.

Excel Split Cells by Text to Column Option


7.     If you are not using the Fixed Width method, just click Next and  move the upward pointing arrow from left to right as per requirement and click Finish.





Excel Split Cells by Text to Column Option


Excel Split Cells by Text to Column Option


8.     And you are done.

Excel Split Cells by Text to Column Option


Delimited or Fixed Width – Which one to use?

Remember Fixed Width will split cell contents by fixed character count, Let suppose we have got such a data where few digits (uniform number of digits) are at the beginning and then few texts are at the end of the data and we need to separate the numbers and the texts from this data. Here we will use Fixed Width option.

In the below picture, we have a fixed text in the C column as 
“Today is :”, suppose we need to separate or split these two data types One column containing the text part and the next will contain the Data portion.


Excel Split Cells by Text to Column Option


Here we will use Fixed Width option and the final data will be as shown in the below image.


Excel Split Cells by Text to Column Option


On the other hand, if a data is separated by Tab, Semicolon, Comma or Space we will use Delimited option.

I hope you have enjoyed this article, in my next article I will show another few techniques to split the cell using formula and other options available in the Excel.

Thank you for reading…


Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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.

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...
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
So far we have discussed about various excel formulas, excel functions, we also discussed how to use PivotTable effectively and more, 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 need 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 it 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.

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.
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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…

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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 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 a formulas 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.

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 provided one. 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…

Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview