Md. Nafis Soumik

Hi MAHMUD,

Thank you for your valuable suggestion. I updated the formula for calculating electricity bill with Variable Unit Price (Slab). Hope you find this article useful.

Regards,
ExcelDemy

Hi Mahmoud,
You can create a half doughnut chart to show the correct percentages with data labels by following the steps mentioned in this article. Only exception is you will need a different data table with the yearly revenues converted into percentages of the total.
You can do this by applying the following formula inside any cell: =C5/$C$10. Use fill handle to populate the dataset and keep the percentage column into percentage format. Now follow the exact steps described in this article. Upon finishing check the Data Labels option from Chart Element. Your half doughnut chart with percentage Data labels is now ready.

I hope it solves your problem.

Regards,
ExcelDemy.

Hi Jenn,
Based on your comment, it seems you are looking for a way to create a spreadsheet that displays different tables based on the selected company.
To address your requirement, one approach is to use Excel’s “Data Validation” feature combined with “Index” and “Offset” functions. Here’s a potential step-by-step solution:
1. Set up your spreadsheet with separate tables for each company, each in a different range of cells.
2. Create a list of company names (e.g., in a dropdown) where the user can select the desired company.
3. Assign data validation to the cell where the user selects the company, limiting the input to the list of company names.
4. Next, use the “Index” and “Offset” functions to display the corresponding table based on the selected company.

Here’s an example of how this could work:
1. Create separate tables for each company, each in a different range of cells (e.g., Company A in cells A1:D10, Company B in cells A15:D24, Company C in cells A29:D38).
2. Create a dropdown list of company names (e.g., in cell A50) using Excel’s Data Validation feature.
3. Use the “Index” and “Offset” functions to retrieve the appropriate table based on the selected company. For example, in cell A55, you could use the following formula:
=INDEX($A$1:$D$10, OFFSET($A$1:$A$10, MATCH($A$50,$A$1:$A$10,0)-1, 0, ROWS($A$1:$A$10), COLUMNS($A$1:$D$10)))
This formula will retrieve the table for the selected company dynamically.
Now, when you select a company from the dropdown list in cell A50, the corresponding table will be displayed in cell A55 and automatically update based on the selection.
Please note that the specific ranges and formulas may need to be adjusted based on the actual structure and layout of the spreadsheet. However, this approach should provide a starting point to achieve the desired functionality of displaying different tables based on the selected company in Excel.

Regards,
ExcelDemy

Hello Ed McCann,
It’s possible that the Pivot table is referencing a range of cells that includes data outside of the intended range. Here are a few steps you can try to troubleshoot the issue:

1. Check the source data range: Make sure that the Pivot table is referencing the correct range of cells that contain your data. Select any cell inside the Pivot table, go to the “Analyze” or “Options” tab in the ribbon, and look for the “Change Data Source” or “Select Data” button. Clicking on this button will show you the range of cells that the Pivot table is using as its data source.

2. Check for hidden data: It’s possible that there is hidden data in the source data range that is being included in the Pivot table. Select the source data range, go to the “Home” tab in the ribbon, and click on the “Format” dropdown. From here, click on “Hide & Unhide” and then “Unhide Rows/Columns” to reveal any hidden data.

3. Refresh the Pivot table: If none of the above steps work, try refreshing the Pivot table. Select any cell inside the Pivot table, go to the “Analyze” or “Options” tab in the ribbon, and click on the “Refresh” button. This will recalculate the Pivot table based on the current data source.

I hope these steps help you troubleshoot the issue with your Pivot table.

If the problem persists, then you can send your excel workbook to this email: [email protected]

Regards
Exceldemy