Do’s & Don’ts of Excel Spreadsheet Design (with Examples)


Here’s some do’s and don’ts when it comes to creating your next Excel workbook. There are two main aspects when you design an Excel spreadsheet. First, the structure of the workbook. Second, the visual
design of the worksheets. This video is brought to you by Excel Essentials for the Real World. Learn Excel from scratch
or fill in the gaps to become immediately confident. Discover hidden tips and tricks which get you working smarter, not harder. Click on the link below or
type in xelplus.com/courses. Let’s cover structure first. Keep raw data separate to the analysis. By separate, I mean in separate tabs. Each sheet should have a
purpose you can easily explain. For example, in this report the Data tab has the raw data. Dashboard has the final report. All calculations are done
in the Calculation tab and the Control tab has a
summary of the changes made to the dashboard together with timestamps. We create this report from scratch in My Excel Dashboard Course. Finalizing an Excel workbook
is usually not a one time task, requirements change,
company structures change. Keep an overview of the changes you make in the workbook in a separate tab. Make a note of the change
and add a timestamp. No one likes to document
but taking the time to do it is going to save you a
lot of time later on. If you’re distributing the
workbook for others to use or for others to input, add an instruction sheet. If you’re using abbreviations or keys, define what these are on this sheet. Outline the purpose of
each tab and write a set of guidelines and instructions. It might be clear for you what your report or tool is meant to do but it’s not going to
be clear for everyone. Excel does have a good
file recovery system so if something goes wrong
and you want to go back to a previous version you generally can. But if you want to be on the safe side it helps to keep a copy of the file. Now, let’s talk about visual design. If you think there is a
slight chance that someone at the office will print out the sheets or export the file as PDF make sure you prepare it for printing. Check each tab. Go to Print Preview and
adjust as you see fit. Also add headers and
footers to your pages. You might want to add the file address. If it’s an internal report you
might want to add the date. Just make sure whatever you do, that you add page numbers. This makes it more obvious
if something goes missing. Keep a consistent color
code for different purposes. Let’s say you’re creating a
file that you want to distribute to collect sales information
from different divisions. Use color to help the users
know which fields are for input and which are calculated. For example, you could
design them like this, use a subtle color for the input cells. Or you can design it like this, keep the input cells white and add a color for the calculated ones. Whichever method you choose, keep it consistent throughout your work. Also make sure you
don’t use too much color or a background color that’s
too close to the font color. Ensure there is a good contrast
so the content is readable. Basically, design it in a way
that’s appealing to consume. Use formatting but don’t
use excessive formatting. A lot of us spend hours figuring
out how to do the analysis and then doing the analysis. We forget to put the time to
organize our files and reports, take the clutter away, give the dataset structure, make certain areas stand
out and others not so. This makes it easier and more appealing for others to understand your file and also for yourself when
you come back later to it. Think of your file as your workplace. We’re more productive when
our workplace is clean and it’s not cluttered. Do the same to your file. Take some time and clean it up. The other side effect of proper
formatting is appreciation. Let me explain. Imagine your boss is
coming over for dinner. You spend hours preparing a meal. You go to the Farmer’s Market. You buy the best ingredients, make the best meal you’ve
ever managed to make. Now, when it comes to serving him or her you just dump everything
on a plate and you serve. Your boss will probably
not be too impressed. It may still taste amazing but
they’re not going to appreciate all the time you spent preparing it. Like the saying goes, “You eat with your eyes first.” So if you just spent a few
minutes organizing everything nicely on the plate like good chefs do, your boss will love it. Put the same effort in your work, organize before you serve. I hope you found these tips useful. Keep them close at hand. Refer to them when you
create your next workbook. (upbeat music)

69 comments

  • ROBERT AKWASI ADJEI

    Dear Leila, Once again may the Good Lord richly bless you

    Reply
  • ryan.m

    I see those adverts for team management software, one 'user' claimed that trying to use spreadsheets was a nightmare… I really think that if you understand the data, and understand spreadsheets, you can achieve similar results

    Reply
  • peterthinks

    Are there any color pallets that are more suitable for people who are colorblind? I don't want to color code two groups different colors and have them look the same to someone who cannot differentiate. Any recommendations?

    Reply
  • Razzouk Muhammad Ali

    Thanks girl!

    Reply
  • Razzouk Muhammad Ali

    And when you will start ms access tutorial

    Reply
  • abd allah kandiel

    Happy Thursday your highness
    Sooner i'll join
    Excel real life course
    Just busy finishing Chris dutton's power bi course
    Thanks a lot
    C u there

    Reply
  • Chris Mancinelli

    Good reminders

    Reply
  • sarfaraj kureshi

    Nice tutorial mam, thank you, god bless you

    Reply
  • Katerina

    Vielen Dank Liebe Leila, there is so much elegance in your excel work, much appreciated
    Viele liebe Grüsse 🤗 🌞

    Reply
  • Will Roberts

    Yeah!

    Reply
  • Leila Gharani

    What's your favorite tip? For me it's Tip #8. I used to struggle just to get the report delivered before deadline so I didn't do any of that – it was too time consuming. Then I realized if I started with the right structure, the rest was done in no time. A little really does go a long way.

    Reply
  • Luciano Panepucci

    Excellent advice as always! I do almost all that, including the change log with timestamps. It saves you big time when you come months later! My Achilles's hells are the instructions.. I need to do then more and much better. I can't find the right style and size for the instructions wording.. — hey, this could be a different topic idea for you! 😉 —

    Reply
  • Emre Murat

    I have just completed course and tested myself.
    Great work Leila for preparation&presentation.!
    Thank you

    Reply
  • grivello

    #2 is my favorite. I wasn't thinking about things this way. Thank you 🙂

    Reply
  • P T

    All are my favorites and things I always strive to achieve…I take pride in a well structured workbook such as these suggestions. Nothing peeves me more than receiving a file not already prepared for printing or not well organized for me as a consumer…

    Reply
  • Yulin Liu

    Great tips. Thanks Leila!

    Reply
  • Joe from DC

    Define a purpose – mandatory for all my reports. Extra tip if possible create a visual walk thru of the report in PowerPoint and attach within the report.

    Reply
  • Muhammad javed

    Lella .please make video about price comparison
    in excel

    Reply
  • nima naghipoor

    hello, thanks so much for teaching such as this excellent, do you have a page on Instagram ?

    Reply
  • SALIF DIALLO

    Excellente comme toujours

    Reply
  • Roberto Velicaz

    Always look forward to your Thursday youtube postings 😃. I have recently started this course and I am really enjoying it. Professional, detailed and methodical and most importantly, easy to follow. Numerous tips, really great! Thank you Leila!

    Reply
  • Deependra Gupta

    Tip # 2,3,8 are awesome tip…

    Reply
  • Malina C.

    Love it!

    Reply
  • Gustavo Feijoo

    Props Leila! Very nice!

    Reply
  • Vijay Arjunwadkar

    Yet another nice video! Keep them coming Leila! You are doing a great job helping thousands of Excel fans like me discover and learn so much more! 🙂

    Reply
  • MF Wong

    Point 8 is so true! I can’t agree more! And that’s absolutely for real business world. Having said that, the content itself is of equal importance. But appearance comes first. 😅

    Reply
  • Neren Lakhraj

    Thank you Leila … Definately valuable piece of information which should always be used used.

    Reply
  • Shweta Nair

    An amazing tutorial. It is truly important to aesthetically present your data. Your tutorial teach us both make complex data calculations and also how to present and showcase them.
    I have learnt so many concepts from all your tutorials. Thank you and God bless you.

    Reply
  • Steven Nye

    very useful

    Reply
  • Tim Su

    Thanks you. See you everyday.

    Reply
  • tha2ir Talib

    I like it , excel lady and thanks for sharing .

    Reply
  • Graham Parker

    Really enjoyed these tips Leila.

    Reply
  • Nadir

    Thanks for video!

    Reply
  • DiBa Media

    Just professional lovely

    Reply
  • Rico S

    Reusability for me! In other words, [where possible] try to make it consistent across business areas, costcentres, profit centres, region, countries or whatever criteria you analyse your data with. If using Power Query/pivot then add slicers so that the user can look back (and forward if necessary) and cross-analysis without having to save the file.

    Try not to store data in a spreadsheet. Most data can be useful across business areas and better resides in a database. If you need to store in a spreadsheet, then store in a DB format so that others that need that data can use it for their own needs. Don't assume that the person receiving your report will have everything they need in your beautiful design, if they need to add their own data then they shouldn't have to spend lots of time re-engineering your data back into its raw state just to add a few more columns for their own use/presentation.

    Also, use good naming conventions when saving a file (I usually add a save button with some VBA so that the user isn't in control). Use the criteria of the report to name the file. For example, August's report for costcentre 12345 could be saved simply as MyReport_12345_201908, or (more likely) MyReport_201908_12345. It is significantly quicker to access reports that have been saved this way as you don't have to move up and down the file structure to open your workbooks. For example if I want to compare Balance Sheet 201908 v 201808 for costcentre 12345, I simply have a folder called Balance sheet with well-named files. That way I don't have to click Balance Sheet/2018/August/12345/BalSht.xlsx and then back, back, back.. 2019/August/12345.xlsx. You can even write a simple Excel utility in VBA (or even just using formulas and hyperlinks) that opens the files using their properties of year, month, cost centre, or a utility that extracts them to a single folder for audit purposes or sharing. Naming consistency and logic keeps things neat, organised and simple.

    Reply
  • Rico S

    A controversial one perhaps, but:

    "never link a formula to another formula"
    (caveat: unless you've named that formula as if it were a measure, such as "sales total")

    Reply
  • Doug H

    Great tips! Thanks for the video

    Reply
  • Russ Streifert

    Good tips, as always!

    Reply
  • Russ Streifert

    One thing I do which may or may not help others, is to put a header row Title that matches the tab sheet name, so that when you click between tabs you have an additional reference to identify where you are.

    Reply
  • Martijn Fransen

    nice Leila. defintely agree with you on all points. One i like to add is that I try to anticipate what the questions might be when presenting my work, having the ablity to quickly change the file by making on and off switches and selection menu's helps to answer questions of my co workers quicly. also your last point is key.. I try always to mkae my files clean and well structured.. takes a bit of time but it helps to present your work.

    Reply
  • Wayne Edmondson

    Hi Leila.. great tips.. especially "Prepare For Print". I constantly get workbooks without print settings and have to do the setup myself each time.. VBA to the rescue.. haha! Content creators should always consider the next person using the workbook. Your tips are excellent. Another one might be to highlight or in some way make an annotation if there are links in the workbook to external sources.. makes it easier for the recipient to understand what is self contained within the workbook vs. external to it. Thanks for sharing. Thumbs up!

    Reply
  • Ahmed Ali

    You are just amazing..
    I always learn something new in every video that you raised..

    note that, I aleady takes 2 Cources in udemy done by you Teacher and my skills has been upgraed to next level.

    Thank you a lot 🧘‍♂️ . 🙂

    Reply
  • Signal Mas

    I’ll add to the list: #9. Freeze panels for large set of data.#10. Save file with active cell where your relevant data starts (begining of the document, usually the top left corner sheet) on EACH SHEET.
    Under #8 my must does are:. (a) For data pivoted, I spend time removing the default pivot format; (b) I remove the grid lines for any data summaries, but do leave it on on sheets with raw data;

    Reply
  • Eduardo Ribeiro Edu

    Vc é ótima Leila!

    Reply
  • kinan pervaiz

    can you please have a video on how to create racing bar graph in excel

    Reply
  • qamar zaman

    Good like your all v

    Reply
  • Badreddin Alsabbagh

    Kindly explain how we can connect multiple sheets to use in one Pivot table.

    Reply
  • Dagwould

    Calculate everything! I had a team member who calculated in her head or calculator then hard entered the resu!

    Reply
  • Marios HP

    1. ✔️
    2. ✔️
    3. 🤯
    4. ✔️
    5. ✔️
    6. ✔️
    7. ✔️
    8. ❤️

    Reply
  • Windgonner

    Less film/photos of your self would be a great improvement. I mean, this is not a beauty pageant, but an instruction/information.

    Reply
  • John Heyrich

    Thanks Leila…do you own a company or work for one? I have a Traveling Salesman Excel Solver for 50 nodes if you would like to see it. No Macros. Email: [email protected] Thanks!

    Reply
  • Gin Tomino

    you're the best teacher, nice tone and pacing

    Reply
  • taizoon dean

    Thanks for reminding for basic hygiene check. It is really helpful.

    Reply
  • Dhananjay Kansal

    Once again. U fail not to impress us. I’m gonna take your excel course. Hopefully, it should have more than your YouTube playlist. 😉

    Reply
  • amet riyadhi

    Hai Leila Gharani please help me to soulve index match multiple values in one cell…

    Reply
  • Dave Goodmanson

    Very helpful tips Leila, it’s always useful for the finishing touch, thanks!

    Reply
  • quasipseudo1

    Great value able info, as always. Good job!

    Reply
  • Amir

    This is one of the best YouTube learning channels, and it is for free!, it helped me a lot with preparing my works, thank you very much.

    Reply
  • Bart Titulaer

    Thank you very nice tips!! I will add a few: do not center numbers (how ugly is it then when to compare 1000,897 with 1,9 ?, so always comma's lined up, or even better: not decimals and a thousand seperator. If you want to present a small calculation to people who understand Excel, use FORMULATEXT. Use data validation and build controls (for example when was the data last refreshed?). Keep your good work!! thanks again.

    Reply
  • krn14242

    Great tips. Thanks Leila.

    Reply
  • mike hamweene

    You have no idea how even in a short video, the lessons are ever elevating. I wish i could thank you in person

    Reply
  • __

    [15734] y'r flyx R awesome! Do U have or plan to do a dynamic calendar (W/M/Y)?

    Reply
  • Subhendu Sahu

    plz make a video for fetching various results from a webpage by providing various inputs to the cells from excel and putting it into respective values into the cell of the excel. e.g I have two columns (A1 and B1) whose value are 5 & 10 respectively, now in te website I have to put the values to resptive field say 1st Number and 2nd Number and the result in the page says 15 and now I want to put the value "15" to my excel cell no C1. And this should be repeated till my last row.

    Reply
  • Bhavik Thakkar

    Mam how to cop with one or more circular errors in formulas?

    Reply
  • techsimmons

    I'm a perfectionist and spend hundreds of hours making a sheet look nice.

    Reply
  • Maqsoodul Hassan

    Hi Leila, I have been watching your videos lately and they are very helpful. A question for you, I have tried to find an answer googling but I haven't been able to find out an answer or fix. I have a custom sorting list and it works fine if a create a pivot table in the same workbook. But if I import or connect data using New Query From File option under Data ribbon to another workbook and try to create a pivot table, it sorts the data alphabetically. I want to keep the data and dashboard separately i.e. in two different workbooks. Any suggestion.

    Reply
  • Vishal Divakar

    Thanks Liela . liked the saying 'You eat with your eyes first' very well goes with the context of the video.

    Reply
  • Emre Altinoglu

    Dear Leila, I wonder if you can make a video about extract data consisting of more 300.000 records with formulas based on multiple criteria. Not solved easily from my side.

    Reply
  • Artur Markowski

    I must be advaced in exel. I was just doing that naturly with common sence before I watched this video.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *