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