Count Not Blank Cells - COUNTA Function

Today in this article I will discuss about the excel function COUNTA, a very small inbuilt function in Microsoft Excel.

Let us first discuss about this function then we will discuss where can we use it and how we can implement this function.

Description

As the name suggest the Microsoft Excel COUNTA function counts the number of cells that are not empty as well as the number of value arguments provided.

Syntax

The syntax for the COUNTA function in Microsoft Excel is:

=COUNTA(value1, value2, ...)

Parameters or Arguments

value1, value2, ...

Each argument can be a range, a cell or a value. There can be up to 255 arguments.

Returns

The COUNTA function returns a numeric value.

Note : Please remember this function returns not empty cell count, therefore, a blank space will also be counted. I strongly recommend to use TRIM function (if the data is very big) before applying COUNTA or use TRIM nested.

How to use COUNTA function ?

1.   Select a cell, then

2.   Type ‘=’ sign in that cell or in the formula bar

3.   Type ‘COUNTA(‘ or type COU, a drop down list will appear, select COUNTA, press TAB button

4.   Select the range or alternatively you can type the range as we do in the basic SUM function. Hit enter.

5.   Done.

6.   This is simple. Now to select multiple columns in a single function you need to put a comma then repeat the step 4

COUNTA Function

Where to use COUNTA ?

Examples of COUNTA function :

Everybody needs a tracker to maintain tasks. We first schedule our tasks and then complete these one after another and when one task is completed we mark a tick or ‘OK’ or some other notation to track how much closer we are to complete the overall task.

Here I have given a fictitious task tracker of a day, if any task is completed I mark it as ‘Done’.

COUNTA Function

We can use COUNTA nested with other functions like INDEX or SUMPRODUCT, we will discuss it in another article.

I hope this article is informative & you enjoyed this article.

Thank you for reading…

Find & Replace - Search By Color Format and Wildcard Search

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 use Find 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…

Selection of Proper Chart - Trend Line - Customize Charts

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…