© Roy Cox (2005 - 2020)

Quick Tips

Making Excel work for you.

Remove The Personal Information Warning

1 . Go to File in the upper left hand cornerof the Excel Application 2 . Choose Options -> Trust Center -> Trust Center Settings -> Privacy Options 3 . Un-check the check box that says "Remove personal information from file properties on save", 4 . Click OK.

Protect Your VBA Code

1 . To prevent unauthorised access to your VBA code you can add a password to the VBA Project to only allow access to it if the user has the password. Remember this is not 100% secure, but will prevent most users from viewing or altering your code. 2 . Open the Visual Basic Editor from the Developer Tab or Alt+F11. 3 . In the Project Explorer window (usually the left vertical pane in the VBE), select the VBA Project you want to protect. You will see your workbook in bold type like this VBAProject (YourFileName.xlsm). 4 . Click on Tools _> VBAProject Properties 5 . Select the Protection tab 6 . Choose "Lock project for viewing" 7 . Enter & confirm your password in the fields provided, and click OK. Note: You must close your workbook and open it for this to take effect. When you next open the workbook your modules will be password protected.

Zoom In or Out with your mouse

There are times when you may want to be able to quickly zoom in and out in your Excel worksheets. This is a handy way simply using your mouse: Hold down the Ctrl key and use the scroll wheel of your mouse to zoom in and out. You can also use the zoom bar on the right bottom of the page, but I think the mouse is more convenient. If you have tried copying and pasting formulas from one workbook to another you will have noticed that Excel generates links between the two workbooks. These links between worksheets call the original workbook for the formula you need in the present workbook. To get rid of them, you need to break the links, or find and delete them manually. Here’s a very simple way to avoid this. 1 . Select the formulas that you want to copy. 2 . Use Ctrl+ F to open the Find & Replace Dialog 3 . Replace “+” with a different character, one not generally used in a formula. e.g. #. 4 . Now Copy the formulas and paste to the new workbook. 5 . Use Find and Replace to change the character back to “=” 6 . Close the first workbook without saving The formulas in the new workbook will not contain any links

The Quick Access ToolBar(QAT)

The Quick Access Toolbar is a ToolBar that appears either above or below the main Ribbon tabs in Excel, PowerPoint and Word. The QAT gives quick access to a set of frequently used commands in a toolbar that is always visible no matter which Ribbon tab is selected. By default the QAT only contains 4 buttons but you can easily customise it by adding buttons for your most frequently used commands. The pre- set commands are Save, Undo, Redo, Mouse/Touch Mode Toggle (only for touch screen devices) There are four main advantages to using the Quick Access Toolbar: 1 . Quick access to most used commands. Using the Quick Access Toolbar for your commonly used commands saves a lot of time otherwise wasted swapping between ribbon tabs or navigating drop-down menus. 2 . Add shortcuts to commands. The Quick Access Toolbar commands automatically have the shortcuts ALT+1, ALT+2,.., ALT+9 assigned to the first 9 commands in the toolbar. You can use these commands with the Alt + Number(s) keyboard shortcut. Click the Alt key to see the numbers associated with the commands. 3 . Show hidden or difficult to find commands in the Quick Access Toolbar . Some commands are difficult to find in each of the programs, e.g. within subgroups of ribbon tabs, in submenus of Windows or Ribbon Tabs, or not shown at all. 4 . Run macros or a custom command. You may have custom macros that automate your work processes, and the Quick Access Toolbar is the ideal place to add a custom for your macros.

Customise the Quick Access Toolbar

If you don’t like the default location above the ribbon then you can move the Quick Access Toolbar below the ribbon. 1 . Click on the small down arrow to the right of the Quick Access Toolbar to open the drop down menu. 2 . Select Show Below the Ribbon from the bottom of the menu. 3 . Now the Quick Access Toolbar displays underneath the ribbon. You can add commands to your Quick Access Toolbar and you’re not restricted only to commands that appear in the ribbon, you can also add commands that don’t appear in the ribbon. 1 . Click on the small down arrow to the right of the Quick Access Toolbar to open the drop down menu. 2 . There is a selection of 12 common commands, including the 4 default commands, that can be added to the Quick Access Toolbar by selecting them off in this menu. 3 . To choose from all of Excel’s available commands select More Commands in this menu. 4 . Next select an option from the Choose commands dialog displayed from the drop down menu. Commands are organised in several useful groups, or you can choose to view All Commands. 5 . Select a command which you want to add to the Quick Access Toolbar. 6 . Press the Add button. Note: You can also remove commands with the Remove button. 7 . Use the Up or Down arrow to rearrange the order of the Commands in your toolbar. 8 . Press the OK button.

Removing a command from the Quick Access Toolbar.

1 . Right click on the command you want to remove. 2 . Select Remove from Quick Access Toolbar from the menu.

Quickly Convert Dates Formatted as Text

Many programs, including the one that I use for my daily job, export data with dates formatted as Text. This is problematic because you cannot use such dates in PivotTable, AutoFilter and formulas. You can usually tell that a number or date is incorrectly formatted as text because it will be aligned to the left. The default alignment for text is left and numbers and dates are aligned right. The quickest way that I have found to convert these dates to functional date vallues is to use Excel’s Text To Columns. 1 . Select the column of dates in your Excel sheet 2 . Find the Text To Columns button in the Data Tab of the Ribbon. 3 . Click this to display the Text To Columns Wizard 4 . In the Wizard choose ‘Delimited’ then click Next 5 . Next uncheck all Delimiters. Click Next 6 . Finally,Choose 'Date' from the 'Column Data Format' options and choose your date format from the drop down list, and then click the Finish button. The dates will be converted to date values that you can now use in your formulas, PivotTables, etc. These dates will now also Group, which you cannot do with text formatted dates.

How to copy a formula in Excel between workbooks without links

Find = replace with # Customise the QAT

© Ut duis incididunt ex officia

Quick Tips

Quick Tips

Logotype

Remove The Personal Information

Warning

1 . Go to File in the upper left hand cornerof the Excel Application 2 . Choose Options -> Trust Center -> Trust Center Settings -> Privacy Options 3 . Un-check the check box that says "Remove personal information from file properties on save", 4 . Click OK.

Protect Your VBA Code

1 . To prevent unauthorised access to your VBA code you can add a password to the VBA Project to only allow access to it if the user has the password. Remember this is not 100% secure, but will prevent most users from viewing or altering your code. 2 . Open the Visual Basic Editor from the Developer Tab or Alt+F11. 3 . In the Project Explorer window (usually the left vertical pane in the VBE), select the VBA Project you want to protect. You will see your workbook in bold type like this VBAProject (YourFileName.xlsm). 4 . Click on Tools _> VBAProject Properties 5 . Select the Protection tab 6 . Choose "Lock project for viewing" 7 . Enter & confirm your password in the fields provided, and click OK. Note: You must close your workbook and open it for this to take effect. When you next open the workbook your modules will be password protected.

Zoom In or Out with your mouse

There are times when you may want to be able to quickly zoom in and out in your Excel worksheets. This is a handy way simply using your mouse: Hold down the Ctrl key and use the scroll wheel of your mouse to zoom in and out. You can also use the zoom bar on the right bottom of the page, but I think the mouse is more convenient. If you have tried copying and pasting formulas from one workbook to another you will have noticed that Excel generates links between the two workbooks. These links between worksheets call the original workbook for the formula you need in the present workbook. To get rid of them, you need to break the links, or find and delete them manually. Here’s a very simple way to avoid this. 1 . Select the formulas that you want to copy. 2 . Use Ctrl+ F to open the Find & Replace Dialog 3 . Replace “+” with a different character, one not generally used in a formula. e.g. #. 4 . Now Copy the formulas and paste to the new workbook. 5 . Use Find and Replace to change the character back to “=” 6 . Close the first workbook without saving The formulas in the new workbook will not contain any links

The Quick Access ToolBar(QAT)

The Quick Access Toolbar is a ToolBar that appears either above or below the main Ribbon tabs in Excel, PowerPoint and Word. The QAT gives quick access to a set of frequently used commands in a toolbar that is always visible no matter which Ribbon tab is selected. By default the QAT only contains 4 buttons but you can easily customise it by adding buttons for your most frequently used commands. The pre-set commands are Save, Undo, Redo, Mouse/Touch Mode Toggle (only for touch screen devices) There are four main advantages to using the Quick Access Toolbar: 1 . Quick access to most used commands. Using the Quick Access Toolbar for your commonly used commands saves a lot of time otherwise wasted swapping between ribbon tabs or navigating drop- down menus. 2 . Add shortcuts to commands. The Quick Access Toolbar commands automatically have the shortcuts ALT+1, ALT+2,.., ALT+9 assigned to the first 9 commands in the toolbar. You can use these commands with the Alt + Number(s) keyboard shortcut. Click the Alt key to see the numbers associated with the commands. 3 . Show hidden or difficult to find commands in the Quick Access Toolbar . Some commands are difficult to find in each of the programs, e.g. within subgroups of ribbon tabs, in submenus of Windows or Ribbon Tabs, or not shown at all. 4 . Run macros or a custom command. You may have custom macros that automate your work processes, and the Quick Access Toolbar is the ideal place to add a custom for your macros.

Customise the Quick Access

Toolbar

If you don’t like the default location above the ribbon then you can move the Quick Access Toolbar below the ribbon. 1 . Click on the small down arrow to the right of the Quick Access Toolbar to open the drop down menu. 2 . Select Show Below the Ribbon from the bottom of the menu. 3 . Now the Quick Access Toolbar displays underneath the ribbon. You can add commands to your Quick Access Toolbar and you’re not restricted only to commands that appear in the ribbon, you can also add commands that don’t appear in the ribbon. 1 . Click on the small down arrow to the right of the Quick Access Toolbar to open the drop down menu. 2 . There is a selection of 12 common commands, including the 4 default commands, that can be added to the Quick Access Toolbar by selecting them off in this menu. 3 . To choose from all of Excel’s available commands select More Commands in this menu. 4 . Next select an option from the Choose commands dialog displayed from the drop down menu. Commands are organised in several useful groups, or you can choose to view All Commands. 5 . Select a command which you want to add to the Quick Access Toolbar. 6 . Press the Add button. Note: You can also remove commands with the Remove button. 7 . Use the Up or Down arrow to rearrange the order of the Commands in your toolbar. 8 . Press the OK button.

Removing a command from the

Quick Access Toolbar.

1 . Right click on the command you want to remove. 2 . Select Remove from Quick Access Toolbar from the menu.

Quickly Convert Dates Formatted as

Text

Many programs, including the one that I use for my daily job, export data with dates formatted as Text. This is problematic because you cannot use such dates in PivotTable, AutoFilter and formulas. You can usually tell that a number or date is incorrectly formatted as text because it will be aligned to the left. The default alignment for text is left and numbers and dates are aligned right. The quickest way that I have found to convert these dates to functional date vallues is to use Excel’s Text To Columns. 1 . Select the column of dates in your Excel sheet 2 . Find the Text To Columns button in the Data Tab of the Ribbon. 3 . Click this to display the Text To Columns Wizard 4 . In the Wizard choose ‘Delimited’ then click Next 5 . Next uncheck all Delimiters. Click Next 6 . Finally,Choose 'Date' from the 'Column Data Format' options and choose your date format from the drop down list, and then click the Finish button. The dates will be converted to date values that you can now use in your formulas, PivotTables, etc. These dates will now also Group, which you cannot do with text formatted dates.