Unhide and hide columns in Excel

It can be difficult for one to focus on a spreadsheet when it contains hundreds or thousands of rows and columns. Sometimes, you may only want to present the critical component of the data in the spreadsheet which only resides in some columns or rows. One way to improve the readability of the spreadsheet is to hide some of the rows or columns that contain less critical information.

This article will walk you through how you can show or hide columns and rows and also some of the common problems faced by unhiding columns in Excel.

How to show or unhide columns and rows in Excel?

Let's use the example below, a dataset showing the different vitamin sales for the different quarters of the year 2022. 

Say if you want to perform two tasks:

- first, to hide columns B, C and D, showing only Q4 and the Total column

- second, to hide Vitamin A and Vitamin B which resides in row 3 and row 4.

I will show you three methods of how you can achieve this.

Using the context menu

To hide columns using the context menu :

Step 1: Click and highlight columns B, C, D

Step 2: Right-click and select Hide from the context menu

To unhide columns, you can perform the following :

Step 1: Click and highlight on either side of the hidden columns

Step 2: Right-click and select Unhide from the context menu

To hide rows using the context menu :

Step 1: Click and highlight rows 4 and 5

Step 2: Right-click and select Hide from the context menu

To unhide rows, you can perform the following :

Step 1: Click and highlight on either side of the hidden rows

Step 2: Right-click and select Unhide from the context menu

Using shortcut keys

To hide columns using shortcut keys :

Step 1: Click and highlight columns B, C, D

Step 2: Press Ctrl + 0 on your keyboard (at the same time)

To unhide columns using shortcut keys :

Step 1: Click and highlight columns B, C, D

Step 2: Press Ctrl + Shift + 0 on your keyboard (at the same time)

To hide rows using shortcut keys :

Step 1: Click on rows 4 and 5

Step 2: Press Ctrl + 9 on your keyboard (at the same time)

To unhide rows using shortcut keys :

Step 1: Click on rows 4 and 5

Step 2: Press Ctrl + Shift + 9 on your keyboard (at the same time)

Using the Group button on the Data tab

To hide columns using the Group button :

Step 1: Click and highlight columns B, C, D

Step 2: Click on the Group button on the Data tab

Step 3: Click on the "-" button to collapse the columns

To unhide the hidden columns, just click on the "+" button

To unhide rows using the Group button :

Step 1: Click and highlight rows 4 and 5

Step 2: Click on the Group button on the Data tab

Step 3: Click on the "-" button to collapse the rows

To unhide the hidden rows, just click on the "+" button

Hiding or unhiding all columns and rows at one go?

If you have hidden a couple of rows or columns, it can be pretty challenging to scroll through the entire spreadsheet to find them and unhide them individually. Luckily, Excel has made it easy for you. 

To hide all rows, 

Step1: Click in the space between the row and column identifiers

Step2: Hover the mouse over the row identifier, right-click and select Hide from the context menu

To hide all columns,

Step1: Click in the space between the row and column identifiers

Step2: Hover the mouse over the columns identifier, right-click and select Hide from the context menu

To unhide all rows, right-click on a row identifier and select Unhide from the context menu

To unhide all columns, right-click on a column identifier and select Unhide from the context menu

Why is unhiding columns in Excel not working at times?

Sometimes, you have overlooked clicking on the row or column identifier before right-clicking the unhide selection on the context menu.

One thing to note is that the shortcut keys shown above may not work for some versions of Windows. To work around this, you can first try to configure the input language hotkey. Take a look at this video at the top of this article to see how you can configure the input language hotkey.

If configuring the input language hotkey does not work,  try using Alt, H, O, U, and L, one key at a time to unhide all the columns.

-Alt : toggle the main Excel ribbon

-H : select the Home tab

-O : select the Format button

-U : select Hide & Unhide

-L : select Unhide Columns

Conclusion

Hope this article helps you to make your spreadsheet easier to scan and less difficult to work with by hiding some cells, rows or columns. 

How to count length of text in Excel

Have you ever wondered if there is a function in Excel similar to the word count function of Microsoft Word? Unfortunately, Excel does not have a function that can do a word count, however, it does have a function to count the number of characters in a text residing in a cell. In this article, I will be covering this function called LEN in Excel.

How LEN function works in Excel?

The LEN function evaluates a text argument and returns the number of characters in that text argument being supplied. 

The function is written as :

=LEN(text)

Say if you have a text cell that contains a person name "David", you can supply this string of text to the LEN function as an argument and the function will return the number of characters in this text string. In this case, the LEN function will return 5.

=LEN("David")    ~ return 5

If the text "David" resides in cell A2, you can also reference the function to cell A2, it will also return 5.

=LEN(A2)    ~ return 5

One thing worth noting is that the LEN function counts the number of characters in the text, including spaces and punctuation. You can think of it as a function that returns the length of a word or sentence in a cell. 

LEN function also counts the number of digits in the cell if it is a number.

Why does the LEN function not work in Excel at times?

There are leading and trailing spaces

If there are leading spaces before the text and trailing spaces after the text, the LEN function will give an incorrect result. 

Take the example below, the correct result should be 19 but instead the function return 23. That is because there are some spaces surrounding the word.

To work around this, you can first use the TRIM function to remove the leading and trailing spaces of the text before using the LEN function. 

The below diagram shows how the formula can be written to result in the correct number of characters counted. In this case, 19 is returned.

There are non-printable characters

Non-printable such as line breaks can cause the LEN function to factor them in during the counting.

To work around this, you can first use the CLEAN function to remove the non-printable, (line break) in the text before using the LEN function. 

The below diagram shows how the formula can be written to result in the correct number of characters counted.

You are working on a date in a text-formatted cell

If you try to use the LEN function on a text cell that contains a date, you may get the result that you weren't expecting. 

Take the diagram below as an example, you should expect the number of characters to be 9 including the backslash, instead the function return 5. 

Let me try to explain this part a little further. Excel evaluate date values as a number, starting from  1. The 1 represents the date 1/1/1900 so a current date like 18/7/2022 is equivalent to 44760. Thus, this is the reason why the LEN function threw out 5 as the number of characters in the text instead of 9.

To work around this, you can first use the VALUE function to convert the data into date format, follow by the TEXT function to convert it into text, and thereafter, you can wrap both functions with the LEN function. 

This way, you can get the LEN function to return the correct number of characters in the text as shown in the diagram below.

You expected the formatting to be counted

The LEN function does not count the formatting of a dollar amount. For example, if the cell has $1000.00 in it, the function will only take into account the number 1000, ignoring the dollar sign, the thousand separators, and decimals.

If your intention is to count all the values in the cell including formatting of the amount, you can write the formula as shown below.

When to use the LEN function in Excel?

LEN function can be used as a standalone function in cases when you want to count the number of characters in a word or sentence in a cell. 

In some other cases, it can be passed as an argument into other functions such as the LEFT, MID and RIGHT functions in Excel.

Take the example below, say if you want to extract only the month into cell C2, you can include the LEN function as an argument pass in the LEFT function.

The LEFT function takes in two parameters, the first argument takes in the text that you would like it to evaluate, and the second argument takes in the number of characters you would like the function to return.

You can first use the :

1. LEN function to find the length of the text;

2. Subtract 5 (this is because the year 2022 together with a space character makes up 5 characters);

3. Include the above two steps as an argument in the LEFT function

The formula can be written as shown in the diagram below. The LEFT function together with the LEN function will give you the partial text "JULY".

Conclusion

By using this LEN function, you will never ever need to count the number of characters in a text string one by one again.

Do check out the video at the top of this article if you would like to see in action what I have shared.

How format painter works in Excel

Have you ever encountered a situation where your boss threw you a task to correct the formatting on a spreadsheet just 15 minutes before an important meeting where the spreadsheet is to be presented? Or have you ever received an ugly spreadsheet from your co-workers when its aesthetic is unacceptable to you? Lucky you, in this article, I will introduce one magical tool in excel to you: the format painter. 

Where is the format painter located?

The format painter is an icon that looks like a paintbrush that can be found in the Home tab, clipboard section of the ribbon of Excel.

How to use the format painter?

Let me use an example to show you how you can use the format painter. In the example below, there are two tables, namely Cluster One and Cluster Two. Both tables show the number of Covid patients grouped into different Covid variants by weekday. The Cluster One table is formatted aesthetically whereas the Cluster Two table is in its raw form. I shall walk you through step by step.

Step 1: Highlight the whole table Cluster One

Step 2: Click on the Format Painter icon

Step 3: Select the cell range in table Cluster Two you would like to apply the formatting, thereafter, release the mouse

And BOOM! Cluster Two table has been formatted.

If there is more than one area of the spreadsheet to apply the formatting to, the above method required you to click on the format painter each time to apply the formatting. 

For now, I will show you how you can use the format painter more efficiently if there is more than a single area of the spreadsheet to apply the formatting to. You can double-click on the format painter once to turn on the format painter mode, thereafter you can continue to apply the formatting to other areas and once you are done, you can turn off the format painter mode.

Here's how :

Step 1: Highlight the whole table Cluster One

Step 2: Double-Click the Format Painter icon to turn on the Format Painter mode. (Note: Mouse cursor should turn into a paintbrush icon.) 

Step 3: Select the cell range in table Cluster Two you would like to apply the formatting

Step 4: Continue selecting the cell range in table Cluster Three you would like to apply the formatting

Step 5: Click on the Format Painter icon to turn off the Format Painter mode

And now, you have the two raw tables formatted.

When to use format painter?

1. Applying consistent formatting for all different tables of a spreadsheet or of multiple spreadsheets.

2. Applying consistent formatting to rows and columns (eg. font typeface, font sizes, colours).

3. Applying consistent width or height of columns and rows.

4. Applying consistent formatting to shapes.

5. Applying consistent formatting to dates.

Why format painter does not work at times?

Format painter will not work on charts in Excel, this is one thing you have to note. You have to use the Paste Special function instead to apply consistent formatting for charts in Excel.

If you are trying to apply format consistency between cells that contain dates, be very careful that all the cell formats need to be in the date form. Format painter won't work if you try to use the format painter on the value that visibly looks like a date but in fact, it is a text format cell.

Hotkeys for format painter?

You can do the following :

Step1: Select the cell or range containing the desired format

Step 2: On your keyboard, press Alt, H, F, P 

Step 3: Click on the target cell or range you want to apply the formatting

One thing to take note of is that the shortcut keys have to be pressed one by one and not all at once.

Conclusion

By using this magical format painter tool, you will never ever find formatting a chore. It allows you to work with your spreadsheet more efficiently. 

Do check out the video at the top of this article if you would like to see in action what I have shared.

Easy way to add multiple columns in excel

Have you been in a situation where you spend hours trying to add up values of multiple columns in excel? Have you ever wonder of ways you can total up these values more efficiently? This blog post will walk you through the different ways you can use to add up values in excel. 

If you are someone who just started on excel, you will be tempted to add up the cell one by one using the addition ("+") operator. For the example, it shows the sales data for a tea company, the total sales for quarter one is achieve by adding cell C4, C5, C6, C7 and C8 using the addition operator. You will then repeat these for the other three columns.

This approach is long and tedious. To be more efficient, you can actually use a SUM() function in excel to sum up the values in the column. You can write the function as such =SUM(C4:C8) where C4:C8 are cell range where the values resides in. 

To get the sum for the four quarter, you can hover your cursor to the bottom right corner of cell C9 and drag towards the right and there you will have the total for the sales for each of the four quarter.

There is an even more efficient method where you can total up the four quarter sales via the use of the AutoSum button in excel. You can do this by first highlighting cell C9 to F9 and click on the AutoSum button in the Editing ribbon of excel. You will have the total for the four quarter. 

Alternatively, you can also press hotkey Alt + = on your keyboard after you highlighted the cell range C9 to F9 to get the total of the four quarter. This will also gives you the same result if you prefer using shortcuts.
You can also check out the video at the start of this article to learn in detail on how to use the SUM function and also the AutoSum button in excel. The video will also highlight some of the shortcoming of using AutoSum in excel.

Count rows in excel with condition

There are five common functions that you can use to count rows or columns that contain data. The functions are COUNT(), COUNTA(), COUNTIF(), COUNTIFS(), COUNTBLANK(). At this point, you might be wondering why five functions should there even be since what we really wanted to do is to just count the rows and columns that contain values. Well, let me take you a quick walkthrough for each of the five functions.

The COUNT() function returns a count of values that are numbers in rows or columns. For example, if you have a worksheet that shows the participants for an event, one column shows that participant name and the other column shows the participant age. You can use the COUNT() to quickly count the number of participants for the event.

Function are written as:

=COUNT(value 1,[value 2]...)

Value 1 can be an item, cell reference or range, Value 2 is optional, it can also be an item, cell reference or range.

The COUNTA() function count everything except empty cells. It count cell that contain values, numbers, text, logical operators, errors and empty text(" "). For ease of remembering, you can take the "A" in the part of this function as "All".

Function are written as:

=COUNTA(value 1,[value 2]...)

Value 1 can be an item, cell reference or range, Value 2 is optional, it can also be an item, cell reference or range.

The COUNTIF() function count cell in a range based on a given criteria. For example, if you are a teacher and you have a spreadsheet that shows all the students in the class with one column showing their name and the other showing their student gender, you can easily use the COUNTIF() function, specifying a gender parameter to quickly count how many of the student are male and how many of the student are female.

Function are written as:

=COUNTIF(range, criteria)

Range refer to the range of cell to count, the criteria refer to the condition you specify.

The COUNTIFS() is similar to the COUNTIF(), the difference is the former function count cell in a range based on more than a single criteria. For example, referencing on the example on students in the class above, you can specify a condition on gender and also another condition on the student nationalities.

Function are written as:

=COUNTIFS(range 1, criteria 2, [range 2], [criteria] 2...)

Range 1 refer to the first range to evaluate, Criteria 1 refer to the condition to specify on Range 1. Range 2 refer to the second range to evaluate, Criteria 2 refer to the condition to specify on Range 2. You can specify multiple range and criteria in this function. 

Lastly, we have the COUNTBLANK() function. This function counts all the empty cell in a range. Formulas that return empty cells are counted too.

Function are written as:

=COUNTIF(range)

The range in which to count blank cells.

Check out the video at the start of this article to learn in detail on the five counting function of excel.

Difference between FIND and SEARCH function in Excel

There are two similar functions in Excel that allow one to search for the position of a character in a text string. The function are the FIND() and SEARCH() functions. They are so similar till one wonder why have two function that perform identical task? However, there are key differences between the two.

The FIND() function is case sensitive whereas the SEARCH() is not case sensitive The other differences is FIND() function does not accept wildcard character whereas the SEARCH() function does.

Both function can search single character, words and symbol within a sentence or text string.

Proper function and it's imperfection in Excel

Upper, Lower and Proper function allow us to deal with messy letter case in Excel.

The Upper() function allows us to convert all lowercase letters in a text string to uppercase.

The Lower() function helps to exclude capital letters in a text string.

The Proper() function makes the first letter of each word capitalized and leaves the other letters lowercase.

There are however some imperfections for the proper functions.

One of the imperfection is that when we try to convert string that contain apostrophe s, it also convert the small letter s to big letter s.

The second imperfection of the Proper function is that when a sentence contain I or II that represent one or two, for example, when we are referencing a movie, Spiderman II, it convert the II into ii.

The third imperfection of the Proper function is that when we are converting text string that contain state code, it will also convert the state code into small letter, example, from California, CA to California ca.

The workaround for these imperfection will be using the Proper function together with the Substitute function.

Check out the video to learn in detail on the Upper, Lower and Proper function.