Indzara

Support

This page will host materials that will provide general help related to downloading and using the Excel templates available on indzara.com.

If you have questions on specific template, please visit the product page of that template. Each premium template also has a dedicated support page for which you can find the link in the product page. Example for Recruitment Manager template: Product PageSupport Page

Editing Excel Templates

Permanent Link to this article: https://indzara.com/faq-items/adding-rows-to-an-excel-table-in-a-locked-protected-sheet/

In some of our templates, we protect (or lock) the sheets using a password. This is done to prevent the user from accidentally editing/removing the formulas used. This is not done to keep the formulas secret. We give the password to the customer so that they can unprotect and edit if they are familiar with Excel and formulas. The free templates have indzara as password.

In this article, I will demonstrate how to unprotect protected sheets in Excel workbooks. 

In the templates you will find on indzara.com, some sheets are protected and will not allow you to make certain changes. Let me explain why I protect certain sheets.

I build each template with the goal of keeping it very simple and easy for the user to use it and get the task done quickly. In order to achieve that, I write several formulas in one or multiple sheets in the workbook. The functioning of the template depends on these calculations. If by mistake these formulas are modified, the template will not provide the intended result. So, keeping the formulas intact is critical. Excel allows protecting  individual sheets and prevent certain types of changes by the user unless a password is entered. This is a great feature (more from Microsoft Excel Help) and I use it in almost all my templates. This allows me to build very sophisticated calculations behind the scenes and the user does not have to worry about making any modifications to the formulas unintentionally.

For the default functionality, you do not have to unprotect sheets at all. I design the template such that the user can use the template as it is delivered. In certain scenarios, the user is an Excel savvy person, completely understands the formula used and is aware of exact impacts if the formula is modified. If he/she would like to make modifications to the template to suit their unique needs, I encourage it. Hence, I provide the password for each template. (All free templates use the same password indzara) You do not have to use it, but if you want to, you can. When you want to expand the template’s functionality, you can unprotect the sheet using the password, make changes as needed and then protect the sheet again. I highly recommend protecting the sheet again to prevent unintentional editing of formulas.

 

The following are the steps to unprotect a sheet and protect it again.

 

UNPROTECTING

  • Go to the REVIEW ribbon and click on ‘Unprotect Sheet’ button in the ‘Changes’ area.
    Unprotecting Sheets - Excel Templates - Unprotect Sheet
    Unprotecting Sheets – Excel Templates – Unprotect Sheet

     

     

  • Enter Password to unprotect sheet. If you do not know the password, please e-mail.

     

    Unprotecting Sheets - Excel Templates - Enter Password
    Unprotecting Sheets – Excel Templates – Enter Password
  • You can now make any changes you need to make.

 

PROTECTING SHEET AGAIN

 

Go back to the REVIEW ribbon and now click on ‘Protect Sheet’ button.

Protecting Sheets - Excel Templates
Protecting Sheets – Excel Templates

 

Enter the password you would like to use. You can use the same password as before or if you would like to create your own new password, you can do that too. Please make sure to remember the password.

Protecting Sheets - Excel Templates - Enter Password
Protecting Sheets – Excel Templates – Enter Password

 

Leave the checked items as they appear. Items that are checked (or selected) might change from template to template, but you can just click OK as Excel will remember the original selected items (when you received the template).

After clicking OK, Excel will show another dialog box asking you to re-enter password. Please do and click OK.

Protecting Sheets - Excel Templates - Confirm Password
Protecting Sheets – Excel Templates – Confirm Password

Now you have protected the sheet. If any one tries to make certain types of modifications, Excel will ask for a password.

 

If you have any questions about this article, please leave them in the comments below.


Permanent Link: https://indzara.com/faq-items/unprotectingprotectedsheets/

Excel Features

In this tutorial, we are going to see how we can create a simple drop down list in Excel.

Video (How to create drop down list in Excel)

 

If you would like to be notified of these videos, please subscribe to our YouTube channel.

What are drop down lists?

A drop down list is a way to implement data validation and improve data entry in Excel.

  • Optionally, you can provide message to guide the user on data entry.
  • It informs the user what values are allowed
  • It allows the user to choose from a list instead of typing the value.
  • It limits what values can be entered in a cell.
  • Optionally, you can provide an error message when the user does not enter a valid value.

Can we see an example?

Let’s take a simple example.

Sample Optional Input Message
Sample Optional Input Message

In the image above, you can see the message for the user instructing what data is expected in that cell.

What is Drop Down List in Excel
What is Drop Down List in Excel

When click on the downward arrow button, we see John, Mike, and Kate. John, Mike and Kate are the allowed values

User can choose Mike from the list instead of typing the value.

If the user enters a value not in the list, it will not allow. If the user enters an invalid value, there will be an error message.

Sample Optional Error Alert Message
Sample Optional Error Alert Message

Now, we know what a drop down list is, let’s see how we can build this.

How to build a drop down list?

Step 1: Enter list of values

In a blank new sheet, let’s type the following information.

Step 1 - Enter list of values
Step 1 – Enter list of values

First value (Employee Name) refers to the header or field name. Then, we have entered names of three employees.

Step 2: Convert the data to a table

Select all four cells with data and press Ctrl+T

Step 2 - Convert to table
Step 2 – Convert to table

Please make sure that you check the box ‘My Table has headers’.

Once you hit OK, you will see that the table is created.

Step 2 - Table is Created
Step 2 – Table is Created

The Name Box will display Table1. That is the name of our Table.

Step 3: Create a name to our list

Select the list of values and then press Ctrl+F3 to open Name Manager. Or select from the Formulas ribbon as shown below.

Step 3.1 - Create a name for the List
Step 3.1 – Create a name for the List

In the following window, click on New, as we are creating a new Name.

Step 3.2 - Select New name
Step 3.2 – Select New name

In the next window that opens, specify the name we want to give. I usually provide all my lists names that start with L. For example, L_EMP as this is a list of Employee Names.

Step 3.3 - Edit the Name
Step 3.3 – Edit the Name

Step 4: Implement Data validation

We will implement first in a single cell.

Let’s select the cell where we want to apply data validation or drop down list. I am choosing cell E2.

Then select Data Validation from the Data ribbon.

Step 4.1 - Data Validation set up for a single cell
Step 4.1 – Data Validation set up for a single cell

In the following window, choose List for the ‘Allow:’

Step 4.2 - Choose Allow List
Step 4.2 – Choose Allow List

Note: There are different types of data validation that can be done in Excel. We are currently focusing on a simple drop down list of names. Hence, we are choosing ‘List’.

Then, we need to inform Excel where the List is.

Click inside the Source field. Press F3.

Step 4.3 - Select source
Step 4.3 – Select source

Choose our name L_EMP and press OK.

Step 4.4 - Select our list name L_EMP
Step 4.4 – Select our list name L_EMP

You will now see that the Source is L_EMP (our list of employee names). Press OK in the dialog box.

Step 4.5 - Press OK to complete
Step 4.5 – Press OK to complete

That’s it. Now, we have implemented the drop down list in cell E2.

Drop down list is complete
Drop down list is complete

Optional:

A couple of optional steps are  to provide the input message and error alert message. These are recommended if the user who is going to use the file is not familiar with the data and needs direction.

Entering Input Message
Entering Input Message
Entering Error Alert Message
Entering Error Alert Message

Extensions

So far, we have learnt how to implement drop-down list in one cell. Let’s look at a couple of ways to extend this technique further.

More than one cell especially when cells are far apart

You can copy this cell (which has the drop down list) and paste in another cell. The data validation and drop down list will also be copied over. So, if you need the same drop down in different cells (far apart), it is easy. Just copy and paste.

 

Inside Tables

In almost all my templates, I use drop down lists inside tables. For example, we have an Applications table where we must select the Job ID from list of available Job IDs (coming from another sheet). This means that the drop-down list should be enabled for each application. Fortunately, Excel makes it easy. We can follow similar steps as outlined earlier.

Let’s say we have a table (call it DATA table) as shown below with just two columns. Employee Number and Employee Name. We want to implement drop down list for Employee Name column.

Implementing Drop down lists in a Table
Implementing Drop down lists in a Table

Select the three cells in Employee Name column.

Then, follow steps as mentioned earlier to choose L_EMP as source of our data validation list.

Select the List as source
Select the List as source

That’s it.

Drop down list Implemented in Table
Drop down list Implemented in Table

The amazing part of this method is that 1) the list of values can grow over time and 2) the data table cells can also be extended without any issues.
If we enter a fourth row in the DATA table. Type 4 in Employee Number and see that the Employee Name column will have a drop down for the fourth row as well automatically.

Drop down list works in new rows as well
Drop down list works in new rows as well

 

Note of Caution

Drop down lists are not perfect when it comes to data validation. There are ways invalid values might get into the cells. An error icon will appear in such cases.

Error Icon in Drop Down list
Error Icon in Drop Down list

When you click on the cell, you will see the following message.

Drop Down list - Error Message
Drop Down list – Error Message

A couple of reasons for this to happen

  1. If we copy (from somewhere) and paste values in the cell, they don’t return errors and Excel allows the invalid values.
  2. After we implement the drop down list and have entered some values in our DATA table, if we change our List of Values, DATA Table will not update automatically.

Please take care and ensure that the above two scenarios do not happen with your file. If it happens, please fix the data entry and update the cell value to a valid one from the list.


Adding drop-down lists enhances the user experience in Excel templates and I use this technique in all my templates. I hope this tutorial is useful to you.

If you have any questions or feedback, please post them in the comments below.

I use Excel Tables in all of the templates you can find on https://indzara.com. Excel Tables allow storing information and they also make it easy to retrieve information. However, if you are new to Excel or Excel Tables, there are some unique things you need to know in order to work effectively with Excel Tables. In this video, I demonstrate how to enter data and delete data from Excel Tables. I use the Retail Inventory and Sales Manager Excel Template to demonstrate, but the concepts are applicable to all Excel workbooks using Excel tables.

Many of the Excel templates from https://indzara.com use Pivot tables for calculations. In such cases, when input data changes, you have to tell Excel to refresh all the calculations. Otherwise, the output cells/charts will not reflect the input. In this video, I demonstrate how to refresh data in a simple single step. It will refresh all the calculations in the entire Excel workbook.

In this blog post, we will learn how to create a check box form control in Excel, use it to receive user input and store it in a cell.

A sample implementation is below.

Final checkbox appearance
Final checkbox appearance

We want to allow the user to choose the days that are weekend days.

Let’s type Day and Sunday.

If you select the Sunday cell and drag it down to 6 more cells, Excel will automatically populate the other weekdays.

Fill the cells down
Fill the cells down

 

Excel fills the series for us to help with data entry, as it knows the common series such as days of week, months in a year, etc.

Fill Series
Fill Series

Check Box does not work in Excel Online yet. So, if you plan to use your file online to edit in your browser, then please do not use this technique. Instead you can use the drop-down list technique.

 

1. Enable Developer Ribbon

Click on File –> Options.

File Options
File Options

Select Customize Ribbon on the left sidebar and then check the box next to ‘Developer’ on the right side.

Select Developer ribbon
Select Developer ribbon

 

Press OK.

Now Developer ribbon appears.

Developer ribbon appears
Developer ribbon appears

 

2. Insert Check Box control

Insert Check Box control.

Insert Check box control
Insert Check box control

Then, click where you want the box to be and drag to create the check box.

Create first check box
Create first check box

Right click on the check box and then select Edit Text.

Right click and select Edit Text
Right click and select Edit Text

Remove the default text in the box. This is usually difficult to format. We will not need that text as we will use the text that we typed already in the previous column.

 

Check box with no text
Check box with no text

3. Link Check Box to a cell

Right Click and select Format Control.

Right click and select Format Control
Right click and select Format Control

Now, we want to establish the link from the Check Box control to a cell.

Select the Cell Link
Select the Cell Link

In the Control tab, select cell $F$4 as Cell link.

Now that we have created the link, when you click on the check box to select, the cell F4 will display TRUE.

Selecting the check box will return TRUE
Selecting the check box will return TRUE

If you click on the box again to uncheck or deselect, then the text display in cell F4 says FALSE.

Unchecking the check box will return FALSE
Unchecking the check box will return FALSE

This is how we can let the Check Box control change the value. If it is checked, Sunday is a weekend, if it is not checked, it is a working day.

Weekday selection column
Weekday selection column

4. Copying Check Boxes

Now, we need to create this for other 6 days.

  • Select cell F4 and press Ctrl+C to copy.
  • Now, select cells F5 to F10.
  • Press Ctrl+V to Paste
Create 6 more checkboxes
Create 6 more checkboxes

All the new checkboxes will also be pointing to the same cell F4.

We must change each of them to point to a different day (checkbox next to Monday should point to cell F5 (Monday).)

Change cell link in all the checkboxes
Change cell link in all the checkboxes

After changing all the check boxes, we will change the font color to white.

Change font color to make the text invisible
Change font color to make the text invisible

This is done only for cosmetic purposes. Check box itself visually shows selected or not. So, we don’t need the display of TRUE or FALSE as well. Though the value is necessary for calculations, we don’t need it visible.

We will have the following appearance which is much cleaner.

Final checkbox appearance
Final checkbox appearance

Freeze Panes is an Excel feature that allows viewing datasets that are very wide (lot of columns) and/or very tall (lot of rows). In this video, I demonstrate how and when to use Freeze Panes.

Excel Formulas

In this tutorial, we will learn how to calculate dates of any chosen month. If we enter Year and Month, we want to automatically calculate the dates of the specific month, as shown below.

Final Result Dates for Chosen Month
Final Result Dates for Chosen Month

 

If you prefer video demo, please watch this.

Subscribe to our YouTube Channel

 

First, let’s set up our data input. We will let the user select Year and Month as two inputs.

STEP 1: Set up Year Input

Type YEAR in cell A1
Type 2018 in cell B1.
With cell B1 selected, type I_YR in Namebox.

Type Year and Name the cell
Type Year and Name the cell

STEP 2: Set up Month Input

Type MONTH in cell A3
Type January in cell B3.
With cell B3 selected, type I_MTH in Namebox.

Type Month and Name the cell
Type Month and Name the cell

Let’s fill the two input cells to a different color and apply borders so that it is clearly visible as input cells.

Year and Month Input cells formatted
Year and Month Input cells formatted

 

STEP 3: Implement Drop down list for Month input

We want the Month input to have data validation and a drop down list that shows all the months. To do this, we need a list of Month names.

Let’s create a new sheet. Type January in a cell. Then click on the cell and find the bottom right corner with your mouse and drag down to auto-fill all the 12 months.

Create a list of Month names
Create a list of Month names

Now, select the 12 months and name this list L_MTHS (as it is a list).

Let’s go back to Sheet 1 and apply data validation to the Month Input cell I_MTH as shown below.

Data Validation Implementation for Month Name
Data Validation Implementation for Month Name

In the previous video, we explained how to create such a drop down list.

Drop down list for Month Input
Drop down list for Month Input

STEP 4: Formula to create the starting date for our calendar

Go to cell C5 and type the formula

=DATE(I_YR,MATCH(I_MTH,L_MTHS,0),1)

Formula to create starting Date
Formula to create starting Date

DATE function needs YEAR, MONTH, DAY as parameters. We get YEAR value from I_YR cell.

For Month, Excel needs the month number such as 1, 2 and not a text string such as January, February. To convert user’s input of January to 1 and February to 2, we use the MATCH function.
MATCH function takes the input Month I_MTH and searches in the L_MTHS list and returns the row number where it found.

MATCH function to find the Month Number
MATCH function to find the Month Number

Since January will be found in the first row of the L_MTHS list, it will return 1 as the result.

STEP 5: Formula to create the other dates for our calendar

In cell C6, we need to use a formula to calculate the second date of the month we chose. Excel treats dates as numbers and hence to find the second date, we can just type = C5+1

Formula for second date of Month
Formula for second date of Month

 

That would calculate the second day.

Extend the formula down to reach the 31st day.

Extend formulas for all cells
Extend formulas for all cells

Now, if you change the Year to 2017, the dates would show for 2017 January.

If you choose 2018 as Year and February as Month, then we will see the below.

Dates for February
Dates for February

If our goal is to display only dates in February, this is not ideal, as it shows the first 3 days of March. This is because, we are just calculating 31 days and since February has only 28 days, the next 3 days are from March.
If we choose April the last day will show as May 1st, as April has only 30 days.
We will solve this problem by adding logic to our formula. There are many ways to do this. We will use one of the methods.

First, the logic we need to implement is

IF Date > End of Month, then do not display date. Otherwise display date.

In Excel, we can implement as

=IFERROR(IF(C5+1>EOMONTH($C$5,0),””,C5+1),””)

EOMONTH function is taking the first date (C5) of our month and finds the end of the same month (as we use 0 as the second parameter)
If we use 1 as the second parameter, then it will calculate the end of next month. If we use -1, it will calculate end of previous month.

You would also notice that I am using the $C$5 instead of C5. This $ symbol locks the column and row when we drag the formula to the cells below. This is called absolute cell reference. Explained in the video https://youtu.be/bTI_GIpw8fU

In this scenario, your result will be the same even if we used C5 instead of $C$5.

IFERROR is used to display nothing when an invalid value appears.

That’s it. With just two formulas, we have now created a way to calculate and display dates of any month chosen.

Final Result Dates for Chosen Month
Final Result Dates for Chosen Month

 

Note:

In this tutorial, we have used two formulas to calculate dates. 1 for the first date of the month and 1 for all the other dates. I usually try to avoid this when I build templates. If we had one formula for all the dates, it will be easier for maintenance, as I would have to make changes to only one and also it will be easy to remember. However, making it into one formula will make the formula more advanced and hence we will leave it to a future video.


Functions Used: DATE, IFERROR, EOMONTH, IF, MATCH
Features Used: Named Range, Data Validation, Drop-down list, Cell References, Formulas

 

In this blog post, we will learn how to calculate Rank with a condition. As Excel does not have a RANKIF function yet, we will use a different method to arrive at the solution.

Permanent link: https://indzara.com/faq/how-to-calculate-rank-if-with-a-condition-in-excel/

 

VIDEO DEMO

If you would like to be notified of these videos, please subscribe to our YouTube channel.

 

DATA

Let’s start with the input data. This is for a list of 10 employees, where the Department Name and the employee’s score are provided.

 

Input Data
Input Data

Let’s assume that the score is out of max of 10, higher the number, the better it is. We need to find the rank of each employee within the Department that the employee belongs to.

 

HOW TO CALCULATE OVERALL RANK AND RANK IF

Step 1: Convert the data to a Table by pressing Ctrl+T

 

Convert to table
Convert to table

 

Step 2: Calculate Overall Rank with RANK function

First, let’s calculate the overall rank (rank of an employee within the company).

We can use the RANK function in our formula.

General Syntax

= RANK( number, ref, [order])

In our context

= RANK(“Specific Employee’s Score”, “List of All Employees’ Scores”,0)

0 is to indicate that we need the rank based on descending order (highest is first rank)

Formula for Overall Rank with RANK function
Formula for Overall Rank with RANK function

Tip: If we need to find the rank based on lowest, use 1 instead of 0 in the function for the Order parameter.

Results of Overall Rank
Results of Overall Rank

That was straightforward, as we have a function RANK specifically designed for this use case.

Step 3: Calculate Overall Rank without RANK function

If we need to determine rank within Department – for example, rank within Finance, or rank within HR, then it is not so straight forward, as we don’t have a RANKIF function. 😊

Not a problem. Excel has enough functions for us to find a solution to this.

If we think about what ranking does, first rank means that there is no other number greater than that. We can translate this to

If number of values greater than the current value = 0, then that is the highest number. In other words, Rank = 1
If number of values greater than the current value = 1, then that is the second highest number. In other words, Rank = 2

We can convert this to a formula as below.

=COUNTIFS([Score],”>”&[@Score])+1

We must add 1 at the end of the formula because for the first rank, there are 0 values greater.

Countifs Function to calculate rank
Countifs Function to calculate rank

As you can see above, the two formulas give us the same result.

Step 4: Calculate Rank within Department 

Now, we need to modify second formula to calculate rank within each department.

The COUNTIFS function allows us to provide multiple conditions (unlike the RANK function). So, we can add Department condition to the same formula.

=COUNTIFS([Score],”>”&[@Score],[Department],[@Department])+1

Formula to calculate RANK with condition
Formula to calculate RANK with condition

Andrea has the top score in Finance, while Cathy has the top score in HR.

We can quickly verify this by using the Sort options.

Sorting just by Score from Largest to Smallest,

Sorting table by Score
Sorting table by Score

Sorting by Department and Score from Largest to Smallest,

Sorting table by Department and Score
Sorting table by Department and Score

Formula results match with the Sorting results. 🙂

Tie

There are scenarios where we may have a tie in score. If two employees had same top score, this formula will assign same rank 1 to both the employees, and there will be no second rank. The next employee will get the 3rd rank.

Ranking in case of tie
Ranking in case of tie

There are scenarios where we may need to handle ties differently. Those will need tweaks to this formula. We can take those up in a future video.

Getting Started

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 3 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.


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

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/

We use Excel’s Table feature extensively in all templates on indzara.com. This article will cover the basics of Tables that one needs to know before using the templates. This article is intentionally kept simple so that it is easy to follow by Excel beginners who have not used this before.

 

TOPICS COVERED

  • What is a Table?
  • Why do we use Tables?
  • Components of a Table
  • 3 Basic Data Entry Operations
  • 5 Essential Tips to avoid mistakes

 

VIDEO DEMO

If you prefer video demonstrations, you can learn the contents of this article by watching the video below.

 

WHAT IS A TABLE?

Excel Table is a rectangular shaped dataset, which has one or more rows and one or more columns. Once we make a dataset a Table, Excel activates a lot of useful features.

 

PURPOSE OF TABLES

  1. Stores information in an organized way
  2. Makes it easy to retrieve information when needed
  3. Acts as source of calculations

 

Here is an example Product Categories Table.

Excel Table - Simple Example
Excel Table – Simple Example

 

This is a simple table of just 1 column and many rows. We can use this to store our list of product categories.

 

COMPONENTS OF TABLE

The following Products Table has 4 columns and many rows.

Excel Tables - 4 Column Example
Excel Tables – 4 Column Example

 

The top row is the header row. This row houses the names of the columns so that we understand what each column represents. Columns are also referred to as Fields.

In this example, we have a list of products where each row represents a unique product. We are storing the product’s attributes such as Name, Description, Category and Starting Inventory. We can type text, dates or numeric data directly. We can also enter formulas to calculate if we need.

The appearance or the design of the table can vary. Excel provides many options to choose colors, borders and fonts as desired by the user. We can change them from the Table Styles in the Table Tools – Design ribbon.

Table Styles
Table Styles

 

I usually use a style that clearly shows the header as well as the border of the last row. This allows us to visually see the first & last row and thus know the boundaries of the table. I use the banded rows approach so that it’s easier to read data across. The colors used for header row may vary from one template to the other. For consistency, I am implementing Green colored headers for calculated columns and other colors for user entry columns.

 

DATA ENTRY OPERATIONS

Entering a new row of data

If you find that the table is empty and just has the header row and no data rows, then start typing in the first row following the header row.

Empty Excel Table
Empty Excel Table

 

Here, we want to start typing from row 3 in column A.

Entering first row of data
Entering first row of data

 

Once I have entered the first row of data, then start typing in row 4 to enter second row of data.

Entering second row of data
Entering second row of data

 

You can see that Excel automatically expands the boundaries of the Table. Now any calculation that is done somewhere else that uses this table as source will now include this new row of data automatically.

 

Inserting a row of data

Let’s say we want to enter a row of data in between the two rows entered already. We would right click on cell A4. This will open a menu as shown below.

Inserting Row of Data
Inserting Row of Data

 

We will choose ‘Insert – Table Rows Above’.

Inserted Row
Inserted Row

 

Now, you can type in the new inserted row.

We can insert multiple rows at once by just selecting multiple cells first before right clicking and choosing Insert – Table Rows Above.

 

Deleting a Row of data

Let’s say we made a mistake and we don’t need that second row anymore. If we want to delete that row, we can right click on cell A4.

Deleting Row of Data
Deleting Row of Data

 

This time, we choose ‘Delete – Table Rows’. This removes that row.

After row is deleted
After row is deleted

 

After that, our table looks as it did before, without that row.

We can delete multiple rows at once by just selecting multiple cells first before right clicking and choosing Delete – Table Rows.

 

TIPS

Excel Tables - 5 Essential Tips
Excel Tables – 5 Essential Tips

 

1. Ensure data is entered inside the Table

The most common reason why users find that the output of the template does not reflect the inputs is that the input data is not entered inside the tables correctly. To know if your data is inside the Table, click on the cell and see if Table Tools ribbon appears.

Table Tools - Design Ribbon
Table Tools – Design Ribbon

 

If it does, your data is inside the table. If not, it is not inside the Table and hence will not be reflected in any dependent calculations.

If you are inside the table and want to know the boundaries of the table, press Ctrl+A. Excel will select all the data cells inside the table and thus show us the boundaries.

Finding Boundary of Table
Finding Boundary of Table

 

2. When copying data from somewhere and pasting, always use Paste Special as Values.

Excel - Paste Special as Values
Excel – Paste Special as Values

 

When you right click inside the table (wherever you want to paste data), you may see this option under the Paste Options as shown above as Option 1. If not, then click on Paste Special and choose paste as values under Paste Values sub-menu (shown as Option 2).

Default Pasting method (Ctrl+V) will paste not only the values, but any formulas and formatting. This is not desired as 1) it may create links to external files and 2) it will overwrite the formulas and formatting expected in the template. Please avoid.

 

3. Avoid Blank Rows

It is recommended to not have any blank rows of data in tables. As we saw before, just select them and delete those rows.

Avoid Blank Rows
Avoid Blank Rows

 

4. Do not edit cells with formulas

Usually in the templates from indzara.com, we have formulas in some calculated columns. Please do not edit them. If you did it by accident, you can undo by pressing Ctrl+Z immediately.

 

5. Do not rename fields with pre-defined column labels

Depending on the template, some of them use pivot tables (another Excel feature) for calculations. In such cases, renaming a field that is used in a pivot table will break calculations. In our templates, we provide what we call as Custom fields which can be renamed without any concerns. You can add new columns if needed. But renaming other existing fields should be avoided. If you have the need to rename and are not sure about the impact, please contact me and I will help.


Permanent Link to post comments: 

https://indzara.com/faq-items/introduction-excel-tables-data-entry/

Using Indzara.com's Products

License Rights for Premium Templates

Purchase of a Premium Excel Template gives rights for one active user to use the file(s).

As an owner of this license

  • You can make copies for your own use.
  • You can export sheets as PDF & share PDF with others
  • You can use the files in multiple computers of yours
  • You can edit the files as needed (password provided for locked sheets)
  • You can transfer the license to someone provided that person is the only user moving forward.

NOT INCLUDED

  • The template (.xlsx) files should not be shared with others
  • Copies should not be made by anyone other than license owner
  • You cannot sell the file commercially

 

Liability 

indzara.com or Dinesh Natarajan Mohan will not be liable for any damages due to any business decisions taken based on the templates. More than a hundred hours are spent on average for each of the templates posted here. It takes a lot of effort designing, developing, testing, documenting and publishing them. Maximum effort has been taken to ensure the accuracy of the templates. If there is any significant bug, please contact me and I will issue a full refund.

 

License Rights for Free Templates

  • You are welcome to download the free templates and use as needed.
  • You are welcome to edit the sheet and modify as needed for your personal use.
  • If posting it anywhere on the web, please give credit to indzara.com.