Excel, but not

[Jeff_The]

Hi "All Dear Readers" ,


Excel, but not...
I assume this is recognisable for some of 'us', so I address my request to the 'others'.
I started to 'develop' an excell file, and then added some other tabs, and more tabs, and even not finished to add more tabs. Today, it is even hard for me to find my way back in all the data, charts, tables, ...
Is there a way to structure the file, please. implementing a process flow, down , and back up...
Further, since the Exel-file will be subject of an audit... I have to document this. Is there an international recognised standard for auditing (cost management related (excel)files).


Thanks in advance! Enjoy the Weekend!
Jeff

 

[Eloise T]

It might help if you uploaded your file so we could take a look at what you're doing so we can give a more focused answer.

 

[Hui]

It is all about being organised

I typically have an Index sheet as the Left Most worksheet

I use a Red Tab as a Separation Tab to separate different sections
eg:
Summ >>>
Calcs >>>
Data >>>
Other >>>

ie: all the worksheets after, to the Right of Calcs >>>, are Calculations until I get to another Red worksheet

Each Section is readily identified by the Leading >>> tab

Tabs in each section are arranged numerically or alphabetically and colored the same

Each Tab can have hyperlinks to the Index Tab or the First tab of each section

All the non-compliant worksheets are put in a Other >>> section and left on the far right as they will rarely be used

eg:


Consistency

I also recommend that all worksheets use a consistent set-out
that is if you have dates across the top of the worksheet, the same date should be in the same column on every worksheet
if Column M is Jan 2017, then any worksheet that uses Dates Column M should be Jan 2017

 

[NARAYANK991]

Hi ,

See if the following link has any tips :

http://chandoo.org/wp/2009/11/03/make-better-excel-sheets/

As far as Hui's tips about using the same columns for the same data , I would differ , to the extent that what might help is an identical layout , where ever possible.

For example , if one worksheet uses column B for Name , column E for Age and column M for Salary , then every worksheet which has similarly named headers , should use column B for Name , column E for Age and column M for Salary.

The advantage of this is that formulae can be more easily written since you do not always have to navigate to the relevant worksheet tab , and can rely on memory to put in the column references.

Even better would be to make liberal use of Excel Tables.

Narayan

 

[Eloise T]

It is all about being organised

I typically have an Index sheet as the Left Most worksheet

I use a Red Tab as a Separation Tab to separate different sections
eg:
Summ >>>
Calcs >>>
Data >>>
Other >>>

ie: all the worksheets after, to the Right of Calcs >>>, are Calculations until I get to another Red worksheet

Each Section is readily identified by the Leading >>> tab

Tabs in each section are arranged numerically or alphabetically and colored the same

Each Tab can have hyperlinks to the Index Tab or the First tab of each section

All the non-compliant worksheets are put in a Other >>> section and left on the far right as they will rarely be used

eg:
View attachment 26680

Consistency

I also recommend that all worksheets use a consistent set-out
that is if you have dates across the top of the worksheet, the same date should be in the same column on every worksheet
if Column M is Jan 2017, then any worksheet that uses Dates Column M should be Jan 2017

Question: Is there anything in the RED tabs, or are they just dividers?

 

[Hui]

Mostly, just Dividers

But You can put Sub-Indexes on them linking to the worksheets in that section and maybe also the Index and Other Section Divider worksheets

I sometimes will call them:

I >
S >
C >
D >

for Index, Summary, Calculations and Data respectively, as those names take up a lot less space, allowing more tabs to be shown

 

[Eloise T]

You've obviously put a lot of thought into this.

 

[Hui]

We are visual animals

We see colors and shapes a lot easier and quicker than having to read all the details

 

[Eloise T]

Yes.