Unhide A single Hidden Rown or Column in MS Excel with Example.

How to Unhide A Single Hidden Row Or Column in Excel Worksheet with Example.

There is question here that is there an easy way or way to unhide a single column when multiple columns are hidden?

The Answer is Yes, there is an easy way.

If an excel user have a multiple rows or columns hidden and he/she needs to unhide just one. Some people unhide all hidden columns or rows and then re-hide all except the one they want. So there is no need to adopt such a long method, Here is an easier way...

To unhide a single column:-

1) In the Name box*, type a cell reference (e.g. D5) for any cell in the hidden column and press Enter. This will select that cell even though it is in a hidden column;

*(the white space to the left of Formula Bar)

Unhide a single row or column in Excel

2) Now from the Home tab, click the Format command, point to Hide & Unhide, then Unhide Columns.

To unhide multiple hidden columns:-

In Step 1 above, type a reference to the columns in the Name box.For contiguous columns, enter the top left and bottom right cells separated by a colon (e.g.. D5:E5).

For non-contiguous columns, enter a cell from each column you want to unhide separated by a comma (e.g. B5,G5).



Related Keyboard shortcuts:-

F5 opens the Go to dialog (can be used instead of Name Box to enter cell reference)

CTRL+9 to hide selected rows

CTRL+0 to hide selected columns

CTRL+Shift+( to unhide selected rows

CTRL+Shift+) to unhide selected columns

NOTE:- This Shortcut doesn't work in Excel 2013

Read More

Print Files directly from File Explorer.

How to Print Files Directly From File Explorer (#A.K.A. Windows Explorer) / How to Print Files without opening it?

#Also Know As (A.K.A or AKA)

Did you know that you don't have to open a file to print it?
There is a hidden trick by which you can save a significant amount of time.

There is a quick little known trick that lets you to print a document directly from File Explorer (formerly known as Windows Explorer prior to Windows 7) without having to first open its associated program first.

What you need to just simply right-click on the file in File Explorer and select Print from the popup menu.

Lets say you have to print reports for all 50 sales reports in your company. Rather than open each spreadsheet separately and then print, you simply go to File Explorer, hold down the CTRL key while selecting each file you want to print. Right-click and click Print.
 
There can be a question in your mind that how it works?

The way this works is, Windows opens the application associated with each file, prints the file(s), and then immediately closes (usually) the application.

Print Files Directly from Window Explorer.

Note:- If you select multiple file formats / types (e.g. Excel and PDF) at the same time, the Print option will not appear on the right-click menu. So select only same format files or same file types.

Read More

Use of SUBSTITUTE function/Formula with Example in Microsoft Excel.

Use of SUBSTITUTE function/Formula with Example in Microsoft Excel.



SUBSTITUTE is a great function/Formula in MS Excel that you may not have aware of, but now that you are about to learn, you will probably find everyday uses for it in Data Analysis.

In Microsoft Excel, you use the SUBSTITUTE function/Formula when you wish to replace specific text in a text string.

There are three pieces of information that are required for the SUBSTITUTE function/Formula in which a fourth is optional.

SUBSTITUTE SYNTAX:-

SUBSTITUTE(text,old_text, new_text, instance_num)
 

Text is the text string or cell containing text for which you want to substitute characters.
 

Old_text is the text you want to replace.
 

New_text is the text you want to replace it with.
 

Instance_num [Optional] specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

Examples of SUBSTITUTE Function:-

Example 1:
Replace the 2nd occurrence of - (hyphen) with / (Slash)

Formula:   =SUBSTITUTE("ABC-123-456-789","-","/",2)
Result:      ABC-123/456-789

Example 2:
In "ABC-123-456-789" replace all occurrences of - (hyphen) with / (Slash)

Formula:   =SUBSTITUTE("ABC-123-456-789","-","/")
Result:      ABC/123/456/789

Because the instance number (i.e. 4th piece of information) is omitted from the function/Formula, Excel replaces every occurrence.

Example 3:
Something that you may need to do is replace text with nothing. In that case, use two double quotes (e.g. "") in the 'with this text' section. I frequently need to do this when I import account numbers, that include dashes, from my accounting system but need to use them without the dashes in Excel.

Formula:   =SUBSTITUTE("T-01-02-740-65","-","")
Result:      T010274065

You could also accomplish this with Find & Replace but you would need to do this every time the data is updated. Also, there will be times when you don't want to affect the original cells.

Substitute Function Excel

Example 4:
As with most other things in Excel, you can use cell references to supply information to SUBSTITUTE function/Formula.

In the previous example if your account numbers are in column B starting at row 10, you can enter =SUBSTITUTE(B10,"-","") in cell C10 and copy the formula down the column.

This is just a small taste of how the SUBSTITUTE function/Formula can be used for everyday applications.

Like most of these tips, the more you practice using this, the more ways you will think of to use it.

Read More