Optimalizing an existing sheet

moocker

New Member
Joined
May 6, 2019
Messages
3
Hi there,

A question about optimizing sheets from colleagues and exterior firms. I've read a bit on best practices and rules of good tables to increase user-friendliness and reduce the reader's cognitive load. As a practice, I'd like to improve and automate the following example.

The following tender document with measurements: https://imgur.com/ipiCCal

How would you go about optimizing this? What would you change?

Things I believe can be improved, correct me if I'm wrong:


  • Hierarchy.
    • The first column considers numeric Chapters, Sections and Subsections (numbers formatted as text - 1, 1.1, 1.1.1, etc.). It is often used as a reference. Is it good practice to put it like this?
    • In the column "Description des traveaux" (description of the works) further indentation is used to create a hierarchy.
    • Empty rows are added to improve readability. But, this means it is difficult to format as a table with consequences for hot keys etc. I'd rather remove them and increase readability in another way. (I know how to remove blank rows, but is my thinking correct here?
    • Would you use groups instead?
  • Summations.
    • Every Section is summed up manually with SUM() (e.g. Section 1.7). My guess is this would be more efficient with SUBTOTAL().
    • Row 112 ("Montant HT ...") is, again, a manual summation of all Sections.
    • On a previous sheet, a manual summary is made of every Section with the total price (for the client). I believe this can be done faster with PivotTables?
  • ...?

Tips appreciated!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top