Category: Srini’s Tech Tips (Page 2 of 6)

Oftentimes, transactions have been captured by a date.  However, when doing summary reports or analysis, we may want to use months.  Some tools such as Pivot Tables in Excel or Crosstab queries in Access can break down the data in months from a date. Even so, there are other instances when we simply need the month names to be extracted into another column.  The TEXT function can produce the month name using the “MMMM” specification.  See the syntax below and the illustration to extract a month name from a date.

NOTE:  If you want an abbreviated month, then use “MMM” (3 M’s, instead of 4).

There are times when you have a data set where the columns and rows need to be switched around for convenience or a better layout, also known as Transposing the Data. Transposing data grants you a better perspective, often leading to greater insight. Handling the data in this way provides the necessary tools for a full analysis.  

Another common circumstance in Excel is that you have too many columns and not enough rows (a wide table), which makes it harder to navigate in the worksheet and to print.  

Consider a situation where you have a workbook with multiple sheets. Imagine having 10, 15 or 20 worksheets where a spelling or name change must be performed.  Such a task would be very tedious if you were to perform the change one sheet at a time. The Find and Replace function has a setting where you can perform the change on ALL instances in the workbook.

In our example below, there are multiple sheets in a single workbook (2020, 2021, 2022 and 2023).  The objective is to change the spelling of “B & B INC.” on all the sheets to “Books and Beyond.”

Steps to Perform (Find and Replace)

1.  Press CTRL + H to get the Find and Replace dialog box

2.  In the Find What field, type the original name to be replaced – in this example, B & B INC.

3.  In the Replace With field, type the new name – in this example, Books and Beyond (case sensitive)

4.  Click on the Options button, which shows more options for replacing

5.  For Within: field, select the dropdown and choose workbook

6.  Leave the two check boxes unchecked
(Reason:  We are not trying to “find” B & B INC. in a particular case, and leaving the match entire cell contents unchecked will look for partial names or entries)

7.  Click on Replace All

8.  You now have a notification that 8 replacements were made, for this example

When analyzing data, sometimes you may want a quick count of certain values (especially if those values occur repeatedly), including how many times an item occurred and a running count next to the value. While many people are familiar with and use COUNTIF and IF functions separately, a “nested” COUNTIF function inside an IF function can be very useful in complex situations.

In addition to this technique of using Nested functions, Conditional Formatting may also be applied to make those values or counts stand out in color, to see what is happening in the data.

(FYI:  CountIF and IF functions are taught in our online Excel level 2 Intermediate course)

Consider a scenario mentioned above where a certain category or value is repeated many times in a data set. Let’s say that you want to count how many times the value appears as well as keep a running total of the count.  In addition to the running total, you want to display what number in the occurrence that the value is in the data set.

The following example demonstrates the use of a Nested COUNTIF function inside an IF function, to perform the count and the running total.

The question:  How many times does the Item value “D” occur in the data set? Also display what number of occurrence the value is next to the Item.

Standard Syntax for CountIF function:  =CountIF(Range,Criteria)

Standard Syntax for IF function:  =IF(Logical Test, Value If True, Value If False)

Nested COUNTIF inside an IF function:  =IF(C8=$F$8,COUNTIF($C$8:C8,$F$8),””)

The Nested CountIF function calculates the value of cell C8, and the number of the occurrence next to value in C8.  When the formula is copied down, the C8 count will be changed to value in C9 (relative reference).

The IF function will test for the value in C8 to the value entered in cell F8 and if it matches the value in C8 the count is performed.  If it does not match the value in cell F8, then it will produce a value of blank/Null (represented by the double quotation marks in the IF function). If the value in F8 is changed to another Item value, then the count is performed for the new value (This is a dynamic way of doing counts by the user).

Answer:
The Value of “D” occurred 3 times and the third occurrence was in cell C17.

**NOTE:  In addition to the count performed by the CountIF and IF functions, the yellow highlight is performed by using conditional formatting. Note the item value “D” is counted and highlighted in yellow.