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

How to Freeze Panes for Wide Spreadsheets in MS Excel?

An Alternative trick To Freeze Panes For Wide Spreadsheets.

When using wide spreadsheets, you will often need to work with data on the far right of the sheet but also keep visible the related data on the left. You may already know that a good solution for this is Freeze Panes. With Freeze Panes, you select the column to the right of the one you want to freeze and, from the View tab, click Freeze Panes, Freeze Panes. Now you can scroll to the right while the frozen column remains visible on the left.

Freeze Panes is a good solution but is not without its drawbacks.

We often find that when we are moving around on the right side of my worksheet using the arrow keys, we sometimes scroll too far to the left and our data on the right scrolls off the screen. Then we need to scroll to the right again. This can get aggravating when it happens over and over.

Freeze Panes

One simple solution / way is to temporarily hide the columns that we are not using.

Another solution that works great for worksheets where we regularly need to hide (and unhide) specific columns is to use Grouping. Grouping is normally used for hiding the details of subtotaled data but, in some cases, it can work great for quickly hiding and unhiding columns.

This would also work for hiding and unhiding specific rows but in most cases.

How to hide specific column(s) using Grouping in MS Excel.

**Note that if you want to hide multiple columns, the columns for each grouping must be contiguous...

1) Select the (contiguous) columns (e.g. Columns D:Q) you want to apply Grouping to;

2) From the Data tab click Group. An outline bar and [-] symbol appears above the columns. When you click the [-] symbol, the columns will be hidden and the symbol changes to a [+];

3) You can select other (contiguous) columns to apply grouping to.

Once you have finished grouping your columns, you will have a simple and quick way to quickly hide and unhide these columns whenever you need to.

To permanently remove the Grouping (and outline symbols) from a column or group of columns, select the columns and, from the Data tab, click Ungroup.

Read More

How to Change Default Open/Save To Location of Excel with Example.

Change Excel's default Save to/Open Location.

If you want to put a specific location to Open or Save your Excel spreadsheet/File, you can do it which is very simple. There is a way to change the default location you get when you open or save workbooks in Excel. In the corporate world / Professional field or work, almost every people from us save our workbooks to a network drive rather then the My Documents folder on our PC's hard drive. So, if anyone would like to save his / her excel files on a network drive or somewhere other than the default My Documents folder, here is a solution in less than a minute.

When a person start Excel and open a file, by default, Excel first looks in the your My Documents folder. If he / she store most of your files in a different folder, they have to browse to it each time you open a file. When he / she save a new file or an existing file, Excel again defaults to your My Documents folder.

Now-a-days, so many of us are working on corporate networks that, more often than not, we open our files and save our files to a network folder. If you would like to change the folder location that Excel defaults to, there is a simple solution which very quick also.

1) In Excel 2010, from the File tab click Options. In Excel 2007, click the Office Button and then click Excel Options;

2) In the Excel Options dialog, click Save (on the left side) and look for the 'Default file location' field in the 'Save workbooks' section;

3) Type or paste the path of the folder you want to use as the default folder in the 'Default File Location' field;

4) Click OK.

Change Default Location

How to copy and paste a folder path in MS Windows?

If path of the new folder is very long, there's no need to type it all and take a chance on
misspelling it. Here's a trick you can use instead:

1) Open Windows Explorer, browse to and select the folder you want to use for your default Open/Save location;

2) Highlight the path in the Address Bar and press CTRL+C to copy it. (Note that Windows 7 doesn't show the path until you click in the blank area on the right end of the Address Bar. In Windows XP, if the Address Bar isn't displayed, from the View menu, select Toolbars, then Address Bar);

3) Switch back to Excel, click in the 'Default File Location' field and press CTRL+V to paste it.

Read More

How to Fix Wrongly Formatted Dates Quickly in MS Excel Spreadsheets with example.

Fix / Correct Wrong formatted Dates very quick in MS Excel. How to use Text to Column in Excel with Example.


Sometimes when we ever copy or import data into Excel from another source and we find that the date formats are all wrong. Take this example, your computer may recognize dates in mm/dd/yy format and you have just copied data from another source where the date format was dd/mm/yy.

So when you download, copy or insert data from another date format and open it in Excel, Excel doesn't recognize the dates. Here is an easy trick that allows you to quickly convert wrongly-formatted dates into a format you can work with.

In the given picture You'll notice that Excel recognizes some of the dates but for others it doesn't. Notice that the dates that Excel has recognized are right-aligned in the cells and those it didn't recognize, and assumed was text, are left-aligned. If you look closely you'll see that even the cells Excel did recognize as dates are sometimes wrong - the months and days are switched.

Date Format

It can be quite a task to fix all of these dates. There can be various techniques to solve this problem, but except this technique all of them are very time consuming. This is a simple little trick using Excel's 'Text to columns' option.

If you find yourself with a column of wrongly formatted dates Here's the method you can use...

1) Highlight the cells (only one column wide) containing the dates. You can select the entire column if you like;

2) On the Data tab click Text to Columns in the Data Tools group;

3) In the 'Convert Text to Columns' dialog, select the 'Fixed width' option and click the Next> button;

4) If there are any column break lines (vertical lines with arrows) through the data area, double-click each of them to remove them and then click the Next> button;

5) In the 'Column data format' section, you will see a dropdown next to the Date option, select the date format of the imported data (not the format you want to convert it to). So if your computer uses a month/day/year format (e.g. USA) and the dates you need to fix are displayed as day/month/year (e.g. UK/Canada), you will need to select the DMY format and Excel will convert them to the default date format of your computer. If your computer uses the dd/mm/yyyy format and you import US dates, you will have to choose MDY. Other formats such as YMD are also available if you import data that matches that format (e.g.20120531);

6) Click the Finish button.

Just like magic, Excel reads all of the imported MDY dates and converts them to DMY format (or DMY to MDY format). All of those dates have been fixed in just a few seconds.

Now, this one is the cool and fast way to do such a little but typical thing.

Read More

Restore R1C1 Style Cell Referencing To Pivot Tables in Excel.

Reset R1C1 Style Cell Referencing To Pivot Tables in MS Excel Spreadsheet With Example.
 

When you refer to a cell in a Pivot Table in Microsoft Excel, you will see a GETPIVOTDATA formula instead of a normal R1C1 style cell reference by default (e.g. =D5) as shown in below image.

GetPivotData

You can easily turn off this GetPivotData feature, If you want to use normal cell referencing.
 

WARNING! A major disadvantage of using R1C1 cell referencing is R1C1-style references does not adjust if the Pivot Table layout changes.

How to enable RICI Referencing in MS Excel Pivto Table? or How to disable / turn off the GetPivotData Function in Excel?


To enable R1C1 referencing follow the below steps...

1) Click any cell in the PivotTable and from PivotTable Tools click the Options tab;

2) In the PivotTable group, click the small arrow on the right side of the Options command. You will see a checkmark next to the 'Generate GetPivotData' option;

3) Click the 'Generate GetPivotData' to turn the option off. Note that this is a global setting that will remain off for all PivotTables until you turn it on again.

GetPivotData option On / Off

Now you can reference data in PivotTables using normal R1C1-style referencing with the Generate GetPivotData option turned off.

How to turn the option GetPivotData back on in MS Excel Pivot Table?

R1C1 Option disable

To turn the option back on, click the 'Generate GetPivotData' option again.

Remember :- when you switch bewteen GETPIVOTDATA and R1C1, previously created references to the Pivot Table do not change.

Read More