Find and Replace

I am sure that as an Excel user you must have had many occasions when you had to check whether some text, number or format is available in a sheet or the workbook. Sometimes you might have to replace all instances of existing text with new text. Excel’s Find and Replace feature is available to specifically facilitate this. In this post, we will see some details about this feature and few cool tricks .

What is?

This is a feature in Excel to facilitate finding of cells with specific text, formula, format or comment etc. It is also possible to select any, few or all of such cells. Additionally, you can replace any text in the cell with any other text you want.

How to access?

Excel 2007 or later: Home > Editing Group > Find & Select > Find

Excel 2003 or earlier: Edit > Find

In all the versions, key board shortcut Ctrl+F shows up the same dialogue box.

Let’s see in details various options available in this feature.

Find Tab

This tab can be used when you only need to find text or numbers.

Find what box – You can enter text or numbers that you want to search for (this box is also a dropdown list that contains all the recently searched items, so you can even select from this list)

Options >> - You can further refine / expand your search by clicking on this button which on clicking shows few more options to work with. You will notice that Find what box size is reduced and few more options are available.

               

Clicking Format… shows up another dialogue box (similar to Format Cells box but here it is named as Find Format. You can define the criteria based on number style, alignment, font, border, cell colour or protection etc. Upon clicking on OK in this box, Formats you selected will be shown in Find and Replace box to the left of Format button.

Format… button also has a dropdown button at the end which has few more options.

Format… in the dropdown menu will show the Find Format dialogue box

Choose Format From Cell… will let you select a cell so that it’s format will be considered for search

Clear Find Format lets you clear off any format you already selected

Within box: To search for data in a worksheet select Sheet or select  Workbook to search in entire workbook

Search box: To search for data in rows or columns, click By Rows or By Columns.

Look in box: Select appropriate option to search for data in Formulas, Values, or Comments.

Match case check box: To search for case-sensitive data, select it

Match entire cell contents check box: To search for cells that contain just the characters that you typed in the Find what box, select this.

Find All: Lists down all the occurrences of text / format entered in Find what box.

Find Next: Find and selects the cell that contains the next occurrence of text / format entered in Find what box.

Close: Closes the Find and Replace box without any further action.

Replace Tab

This tab can be used when you need to replace text or numbers. All features discussed above will stay with following additional boxes.

Replace with: This box is similar to Find what, and you need to define what you want to replace with.

Format button: See above.

Replace All: Replaces all the occurrence of text / format in Find what with text / format in Replace with.

Replace: Replaces the first occurrence of text / format in Find what with text / format in Replace with. This selects next available occurrence.

That’s the theory. Now let’s get into the cool part to see few practical uses of this.

Tip 1: Find in Only Particular Region

Select the region you want before accessing Find and Replace feature. If more than one cell is selected, Find and Replace checks only in the selected part. Select any one cell if you want Excel to find in entire sheet.

Tip 2: Select All Occurrences

Find and Replace feature can list down all the occurrences of text, number or format you are looking for. However, sometimes you might want to select all the cells that contain a specific format so that you can change that format for all the cells in one shot. You can use Find and Replace to do this for you. Once you selected the format you are looking for, click Find All and Excel will list all the cells in the dialogue box. Press Ctrl+A to select all of referenced cells in the list. Click Close. Now, your cells are selected.

You can also keep pressing Ctrl and click individual entries with mouse to select only few cells of your choice.

Tip 3: Use Wildcards

Find and Replace supports entry of wildcards. You can use ? (question mark) to search for a single character and * (asterisk) for any number of characters. For example: if you type b?ll in Find what then Excel will search for all words that has b in the start and ending with ll like bill, ball, bell etc. This feature is of particular use if you know the number of characters & any character of what you are looking for.

Using asterisk will give you more flexibility. Example: if you search for b*t, Excel will look for all the words that has b in the start and t in the end like bat, best, bolt, bright etc.

Tip 4: Search for Asterisk or Question Mark

In our earlier tip, we saw that you can use ? and * as wildcards. But what if you want to search for them specifically? Every time you use these characters in Find what box, Excel will think you are using them as wildcards. In this case, you should place a ~ (tilde) before ? or *. This will tell Excel that any character that follows tilde will have to be looked for as it is.

For example, if you are searching for a question mark, you will enter ~? in Find what box.

Happy Learning!!

Unhide All Hidden Sheets

Excel supports hiding multiple sheets in one go but you can unhide only one sheet at a time. As part of our VBA learning, today we are going to code a macro which actually does something useful, unhide all sheets with one action. We are going to use our macro recorder to see what code Excel use to hide / unhide a sheet and expand it to suit our requirement. I hope you remember how to record a macro.

Record Hiding a Sheet

Create a new workbook and save it as macro enabled workbook.

In Excel 2007 or later: Click on View ribbon > Macros drop-down button > Record Macro

In Excel 2003 or earlier: Click on Tools menu > Macro > Record New Macro

• Enter "Macro Name" as HideSheet (any name you want for that matter, just follow rules)
• Click OK.

After clicking OK, you can notice a blue coloured button in Excel 2007 or later and a word "Recording" on the status bar in Excel 2003 or earlier. Now your macro started recording.

Right click on a sheet tab and click on Hide. This will hide the tab. Now right click again and click on Unhide > click on sheet name you have hidden in previous step and click OK. Your macro would have recorded both actions, let’s go and check it out. Before that, stop macro recording by clicking on that blue button.

Press Alt+F11. This should open up the VBE window. (Read about VBE appearance here). In Project Explorer window, you can notice that Excel created a new folder Modules with a plus sign next to it. Clicking on that sign or double clicking on folder name shows you the child item under that folder which is Module1. If you are unable to view Project Explorer window, access it from View menu of VBE window. My recorder shows the below code in Code Window.

Sub HideSheet()
'
' HideSheet Macro
'
 '
    Sheets("Sheet1").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Sheet2").Select
    Sheets("Sheet1").Visible = True
End Sub

We know that the VBA recorder code is not perfect. Let’s first analyse the code to see what it does.

• Sheets("Sheet1").Select selects the sheet.
• ActiveWindow.SelectedSheets.Visible = False changes the visibility of the selected sheets to false i.e. this is the code to hide the sheet.
• Sheets("Sheet2").Select selects the sheet.
• Sheets("Sheet1").Visible = True changes the visibility of the sheet to true i.e. this is the code to unhide the sheet.

Remember, there is no need to select an object in order to work with it. But Excel does while recording which we can totally avoid. So effectively, point 1 and 2 can be combined into one. Point 3 can be totally removed. Let’s see our modified code.

Sub HideSheet()
    Sheets("Sheet1").Visible = False 'hide the sheet
    Sheets("Sheet1").Visible = True 'unhide the sheet
End Sub

Now that we know which object’s what property we need to alter to hide / unhide a sheet, let’s proceed to find out how to do the same on multiple sheets.

Before we continue, you have to learn a bit about Sheets object. Sheets object is a collection of all sheets in the workbook. You can refer any particular sheet in the workbook by accessing it from the Sheets collection. There are multiple ways to refer a sheet from the Sheets object. One way is to call out using the sheet name as in above code snippet.

Sheet(“Sheet1”)

This calls the sheet named Sheet1 no matter where this sheet is placed in set of sheets. Another method is to refer to number of the sheet.

Sheet(1)

This refers to first sheet in the order, no matter what its name is. You can follow both the ways based on your convenience. I use index number, because most of the times you may not know what will be the sheet name.

Now that we know how to refer sheets from Sheets object, let’s see how we can change visibility property for all sheets.

Method 1: Using a Counter

We will use a variable as counter & For Next statement to repeat the macro for all sheets.

Sub UnhideAllSheets()
Dim A As Integer
For A = 1 To Sheets.Count
    Sheets(A).Visible = True
Next A
End Sub

Copy the above code and place it in code window. Run this macro, you will be able to see all the sheets in your workbook visible.

I am using a variable A as an index number to refer the sheet. Also same variable is used to repeat the macro for those many times as the number of sheets in the workbook. Number of sheets in workbook is retrieved by Sheets.Count method. I used For Next statement to loop through.

Method 2: Using For Each Statement

Another way to get this done is to loop using For Each statement. For this we use a variable to contain each sheet.

Sub UnhideAllSheets2()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Sheets
    Sht.Visible = True
Next Sht
End Sub

In above code snippet, we are declaring a variable Sht to contain all the properties of a sheet. Then using For Each statement, we assigned first sheet to Sht. Changing Sht properties now will change first sheet properties. Then the Sht will be assigned with second sheet and its visible property will be changed. This process continues till all the sheets visibility property is changed.

Above utility will unhide all the hidden sheets (including very hidden). Of course, you can customize the macro to unhide only normal hidden sheets or only very hidden sheets or any other criteria you want. Just explore the possibilities. Happy Learning!!

You may also want to read about:

Very Hidden Sheets

Minimise The Ribbon

This article is applicable only to Excel 2007 and later versions where Microsoft Office fluent Interface (aka Ribbon) is implemented. Ribbon replaced the earlier menus and it is quite handy.

There is no way to delete or replace the Ribbon with the toolbars and menus to appear like earlier versions of Microsoft Office. However, you can minimize the Ribbon to make more space available on your screen. When you minimise ribbon, tabs are visible but not the options under those tabs.

Ribbon When Visible

Ribbon When Minimised

Minimising ribbon shows few rows more in the sheet than when it is visible. However, you can still use all the key board shortcuts to do your job. You can also click any tab once when the Ribbon is minimised to get the ribbon temporarily visible.

How to Minimise Ribbon

Easiest Way

Just press Ctrl and F1 together. The Ribbon is gone until you press the same combination again.

Easy Way

With the mouse pointer, double click the active tab name. For example, if Home tab is activated, double click on the word Home. Double click on any active tab again to get the Ribbon back.

Long Way

Click on Quick Access Toolbar (QAT) drop down button.

In the drop down, click on More Commands… In the dialogue box that opens up, select All Commands in Choose commands from dropdown. Locate Minimize the Ribbon in box that is below the dropdown and click Add >>. Click OK.

Now you should be able to see a new button added to your QAT.

Click on the button to minimise the ribbon or make it visible.

VBA Way

Copy the code below to a code window and execute. That should minimise the ribbon. Run again to make it normal.

Sub HideRibbon()
    CommandBars.ExecuteMso "MinimizeRibbon"
End Sub

VBA Way 2

Copy below code and paste in a code window. Go to Excel and run macro from View > Macros (i.e. not from the code window directly)

Sub HideRibbon()
    Application.SendKeys ("^{F1}")
End Sub

Change the Default View of Newly Created Sheet

When you add a new sheet, Excel by default shows the newly created sheet in Normal mode (as opposed to Page Layout view or Page Break view). If you want to change this behaviour and let Excel opens in the mode you want, change the default setting as below.

Excel 2010 and later:

File tab > Options > General > When creating new workbooks segment > select mode you like in the Default view for new sheets drop down. Options available are Normal View (default), Page Break Preview, and Page Layout View.

Excel 2007:

Click the Microsoft Office button > Excel Options > Popular tab > When creating new workbooks segment > select mode you like in the Default view for new sheets drop down. Options available are Normal View (default), Page Break Preview, and Page Layout View.

This feature was introduced in Excel 2007. Users of earlier versions are out of luck here.

Use A Template For Pre-Applied Formatting

If you need to use a specific format for all your files, you can try saving the format you want as a template file. and whenever a new Excel file is created, you can directly apply template rather than making changes manually. For example: if you always want gridlines to be off when you create a new workbook or want to have a standard text for header etc, you can use templates.

One of my friends in billing team spends most of his time preparing invoices in Excel and printing them. He keeps an invoice format in a file on his desktop and opens that each time. He could save some time if he can save that invoice format as a template and keep it in start-up folder so that every time he opens up an Excel file, it opens with his invoice format by default. Let’s see how to do that.

1. Create a new workbook
2. Apply all the formatting you need (cell format, styles, sheet format), data validation settings etc to this workbook
3. Press Ctrl+S
4. In the Save in field, locate XLSTART folder. This folder can be found in one of the following locations depending on your operating system.
• Windows XP: C:\Documents and Settings\<username>\Application Data\Microsoft\Excel\XLStart
• Windows Vista: C:\Users\<username>\AppData\Local\Microsoft\Excel\XLStart
• Windows 7: C:\Program Files\Microsoft Office\Office\XLStart
5.  In the File name field, type Book
6. In the Save As window, Select Excel Template (In Excel 2007 onwards) / Template (In Excel 2003 and below) in Save as type field drop down list.
7. Click OK.

That’s it. From now on, whenever you create a new workbook, it will contain all the formatting you applied in step 2 above.

The ClearType Font

Caution: Techie terms ahead

What is ClearType

ClearType is a technology of showing text in a font. ClearType is owned by Microsoft and was introduced first in 2000.

How it Works

Normally, the software in a computer treats the computer’s display screen as a rectangular array of square, indivisible pixels, each of which has an intensity and color that are determined by the blending of three primary colors: red, green, and blue. However, actual display hardware usually implements each pixel as a group of three adjacent, independent subpixels, each of which displays a different primary color. Thus, on a real computer display, each pixel is actually composed of separate red, green, and blue subpixels. 

If the computer controlling the display knows the exact position and color of all the subpixels on the screen, it can take advantage of this to improve the apparent resolution in certain situations. If each pixel on the display actually contains three rectangular subpixels of red, green, and blue, in that fixed order, then things on the screen that are smaller than one full pixel in size can be rendered by lighting only one or two of the subpixels. For example, if a diagonal line with a width smaller than a full pixel must be rendered, then this can be done by lighting only the subpixels that the line actually touches. If the line passes through the leftmost portion of the pixel, only the red subpixel is lit; if it passes through the rightmost portion of the pixel, only the blue subpixel is lit. This effectively triples the horizontal resolution of the image at normal viewing distances; the drawback is that the line thus drawn will show color fringes (at some points it might look green, at other points it might look red or blue).

ClearType uses this method to improve the smoothness of text. When the elements of a type character are smaller than a full pixel, ClearType lights only the appropriate subpixels of each full pixel in order to more closely follow the outlines of that character. Text rendered with ClearType looks “smoother” than text rendered without it, provided that the pixel layout of the display screen exactly matches what ClearType expects.

Why Are We Bothered 

Microsoft had implemented ClearType font in all MS Office products from 2007 and onwards. This is on by default. If at all you feel your font is rendered bit blurry, you may want to try switching off this feature.

According to a study at the University of Texas, ClearType "may not be universally beneficial". The study notes that maximum benefit may be seen when the information worker is spending large proportions of their time reading text (which is not necessarily the case for the majority of computer users today). Additionally, over one third of the study participants experienced some disadvantage when using ClearType. Whether ClearType, or other rendering, should be used is very subjective and it must be the choice of the individual, with the report recommending "to allow users to disable [ClearType] if they find it produces effects other than improved performance".

How to Disable / Enable ClearType 

Excel 2007:

Click the Microsoft Office button > Excel Options > Popular tab > Top options for working with Excel segment > clear the Always Use ClearType check box.

Excel 2010 and later:

File tab > Options > General > User Interface options segment > clear the Always Use ClearType check box.

Follow the same steps but check the box if you want to enable this feature.

Points to Remember

You will have to close all the Office applications that are open while changing this feature to take effect. 

Microsoft do not recommend to turn off this feature. As a workaround, you can use Microsoft’s ClearType tuning tool. More information can be found here.

Change the Default Font & Size

By default, Excel uses Calibri font with 11 pt size (10 pt if you use Excel 2003) for any new workbook you create. Of course, you can change the formatting for each book / sheet whenever you like. But your company uses a particular font in all the reports (other than Calibri) and you want that font as your default font? Read on.

This post is useful if you want to change the default font and size so that each time you create a new workbook, your chosen font is the default.

Excel 2010 and later:

File tab > Options > General > When creating new workbooks segment > select font you like in the Use this font drop down. Select size of the font you want as default in the Font size drop down list.

Excel 2007:

Click the Microsoft Office button > Excel Options > Popular tab > When creating new workbooks segment > select font you like in the Use this font drop down. Select size of the font you want as default in the Font size drop down list.

Excel 2003:

Tools menu > Options > General tab > select a font in the Standard font drop down box. In the Size box, type or click a font size.

Points to remember:

1. You must restart Microsoft Office Excel to begin using the new default font and font size.
2. The new default font and font size are used only in new workbooks that you create after you restart Excel. Existing workbooks are not affected.

Change Default User Name

Excel by default picks up the system login ID as the user name. All the Excel files you create will be stamped with your login name as author. Also, when you insert a comment, every comment by default starts with the user name.

If you don't like the default logic ID to be your Excel user name, you can change it and put any name you fancy. Let's see how to do it.

Excel 2010 and later:

File tab > Options > General > Personalize your copy of Microsoft Office segment > type a name in the User Name.

Excel 2007:

Click the Microsoft Office button > Excel Options > Popular tab > Personalize your copy of Microsoft Office segment > type a name in the User Name.

Excel 2003:

Tools menu > Options > General tab > > type a name in the User Name.

Points to remember:

1. User Name cannot be more than 52 characters. You can have special characters too in the name.
2.  If you keep user name field blank, Excel automatically places the Windows user name.
3. Changing the user name will effect the future workbooks. Files already saved with earlier user name will not be updated.

Control The Cursor Movement on Pressing Enter

Typically, whenever you press Enter in Excel, cursor will move one box down (there are few exceptions, that’s not the point for this post). Few know that you can command Excel to behave differently upon pressing Enter viz. do nothing, go Up, Left or Right. Let’s see how to do that.

Excel 2010 and later:

No Cursor Movement after Enter

File tab > Options > Advanced tab > Editing options segment > clear the check box After pressing Enter, move selection > OK.

To Move Cursor In a Direction

File tab > Options > Advanced tab > Editing options segment > check the check box After pressing Enter, move selection > and select the direction you want the cursor to move in Direction drop down. Available options are Down, Right, Up and Left > OK.

Excel 2007:

No Cursor Movement after Enter

Click the Microsoft Office button > Excel Options > Advanced tab > Editing options segment > clear the check box After pressing Enter, move selection > OK.

To Move Cursor In a Direction

Click the Microsoft Office button > Excel Options > Advanced tab > Editing options segment > check the check box After pressing Enter, move selection > and select the direction you want the cursor to move in Direction drop down. Available options are Down, Right, Up and Left > OK.

Excel 2003:

No Cursor Movement after Enter

Tools menu > Options > Edit tab > clear the check box Move selection after Enter > OK.

To Move Cursor In a Direction

Tools menu > Options > Edit tab check the check box Move selection after Enter > and select the direction you want the cursor to move in Direction drop down. Available options are Down, Right, Up and Left > OK.

Once you are done with above steps, press Enter in excel and see how the cursor is moving.

Live Preview

Excel 2007 introduced a concept called Live Preview. Using this, you can view how the format change you are about to make actually look like, even before applying that change. This saves you the time you would otherwise spend on applying various formats and removing the applied format if you don’t like it.

You can access this feature by hovering the mouse over desired format options and wait a second, Excel will automatically update the selection to show how that will look like once the highlighted format applied. You just need to click on those options if you fancy or move away from it if you don’t like. That’s all.

Example:

To check different font sizes: Select the cells you want to change font size and Home tab > Font group > click the Font Size box down-arrow, and then move the pointer over different font sizes. Observe the cells you selected, they will change the size as you hover the mouse.

You can preview font formatting, quick styles, picture formatting using this feature. If you are using Excel 2010, you can also preview the Paste commands such as Keep Source Column Widths, No Borders, or Keep Source Formatting.

Though this option is awesome, if you feel that you don’t require live preview, there is an option to turn this feature off. Let’s see how.

Excel 2007:

Click the Microsoft Office button > Excel Options > Popular tab > Top options for working with Excel segment > clear the Enable Live Preview check box.

Excel 2010 and later:

File tab > Options > General > User Interface options segment > clear the Enable Live Preview check box.

Follow the same steps but check the box if you want to enable this feature.

Use Custom Number Formats Across Workbooks

We have been seeing about how to create custom data/time & number formats in a series of posts.  If you follow those codes carefully, by now you must be able to create formats as you require. There is a problem with these custom formats. They will be saved in the workbook you created them in and will not be available for other workbooks. If you spent an hour to create a custom number and want to use it across all Excel books anytime, you will have to save that workbook you have the customer format as a template.

Templates can contain the sheets, default text (such as page headers and column and row labels), formulas, macros, styles, and other formatting you want in all new workbooks you create.

Once you create a workbook with your custom format, save it as a template as below.

• Click on File tab > Save As (Excel 2010) / Microsoft Office Button > Save As (Excel 2007) / File menu > Save As (Excel 2003)
• Save as type box, click Excel Template or Excel 97-2003 Template (Excel 2007 or later) / Template (Excel 2003 or lower)
• Give any name you want to this workbook and click Save.

Next time onwards, whenever you want your custom formatting required in a new workbook, you will have to create a new workbook using File tab > New > New from existing (Excel 2010) / Microsoft Office Button > New > New from existing (Excel 2007) / File menu > New > From existing workbook(Excel 2003).

If you think this is too much to do or want your custom format available whenever you open Excel, you may want to save your template in XLStartup folder or alternate startup folder.

Use AutoCorrect To Expand Your Shorthands

Excel 95 version introduced the AutoCorrect feature. Though this is mostly used to correct the commonly misspelled words, quite a few smart users started using this feature for another use, to expand the shorthand form of sentences. For example: You can type “P Ltd” in a cell and make Excel change it to “Private Limited”. You can set up this using Excel’s AutoCorrect feature.

This feature is on in Excel by default. You can toggle this on and off by following the sequence below.

In Excel 2010 or later

File Tab > Options > Proofing tab > AutoCorrect options > AutoCorrect tab > clear / check the box Replace text as you type

Excel 2007

Click the Microsoft office button > Excel Options > Proofing tab > AutoCorrect options > AutoCorrect tab > clear / check the box Replace text as you type

In Excel 2003 or earlier

Tools menu > AutoCorrect options > AutoCorrect tab > clear / check the box Replace text as you type

Under the check box, you will notice two fields viz. Replace: & With:. This is where you define what to replace and with what. As an example, type P Ltd in Replace: field and type Private Limited in With: field. Click on Add.

Now onwards, anywhere you type P Ltd in any cell, Excel will automatically change it to Private Limited. You can use this feature to expand any shorthand you want.

You can edit or delete any of the replace and with pairs available in Excel AutoCorrect by selecting that pair and clicking on Delete button.

Explore other options available in this dialogue box.

Change Default Number of Sheets in New Workbook

By default, a new workbook contains 3 sheets in it. Later you can add any number of sheets as you like or delete any of the sheet. But If you want to change this default number of sheets from 3 to any other number, follow the below sequence.

Excel 2010 or later

File tab > Options > General tab > type / select number of sheets you want under Include these many sheets

Excel 2007

Microsoft office button > Excel options > Popular tab > type / select number of sheets you want under Include these many sheets

Excel 2003

Tools menu > Options > General tab > type / select number of sheets you want under Sheets in new workbook

Apply Page Setup To Multiple Sheets

Many a times, I adjust the Excel’s page setup default values for borders, header & footer to suit my printing needs. However if I add a new sheet to the workbook, Excel default page setup options will be set for that sheet. I have to change the settings of new sheet to suit my needs. This process repeats for each sheet I add. So, if I want to apply the same page setup to new sheet or few other sheets in the same file, how to quickly do this? You can achieve this by following the steps below.

Select the sheet where you already have your page setup values changed, then press down Ctrl key, click all the other sheets you want this setup to be applied.

In Excel 2007 or later

Click on Page Layout tab > Page setup group > Click on the small arrow that is at the right bottom of the group, this arrow will be under Print Titles button.

If you are using Excel 2010, you can also click on File tab > Print > Page Setup

When the Page Setup dialogue box opens up, just click OK. This applies the first selected sheet’s options to all the Ctrl clicked sheets.

Excel 2003 or earlier

File menu > Page Setup

When the Page Setup dialogue box opens up, just click OK. This applies the first selected sheet’s options to all the Ctrl clicked sheets.

Navigating Through Multiple Sheets Made Easy

You have multiple sheets (more than 10) in a workbook. After few sheets, remaining sheets will start hiding behind the scroll bar as shown in below picture.

Question is, how will you go to the second sheet from the last for example?

I see most of the users clicking the sheet navigation buttons (see the arrow marks on extreme left of the picture above) again and again to go through all the sheets. Or some click on the end buttons to reach ends and click on middle buttons to go to the desired sheets.

Few smart users also use key board short-cuts; Ctrl+PgUp and Ctrl+PgDn buttons to navigate through the sheets.

But there is also a little known trick to navigate through all the sheets and activate the desired sheet in a jiffy. The trick is; just right clicking on any of the navigation buttons. This action shows a popup list of the sheet names with a tick mark against current active sheet. You can click on any sheet name in the list and that sheet will be activated.

Change Default Saving Location

While saving a new workbook or using Save As command, by default, Excel prompts to save all the new workbooks in My Documents folder.

However, many of us don’t always use this folder to save new workbooks. Of course, you can move to the target location in couple of clicks. But if you usually use some other folder to save Excel files, it will be a better idea to directly show that folder in Save As box instead of My Documents. Let’s see how to do that in Excel.

Excel 2010 and later:

File tab > Options > Save tab > Save workbooks segment > Enter entire path of the folder you want in Default file location > OK.

Excel 2007:

Click the Microsoft Office button > Excel Options > Save tab > Save workbooks segment > Enter entire path of the folder you want in Default file location > OK.

Excel 2003:

Tools menu > Options > General tab > Enter entire path of the folder you want in Default file location OK.

That's it. Next time onwards, Excel will prompt to save in above entered path when you open Save As box.

Make sure that the path you are entering exists before entering it in Default file location, otherwise Excel complains.

Very Hidden Sheets

I suppose we all know how to hide and unhide a sheet. If you have a sheet that you wish to keep secret when the file is sent across to people, you will hide that sheet. But receiver of the file can unhide this sheet and view the contents. You can password protect the structure so that users cannot unhide the sheets.

There is one more option that is very little known to Excel front end users. This option comes somewhere in between hiding a sheet and password protecting the book. We are talking about one of the VBA property of sheet i.e. very hidden.

If You Don’t Have Developer Tab Enabled

1. Open up the VBE (you can find all about how to access VBE window from here)
2. In the Project Explorer window, click on the sheet you want to hide
3. In the Properties window, click on Visible, you can see a dropdown next to that field. Click on that dropdown button and you can see 3 options in it. Select third option which is 2 – xlSheetVeryHidden.
4. Go back to Excel and you will observe that the sheet is gone now. If you notice the hidden sheets list (by right clicking on any sheet tab and click unhide), you will not see this sheet.

Normal users will not be able to notice this sheet. But anyone can repeat the above steps and select 1 – xlSheetVisible to make the sheet visible. Selecting 0 – xlSheetHidden will hide the sheet, just like how you do normal hiding.

If You Have Developer Tab Enabled

In Excel 2007 and later, you can enable the Developer tab and view sheet object properties in Excel itself rather than in VBE. See here how to enable Developer tab.

Under the Developer tab, under Controls group, click on Properties. You will get the Properties window, just the one you see in VBE. Repeat step 3 & 4 above to make the sheet very hidden.

Change Comment's Default Formatting

You can insert comments to a cell in Excel. If you don’t like the format of it, you can change it by selecting the comment and right click, choose Format Comment option. But if you want to change the default format of the comment so that you need not change the format every time you insert a comment, follow the steps below.

Excel's comment uses Windows ToolTip settings for its own formatting. So, you have to change the ToolTip settings in order to set comment's default format. This is the sequence in my Windows XP. Similar commands should be available in other OS as well. Try any one of them.

Start > Control Panel > Appearance and Themes > Display (in category view)

Start > Control Panel > double click Display (in classic view)
Right click in any empty space on desktop > Properties

In the Display Properties dialogue box, click on Appearance tab and click Advanced button! In Advanced Appearance dialogue box, under Item dropdown, select Tooltip, adjust the background color, font color, font size, Italic, Bold properties etc here.

Changing Windows ToolTip format will effect all other Windows based programs too.
You may want to read how to insert picture background in comments. 

Always Open a Specific Excel File

Do you work on a specific Excel file every day? Or you have a master file that must be opened every time you work in Excel? It is generally good idea to let Excel open up this file instead of manually opening every time as this will save little effort and time. Let’s see how?

When you open Excel, it will always open the files saved under a particular folder named XLStart. So, save any workbook as you normally save, in this folder. This folder can be found in one of the following locations depending on your operating system.

• Windows XP: C:\Documents and Settings\<username>\Application Data\Microsoft\Excel\XLStart
• Windows Vista: C:\Users\<username>\AppData\Local\Microsoft\Excel\XLStart
• Windows 7: C:\Program Files\Microsoft Office\Office\XLStart

Once saved here, you will get this workbook opened every time you start a fresh instance of Excel.

Tip: To quickly know your start up file path, in VBA Immediate Window, type below line and press Enter.

? Application.StartupPath 

You may want to read about maintaining an alternate startup folder. 

Use an Alternate Startup Folder

We know that Excel always opens up all the files in its XLStartup folder. What if you want to open all the files in some other folder along with start-up folder files? Yes. You guessed it correct, Excel provides that functionality too. Follow the sequence below to get this.

Excel 2010 and later:

File tab > Options > Advanced tab > scroll all the way down to see General segment > Enter path of the folder you want in At startup, open all files in > OK.

Excel 2007:

Click the Microsoft Office button > Excel Options > Advanced tab > scroll all the way down to see General segment > Enter path of the folder you want in At startup, open all files in > OK.

Excel 2003:

Tools menu > Options > General tab > Enter path of the folder you want in At startup, open all files in > OK.

Points to remember:

• Excel will attempt to open every file in the alternate startup folder, make sure you specify an empty folder or a folder that contains only files that Excel can open.
• If a file with the same name is in both the XLStart folder and the alternate startup folder, the file in the XLStart folder opens.