Grouping Excel worksheets

Working with multiple worksheets

Excel has a great feature that allows you to work in multiple worksheets simultaneously. This feature allows you to add content and/or format the contents in the multiple worksheet at the same time. Let's look at an example where this feature can be a great time saver.

Let's say you have a yearly budget for five years and the data for each year is placed in a separate worksheet. For each year the structure of data set, column heading and row heading are the same. It has cell formatting and number formatting consistent across all worksheets. Now let's say that you want to modify all 5 worksheet simultaneously. This is when the grouping worksheets become very useful. Instead of modifying one worksheet and then copying the changes to other worksheets, you can modify all worksheets at the same time. To do this first group the worksheets that you want edit simultaneously.

To group worksheets

To group worksheets together use one of the following methods:

When "2016" worksheet is selected press shift key and click "2021". Now notice the there is a continuous line from 2016 worksheet tab to 2021 worksheet tab. Also note that the title bar now has the word "[Group]". This indicates that the worksheet has been grouped.

To group worksheets that are not adjacent to the current worksheet press and hold control key and click the worksheets tabs to need to group.

Now any changes to 2016 worksheet will affect all other worksheet in the group. Be careful now! any content in the same cell addresses in other worksheet in group will be overwritten.

To ungroup worksheets

To ungroup worksheets, right click on the worksheet tab and select ungroup

Comments

CUMIPMT and CUMPRINC function

CUMIPMT Cumulative interest payment function allows you to calculate the interest paid for a loan or from an investment from period A to period B. When getting a loan, CUMIPMT function can be used to calculate the total amount of interest paid in the first five months or from period 12 to period 20. A period can be a month, a week or two week. Loan Amount : 350,000.00 APR: 4.5% Down payment: 0.00 Years: 25 Payment per year: 12 From the above data, we can calculate the following: No of Period: 25 × 12 = 300 Periodic Rate: 4.5/12 = 0.375% Here is how you will substitute these values into the function. = CUMIPMT (periodic rate, No of period, vehicle price, start period, end period, ) = CUMIPMT (0.375, 300, 350000, 1, 5, 0) In an excel worksheet, we use cell address instead of actual values as shown below: Here is the formula view of the worksheet: CUMPRINC Another related function is CUMPRINC. CUMPRINC function is used to calculate cumul

Excel PMT Function

PMT function is very useful for calculating monthly payment required to payback a loan or mortgage at a fixed rate. This function require a minimum of three inputs, periodic rate, number of periods, present value or the loan amount. Here is a simple example. Home Loan: 350,000.00 Interest rate: 4.5% Number of years to repay the loan: 25 Note: To calculate monthly payment, we need to find the monthly rate and number of months as shown above. Then it is simply a matter of substituting the values into the payment function, as shown in the formula view below.

BCG's Brand Advocacy Index

The Boston Consulting Group's (BCG) Brand Advocacy Index (BAI) is a metric developed to help companies measure the degree of customer advocacy for their brands. BAI focuses on the likelihood of customers to recommend a brand to others, which is a powerful indicator of brand strength and customer loyalty. Unlike other customer satisfaction or loyalty metrics, BAI emphasizes the importance of customer referrals and word-of-mouth marketing. BAI is calculated based on a survey where customers are asked about their willingness to recommend a brand to their friends, family, or colleagues. The responses are then used to compute a score, which ranges from -100 to 100. A higher BAI score indicates that a brand has more advocates who are likely to recommend the brand to others, while a lower score suggests that the brand has fewer advocates or even a higher number of detractors. BCG's research has shown that companies with higher BAI scores tend to experience higher growth rates and bett

EduSarva

Search This Blog