5 Tips Before Starting to use the Templates (with Pivot Tables)

Posted on

Excel templates from indzara.com use certain approaches to keep the files simple to use, but at the same time very effective in solving real life needs. We design them such that even someone new to Excel can start using very quickly and benefit. We also keep it flexible so that an intermediate or expert Excel user can easily extend the functionality by adding fields or calculations.
Before using the templates, it will be helpful to know these 5 important things. They are very simple and easy to follow.

 

VIDEO DEMO

 

1. Do not edit calculated cells

Each template has several calculated fields and most are protected from editing. However, some are not protected. It is strongly recommended that these fields are not edited as it would change the calculation logic.

Tip 1: Do not edit Calculated cells
Tip 1: Do not edit Calculated cells

These fields can be easily identified by green colored headers. When you click on such a calculated cell, you will see a formula in the formula bar at the top.

2. Input Data is always visible & can be edited easily

In most business applications, we may have a form where we enter data and click submit to push that data to the database behind the scenes. Until you click Submit or OK, nothing is registered. In our templates, we use a different approach. You are directly entering data in the database which is nothing but Excel Table. This saves a lot of time, but adds more responsibility to the user to make sure that they don’t unintentionally edit any data that was previously entered.

Tip 2: Input Data is Live
Tip 2: Input Data is Live

Since calculations are real-time and dynamic, if you make any changes to an old order, it will immediately update all dependent calculations.

3. Backup by saving file regularly

Microsoft Excel has its default data recovery process if the Excel application crashes. Other than that, there is no special backup system in this file. This is a simple regular Excel workbook. So, please save regularly (keyboard shortcut Ctrl+S) as you are working with it, so that your changes are not lost.
Also, have a regular back-up process. For example, you can save a copy of the file every week and name it with the week number or week end date.

Tip 3: Save and Backup regularlyTip 3: Save and Backup regularlyTip 3: Save and Backup regularlyTip 3: Save and Backup regularly
Tip 3: Save and Backup regularly

You will continue to use the Main file as the default working file, but if you delete that file by mistake, you can be assured that you have your weekly copy saved. You could keep only the last few weeks’ files and keep deleting the older copies as needed.

 

4. Do not rename fields used in Pivot Tables

Some Templates use Pivot Tables for calculations and in such templates, please do not rename fields used in Pivot Tables. Such fields are highlighted and alternative methods are provided to ‘rename’ the fields as needed.

Do Not rename fields used in Pivot Tables
Do Not rename fields used in Pivot Tables

 

5. Refresh Calculations when Pivot Tables are used

In templates that use Pivot Tables, after we enter/edit data, we need to refresh so that calculations are updated. Otherwise, the calculations will not reflect the data input and thus lead to misleading conclusions. Refreshing is very easy. Press Ctrl+Alt+F5 or choose Refresh All from the DATA ribbon.

Tip: In some computers, the function key F5 is locked for other actions and thus Ctrl+Alt+F5 may not work until you go and update your settings in Windows control panel.

Refresh Calculations by Ctrl+Alt+F5 or DATA -> Refresh All
Refresh Calculations by Ctrl+Alt+F5 or DATA -> Refresh All

Permanent Link to post any comments: https://indzara.com/faq-items/5-tips-starting-use-templates-pivot-tables/