Top Excel Tips and Tricks (HACKS) to Make You a PRO (Free PDF) [PART-9]

(Excel Tip-1) Default File Saving Location

Normally while working on Excel, I create more than 15 Excel files every day. And, if I save each of these files to my desktop it looks nasty. To solve this problem, I have changed my default folder for saving a workbook, and here’s you can do this.

1. First, go to the File tab and open Excel options.
2. In Excel options, go to the “Save” category.
3. Now, there is an input bar where you can change the default local file location.
4. From this input bar, change the location address and in the end, click OK.

From now onward, when you open the “Save As” dialog box Excel will show you the location you have specified.

(Excel Tip-2) Disable/Enable Start Screen

Here are the steps to disable the start-up screen in Microsoft Office.

1. First, go to the File tab and open Excel options.
2. In Excel options, go to the “General” category.
3. From the option, drill down to the “Start-Up” options and un-tick the “Show the Start screen when this application starts”.
4. In the end, click OK.

From now onward, whenever you start Excel it will directly open the workbook without showing the start-up screen.

(Excel Tip-3) Developer Tab

Before you start writing VBA codes the first thing you need to do is to enable the “Developer Tab”. When you first install Microsoft Excel, a developer wouldn’t be there. So, you need to enable it from the settings.

1. First, go to the File tab and click on the “Customize Ribbon” category.
2. Now from the tab list, tick marks the developer tab and click OK.

Now when you come back to your Excel window, you’ll have a developer tab on the ribbon.

(Excel Tip-4) Enable Macros

When you open a macro-enabled file, you need to enable macro options to run VBA codes. Follow these simple steps:

1. First, go to the File tab and click on the “Trust Center” category.
2. From here click on “Trust Center Settings”.
3. Now in “Trust Center Settings”, click on macro settings.
4. After that, click on “Enable all macros with Notifications”.
5. In the end, click OK.

(Excel Tip-5) AutoCorrect Option

If you do a lot of data entry in Excel, then this option can be a game-changer for you. With the auto-correct option, you can tell Excel to change a text string into another when you type it.

Let me tell you an example:

My name is “Puneet” but sometimes people write it like “Punit” but the correct spelling is the first one. So, what I can do is, use autocorrect and tell Excel to change “Punit” into “Puneet”. Follow these simple steps:

1. First, go to the File tab and go to options and click on the “Proofing” category.
2. After that, click on “AutoCorrect Option” and this will open the auto-correct window.
3. Here in this window, you have two input bars to specify the text to replace and text to replace with.
4. Enter both values and then click OK.

(Excel Tip-6) Custom List

Just think like this, you have a list of 10 products that you sell. Whenever you need to insert those product names you can insert them using a custom list. Let me tell you how to do this:

1. First, go to the File tab and go to options and click on the “Advanced” category.
2. Now, drill down and go to the “General” section and click on “Edit Custom List…”.
3. Now in this window, you can enter the list, or you can also import it from a range of cells.
   In the end, click OK.

Now, to enter the custom list you have just created, enter the first entry of the list in the cell and then drill down that cell using the fill handle.

(Excel Tip-7) Apply Table

If you use pivot tables a lot then it’s important to apply the table to the raw data. With a table, there is no need to update the pivot table’s data source, and it drag-down formulas automatically when you add a new entry.

To apply the table to the data just use Ctrl + T keyboard shortcut key and click OK.

(Excel Tip-8) Gridline Color

If you are not happy with the default color of cell gridlines then you can simply change it with a few clicks and follow these simple steps for this:

• First, go to the File tab and click on the “Advanced” category.
• Now, go to the “Display options for this workbook” section and select the color you want to apply.

• In the end, click OK.

Related – Print Gridlines

(Excel Tip-9) Pin to Taskbar

This is one of my favorite one-time sets up to save time in the long run. The thing is instead of going to the start menu to open Microsoft Excel, the best way is to point it to the taskbar.

This way you can open it by clicking on the icon from the taskbar.

(Excel Tip-10) Macro to QAT

If you have a macro code that you need to use frequently. Well, the easiest way to run a macro code is to add it to the Quick Access Toolbar.

1. First, go to the File tab and click on the “Quick Access Toolbar” category.
2. After that, from “Choose Command from”, select Macros.
3. Now select the macro (you want to add to QAT) and click on add.
4. From here click on “Modify” and select an icon for the macro button.
5. In the end, click OK.

Now you have a button on QAT that you can use to run the macro code you have just specified.

Related – How to Record a Macro in Excel