Timesheet excel worksheet getting too big

Question

Timesheet excel worksheet getting too big

Jen 0

I have a customized worksheet that I used to keep track of my time (every 30min) for each project and I can be involved in several projects during the whole day.

My worksheet looks like this:

User's image

I have all the 30min slots in the rows and with dropdown lists for the project / task / bill state and some details in the rows as well.

The columns where the yellow cells are the project/task and I have formula that will pick up the the count of based on selection of the project/task/bill state in the rows. That will help the sum of the total 30min slots convert into hours in the blue highlighted rows, which ultimately help me to register the time spent for the day.

I know this resulted in many formula cells that are unused, thus 0 in value. The file is now so big that excel will get stuck trying to update the file even though I only make very minor change. The file is saved in onedrive and is 3400 KB in size now.

Is there anyway to improve this?

2 answers

Your answer

Answer 1

Liora D 5,720 Microsoft External Staff Moderator

Dear Jen,

Thank you for contacting Microsoft Q&A.

Before suggesting the best solution, could you confirm:

Approximately how many rows and columns are in the sheet?
Are you using Excel Tables or just a normal range?
What type of formulas are used in the yellow columns (e.g.,, or array formulas)? COUNTIFSUMIF
Do you need real-time calculations, or would calculating totals on demand be acceptable?
Is this workbook for a single day or multiple days/months?

In the meantime, here are a few steps you can take:

1. Reduce Formula Overhead

Replace repeated or formulas with: COUNTIFSUMIF
- PivotTables for summary calculations.
- Or use Excel Tables with structured references to limit calculation ranges.
Avoid formulas referencing entire columns (e.g.,); restrict to actual data ranges.A:A

2. Minimize Unused Formulas

If many cells show 0, consider:

Using IF to return "" instead of 0 for empty rows.
Or remove formulas from unused rows and calculate only when needed.

3. Switch to Manual Calculation

Go to Formulas > Calculation Options > Manual.
Press F9 to calculate when ready. This prevents Excel from recalculating every change.

4. Optimize File Format

Save as Excel Binary Workbook (.XLSB):
- File > Save As > Choose ..xlsb
- This reduces file size and improves performance.

5. Reduce Conditional Formatting & Data Validation

Too many dropdowns and formatting rules can slow Excel.
Limit validation to active rows or use dynamic named ranges.

6. Use Power Query or VBA

Instead of formulas in every cell, consider:
- Power Query for aggregating time data.
  - A simple macro button to calculate totals when needed.

7. Split or Archive Data

Instead of one large file for all time periods:

Create separate files for each month.
Or separate sheets for each week.

Please understand that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.

I hope this information helps point you in the right direction. If you run into any issues while trying the steps, or if something still doesn’t feel quite right, please don’t hesitate to reach out again. I’ll do my best to support you however I can.

Looking forward to hearing back from you with any updates or additional details.

Warm regards,

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Jen 0 Reputation points

2025-11-03T09:50:37.8933333+00:00
Hi,

The number of columns are 35 but the rows are 10,000

I am just using normal range

The formula used are =COUNTIF / COUNTIFS / SUMIF / SUMIFS / NETWORKDAYS

I need real time calculation

The workbook is for single day for all 12 months in the year
Liora D 5,720 Reputation points Microsoft External Staff Moderator

2025-11-03T13:39:52.2266667+00:00
Hi Jen,

Thanks again for the extra details!

With 35 columns and 10,000 rows, it makes sense that your workbook is starting to slow down especially with real-time calculations and multiple COUNTIF, SUMIF, and NETWORKDAYS formulas running across such a large range.

To help improve performance while keeping your workflow intact, here are the first three steps I’d recommend:

Convert your data range into an Excel Table

Using Excel Tables (via Home > Format as Table) helps Excel manage large datasets more efficiently. Tables automatically adjust ranges, support structured references, and improve formula performance. I’ve found this especially helpful in my own workbooks when dealing with dynamic data.

Limit formula ranges

Instead of referencing entire columns like A:A, try to restrict your formulas to the actual data range, such as A2:A10000. This significantly reduces the number of cells Excel recalculates. I use this technique regularly to keep my larger workbooks responsive.

Split the workbook by month

Since your file contains data for all 12 months, consider splitting it into separate files or sheets per month. This will reduce the number of rows per file and make each one more manageable. Personally, I’ve seen great performance improvements by organizing time-based data this way it also makes archiving and sharing easier.

Let me know if you’d like help applying these steps or if you’d like to explore additional options like using Power Query or macros to streamline your calculations. I’m happy to assist further!
Liora D 5,720 Reputation points Microsoft External Staff Moderator

2025-11-04T15:22:29.4+00:00

Hi Jen,

I just wanted to follow up to see how things are going with the information I shared above. I truly hope it was helpful and brought you closer to resolving the issue.

If you’ve had a chance to review it and have any updates, questions, or if you ran into any difficulties along the way, please don’t hesitate to let me know. I’m more than happy to assist further if needed.

It would be great to hear back from you!

Warm regards,

Liora.
Jen 0 Reputation points

2025-11-05T06:52:15.19+00:00

Hi, I have been trying to test out using only one month as well as converting to table. Converting to table is a bit annoying because I have some formula in the cells that will help me identify day of the week and breakdown 30min interval in the rows. Once I convert the entire range as table, the formula is gone. That will cause issue when I need to copy the template for next month and change the dates.
Liora D 5,720 Reputation points Microsoft External Staff Moderator

2025-11-05T08:17:15.4433333+00:00
Hi Jen,

Thank you so much for your thoughtful follow-up.

To help you move forward while keeping your workflow intact, here are two detailed suggestions that I believe will work well for your scenario:

Keep your template formulas outside the table

Instead of converting the entire worksheet into a table, you can create a separate “template zone” (either in the same sheet or a dedicated one) that contains all your formulas for generating:

Dates

Weekday labels

30-minute intervals

This template zone remains untouched and can be reused each month. When you need to start a new month, simply copy the output from the template into the table, keeping the table focused on data entry and tracking.

This way, you preserve your formulas and still benefit from the performance and structure of Excel Tables.

Use the table only for input and tracking

Instead of converting the entire range, you can:

Convert only the data entry area (e.g., project, task, billing status) into a table.

Keep the calculated columns (e.g., weekday, time slot breakdown, totals) outside the table or in a separate sheet.

This approach allows you to:

Maintain your formulas without interference

Use structured references and filtering in the table

Avoid performance issues caused by recalculating formulas across 10,000 rows
Jen 0 Reputation points

2025-11-07T06:45:46.59+00:00

Thanks for the advice of just setting part of the data as table. That never crosses my mind. I always thought the entire set of data needs to be format as table. This changes a lot of things.
Liora D 5,720 Reputation points Microsoft External Staff Moderator

2025-11-08T06:59:11.2266667+00:00

Hi Jen,

Thank you so much for your update, I’m really glad to hear that the idea of applying the table only to part of your data was helpful! It’s a small shift, but it can make a big difference in how you manage and scale your workbook.

And if you found the answer helpful, feel free to mark it as Accepted Answer, this helps highlight the solution and makes it easier for others with similar questions to find.

If you need any further assistance or have additional questions, please don’t hesitate to reach out. I'm here to help.

Wishing you smooth and efficient tracking ahead!

Warm regards,

Liora

Answer 2

JeovanyCV 310 Volunteer Moderator

Hi Jen

Based on your question, scenario, and comments. I wonder if you'd consider having a video chat with us. There, we can share the screens to understand your scenario and goals better, and therefore provide you with a solution to your problem.

Since today is Friday, to give you some time to answer, let's set up the meeting for

Tuesday, November 11th. at 19:00 (7:00 pm) LONDON time.

I'll send you the link once you've decided.

Have a nice day and weekend.

Kind regards

JeovanyCV

Volunteer Moderator

Share via

Timesheet excel worksheet getting too big

2 answers

Your answer