• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

My spreadsheet design skills are terrible, how do I improve?

S

skr5e

New Member
I've learned some basics on Excel and every day I'm learning more on how to DO things using excel, but I'm starting to realize that it's not enough. I'm terrible at making the presentation simple and concise. I've attached one of my first spreadsheets to see what I mean. It seems so messy that I don't know if anyone except I would be able to decipher it.

Why does my spreadsheet seem so convoluted? Any general design ideas, or specific to this particular spreadsheet would be appreciated.
 

Attachments

Hi ,

There is a lot of material on the net with regard to good spreadsheet design ; check out these links :

http://geekgirls.com/2013/08/good-spreadsheet-design/

http://www.techrepublic.com/blog/10-things/10-ways-to-screw-up-your-spreadsheet-design/

I have made the following changes to your uploaded file :

1. Converted your data range to a table ; this is now almost a given ; if you can use tables , do so ; their features will help ease a lot of tasks.

2. Re-arranged the columns ; never have formulae in the midst of data ; try to keep all your data together , and where you need derived columns , which have formulae , put them at the extreme right of your data.

3. Inserted a pivot table ; PivotTables are an incredibly powerful feature of Excel , and the sooner you master them , the more efficient you will become.

As a rule , always separate your data from your calculations ; where possible , have a Data tab , where all data is entered , have a separate Calculations tab , where you can have all your data processing done , and then you can have separate tabs for your Dashboard , Charts ,...

Narayan
 

Attachments

Hi

I have designed the file the way I would normally, summarised at a reasonably high level. Not sure how that stacks up with the reading material above but I set up all of my files by separating Inputs (raw data), calculations and outputs. This data flows from left to right in the spreadsheet provides a segregation of activities which culminates in a summary page and the far right of the document. Excel's calculation engine operates left to right so by setting your models up like this you are working with Excel to keep the flow of data as efficient as possible.

The above are of course just my views and I have attached a file to show a working example.

Take care

Smallman
 

Attachments

Thanks, that answer was even better than what I was hoping for. It's opened my eyes to how little I know :p

One question--what happened to the "% of total" column? That really is the most crucial part of the spreadsheet, and unfortunately the one that was making the spreadsheet messy. But it seems it was removed. Is there some way to access that information with the changes made?

EDIT: Smallman this is very close to what I would consider ideal. I have to modify the bar chart into a pie chart and mess with the labels, and I don't know much about charting yet so it looks like I have some reading to do. Also I haven't the slightest idea how you accomplished this, it's way beyond my set of knowledge right now. I'm definitely learning a lot here


NARAYANK991 said:
Hi ,

There is a lot of material on the net with regard to good spreadsheet design ; check out these links :

http://geekgirls.com/2013/08/good-spreadsheet-design/

http://www.techrepublic.com/blog/10-things/10-ways-to-screw-up-your-spreadsheet-design/

I have made the following changes to your uploaded file :

1. Converted your data range to a table ; this is now almost a given ; if you can use tables , do so ; their features will help ease a lot of tasks.

2. Re-arranged the columns ; never have formulae in the midst of data ; try to keep all your data together , and where you need derived columns , which have formulae , put them at the extreme right of your data.

3. Inserted a pivot table ; PivotTables are an incredibly powerful feature of Excel , and the sooner you master them , the more efficient you will become.

As a rule , always separate your data from your calculations ; where possible , have a Data tab , where all data is entered , have a separate Calculations tab , where you can have all your data processing done , and then you can have separate tabs for your Dashboard , Charts ,...

Narayan
Click to expand...
 
Last edited:
NARAYANK991 said:
Hi ,

Can you see your file now ?

Narayan
Click to expand...

Yes that's perfect. I'm looking at the pivot table field list, somehow you got "Sum of Values" as a column label and got "Percent of Total" in the summary area. Even though these aren't listed in the "Choose fields to add" box. I know a little bit about pivot tables but this seems like a more advanced concept.

It works very well, but how could I reproduce what you did?
 
Hi ,

These are all quite basic tips as far as PivotTables go ; the link posted earlier , Debra Dalgleish's website , is the ultimate go-to site for pivot tables ; if you can master all of the knowledge there , you'll become a PivotTable expert.

Narayan
 
Hi

Thanks for the kind feedback. You will learn a lot if you read the threads on this forum daily. There are some very knowledgeable people around.

The technique I used is called a Slicer. Just Google Slicer and Excel and there will be loads of tutorials. Changing the chart to a pie is pretty simple, just right click on the chart and select Change Chart Type - then choose Pie Chart.

It is all good and your learning will be very steep at first but it gets easier and we are all still learning.

Take care

Smallman
 

Attachments

You must log in or register to reply here.
Back
Top