How to beautify your Excel Sheets | Niels Schellekens | Skillshare

How to beautify your Excel Sheets

Niels Schellekens, Excel Fanatic

How to beautify your Excel Sheets

Niels Schellekens, Excel Fanatic

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
12 Lessons (40m)
    • 1. Lesson 1: If Empty, Keep Empty

      2:53
    • 2. Lesson 2: Colour Coding

      3:23
    • 3. Lesson 3: How to make a dropdown

      3:55
    • 4. Lesson 4: Conditional Formatting

      4:25
    • 5. Lesson 5: Protecting Cells

      2:12
    • 6. Lesson 6: Restricting Cells

      2:36
    • 7. Lesson 7: Trimming the Edges

      2:31
    • 8. Lesson 8: Adding the Form quick option and how to use it

      4:22
    • 9. Extra Lesson 9: Auto Translate

      4:33
    • 10. Extra Lesson 10: Grouping Rows and Columns

      3:42
    • 11. Extra Lesson 11: Slicers

      2:18
    • 12. Extra Lesson 12: Sparklines

      2:51
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

27

Students

--

Projects

About This Class

This class will teach you the often neglected art of visually and functionally improving your Excel sheets and how to make them easier and prettier to use for yourself and especially others who don't know the intricacies of your sheets when you're making templates.

Meet Your Teacher

Teacher Profile Image

Niels Schellekens

Excel Fanatic

Teacher

Hello, I'm Niels. I have been teaching Excel to interns and clients for well over a decade and I hope my simplified introduction to Excel Spreadsheets can help you in starting to see the treasure trove that Excel can be for you!

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

phone

Transcripts

1. Lesson 1: If Empty, Keep Empty: welcome to beautify your excel sheets. My name is Neil Skeletons, and I'm also known as you. I've worked with Excel and beginning users for close to 10 years and no value off, making your sheets as clean and usual responsible. My goal for this course is not teaching formulas. Oh, programming, Vehbi, eh? But my favorite tips and tricks for beautifying your sheets instead. For the first lesson, I want to start with my favorite one, which I call if Mt keep empty. The amount of times I've seen template like these are astounding. The first thing you will notice are these so called naked formulas, not results in errors. I've seen this intimidate or confused many. You begin a user, so want to illustrate now how you can make this prettier unless intimidating for the end user the way how we are going to do this is only that output one calculate. When both in football ends to have been filled. The way we are going to do this, you start off with any statements that checks. If eider see tree or D tree, it's still empty. So we are making an or statements off. See tree still empty or thes de trees still empty. Now we close off your statements. If eider is still empty, we want to keep this formula empty and only run. Both have been filled. Do A wants to calculate the actual formula. If we know drag this through all the other rose, you will see that output one will not be calculated as long as either include one foreign. Boudou has been filled on its own only wins. Both have been filled well, Output won't calculate the reason why I like to check if empty, keep amity instead off keeping it empty at the ends off the formula. This way is easier to change if statements or the formulas. If Excel notices that the first thing it needs to do is ignored rest of the formula you will make your sheets so much false. I hope this lesson was helpful to your sheet making creativeness, and I hope to see you in the next lesson. 2. Lesson 2: Colour Coding: welcome back to beautifully your Excel sheets a lesson to I want to illustrate the effect off using color coding in your Excel sheets to help out yet user off your templates. You absolutely do not have to use it in the over the top method. I will show in my example. But having your sheets color coded will decrease the amount of mistakes and questions for your help, because the color coding assisted beginning usual toward finding them problems themselves. In my experience in this example, I have core code. It's in the way I would usually do it, which is using yellow for drop downs, green for restricted input and blue or science in this case for formulas, which are also protected cells. All three off these subjects will be a future lesson. But for now, I would like to fill in a few extra data. Points in this table do illustrate how the schools can assist me in using this sheet. First off the I. D. Is a white known coal itself, so I now know I can input anything I want. Probably the next i d number en Gaisal dinning. It's the same. But for the region. I know it's a drop down because it's cool. Ajello, In this case, I have also added the drop down option in the cell itself so you can see what the choices for region are. In this case, we will pick south no reason. The travel compensation is a restrict itself, which is a number between 20 cents and 25 cents. So if I buy accidents imported 15 cents, Excel would not allow this. In the desktop version. You can also enter a difference error message from the help message. In all life. Spreadsheets is not possible. But as you can see, the pop up is still helpful and it has rejected my includes. So that's correct. That 22 cents, which is fine kilometers travels, is once again a white so so I can put anything I want, which is in this case, 15 hundreds and only now, as you can notice, the trick from Lesson one. If empty keep empty, has been applied in the sheet, it still functions exactly the same. But now, with checks if travel conversation and kilometers travels half being filled it, I hope this lesson was helpful to your sheep making creativeness, and I hope to see you in the next lesson. 3. Lesson 3: How to make a dropdown: Welcome back to Beautiful Your Excel. Sheen's in less and tree. I want to teach you how to make a drop down as you have seen, unless into a drop down is a simple yet elegant way to restrict the in put options on the cell as compared to an actual restrict itself. A drop down will help the beginner user a little more because it shows the list of options . My example. This time, we'll once again be very easy. But keep in mind that you can make drop downs any time you know that there is a limited amount off. Include options like regions lines for your company products you have in stock much more In this example. I want to fill these five cells. We will drop down off the various produce we have in our store. The way to do this. Just make a drop down list with all the possible options. I like to put a line here to indicate that everything below this line are the actual options. So in this case, let's go with apples, bananas, oranges and even que ese the way to actually make the drop down. Now that we have the list of options. He's selecting all the cells. You want to have a drop down. After that, you go through data validation. The validation we need is list from a range which will ask us to select a data range, which is all drop down. This that we just made the option for show drop down list and sell is the arrow we saw in lesson to. If you uncheck this option, the cell will just look like any others without the selection option, which I don't really like. It's much easier if you can just choose from the drop down instead of having to guess only invalid data. Can I just show a warning or flat out rejecting input? Like a lesson, too? I don't like to you show warning for drop down because of drop down is a strict list off. The only options you know exist. There shouldn't be anything outside of this range, so we're going to reject the airport. The show validation helped. Next is the error message that popped up, which we saw a lesson, too, as well. In this case, we can enter something like these. Insert apples, bananas, oranges or humans if we now safe this, we have a drop down off the list we just made. We don't have to use this roll down. We can also just include apples. If we know that down, there's an option. But if we try something else like tomato, it will flat out restrict us and give to pull up, which gives the options as well, Which is nice if you do that. This is helpful. And this is the way how you make a simple drop down in excel. I hope this lesson was helpful to your sheet making creativeness, and I hope to see you in the next lesson. 4. Lesson 4: Conditional Formatting: Welcome back to beautify your Excel sheets lesson for I want to show you value off conditional formatting. Conditional formatting means that when the condition in the cell triggers, it will give that cell a four months to your liking. Sounds logical, right? What I would like to show you a lesson for is how to make a very simple condition entry off my favorite examples in this example on to show simple condition aspirate this lovely request of our excel sheets to make a conditional format, you select all the cells, you want to have a conditional formats and select conditional formatting. What we want to do is a single Goler just read when I'm a five. So the rules we won't to check is is equal to the valuable Fife. The style we want to have. He's red. And though that's all you need to do. Really? Now we have this conditional format, wronging. And if we enter numbers from 1 to 10 you will see that the five is highlighted red. Now, let me close this and show my favorite examples in this condition. I've applied a format that checks for duplicate values in our invoice numbers. which would obviously be impossible. One outside of this condition is that whenever we accidentally typo a future invoice number , excel well, let us know, because we have applied this condition to the employer vehicle. In my second example, I've done a check, if going be good things. The number five the usual way. You would do this with a simple, if statement, displaying a one if it does contain a Fife and zero if it doesn't personally, I prefer using a condition to call a green for a one and red for zero, which is visually quicker to notice. If you have a gigantic, least, well, checks to do, you can also do many checks, which ideally colors to be more useful for. And in my last example, I have applied a condition that calls anything higher or equal to 50 green. Anything higher or equal to 25 yellow and anything below 25 red To quickly given indication off the winding stock at the end of this lesson, I will ask a screenshot off one off my ultimate favorite ways to use conditional formatting in the desktop version of Excel. I hope this lesson was helpful to your sheet making creativeness, and I hope to see you in the next lesson. We are s promised in the desktop version of Excel, which is in my native language, which is why I will try not to use it if not necessary. But in this example, I have applied a conditional format on this stable that shows me if 2018 compared to 2000 and 17 as I do reason fulling Allstate the same. Do me when checking differences in numbers. I am a financial advisor. So in my case is these stables can be hundreds off Roshal. It is so much easier. Do notice these cycles or any other ones you want to use out of the Eichel's compared to just checking numbers if they changed. 5. Lesson 5: Protecting Cells: Welcome back to beautify your excel sheets In Lesson five, I want to show you devalue off protecting cells. A lesson to we have used the example of protecting cells contain formulas I should don't want tea and usual toe accidentally or even purpose Tree changed these it is example. I will show you the exact same application of protecting cells and how to set this up, but with an online spreadsheet twist which might assist you and working together with others in the same online sheet. For the desktop version, protecting cells works the same, but obviously it's a flying based on the sheet itself. The formula in this example is are good old friends. Am I the number five years or no? And as you can see, gold coating has also made this return. So in this case, we want to protect all these formula self. So first, we are going to select all the cells we want to protect. Don't go to protect, range and set the permissions. You can even enter a description like protecting formula, and in this case, I want to show you show warning when editing difference. This is obviously unique to online fact sheets, and if we want to change this formula, it won't outright stop you. But it is going to give you a heads up that you are trying to edit a part of the sheet that shouldn't be changed accidentally. Would you like to cancel or ended in anyways for online spreadsheets? When you're working together, leaving these sort of hints for each other can really help make Excel more efficient and more beautiful to youth. I hope this lesson was helpful. Do your sheet making creativeness and I hope to see you in the next lesson. 6. Lesson 6: Restricting Cells: welcome back to beautify your excel sheets in less than six. I want to show you value off restricting in boots by using data validation. A lesson to We have used the example off restricting cells containing in boots with a minimum and a maximum value. I should don't want the end usual toe accidentally or again, even purposely exceed these bounce in this example. Also show you the exact same implications restricting cells and how to set this up. As you can see, we have returns to our table from lesson to but I've turned off the import restrictions. You call him E to show you how to set up the restriction we have before. To do this, you select all the cells that you want to have a restriction and go to a data validation. In this case, we do not want this from a range like the drop down we made. But we want number. We want to restrict these cells between 20 cents and up to 25. So we pick between 0.2 because the lower bounds will be included. But we have two big 0.26 because the upper bounds off 25 cents will not be included. To be even more accurate, you would have to do 50001 You get the point. But in this case, I want to point out that maybe in this case, show warning might be better than straight up rejecting the in boot because normally 20 cents and 25% started bounds. But maybe a supervisor or used a sheet and he will have the authority do make an arrangement with home one that if it's a long travel, it will be 30 cents instead. So the norm is 20 and 25 and we will make this validation based upon 20 cents and 25% with that as the help text. But it will not stop us from in putting, for example, 29 cents. I hope that difference between rejecting and showing the warning. Sq. And I also hope this lesson was helpful to your sheet making creativeness, and I hope to see you in the next lesson. 7. Lesson 7: Trimming the Edges: Welcome back to beautify your Excel sheets in less than seven. I want to show you value off one of my absolute favorite methods off, making a sheet look so much prettier. And we will do this for political dreaming the edges, which is a perfect way to hide your formula, crimes or intermediate calculations and clean up the look off your templates. In this example, let's return once again to our lesson to table. But for this example, let's imagine that corn G had a very complicated, multilayered formula that you didn't want to do in one long formula where you can make a mistake for this, I have made some Mark Collins do the right to visualize how this would look. It was actually the case. Also, let's imagine that these would be checks you would have to do before calculating the formula or not, and zeros wants, as you know, how big this template is going to be. Which is this stable and a little bit of room for whatever look you have, you can hide these crimes by trimming the edges and the way to do this. You're standing in the first column you don't need to see anymore using control shift on the right arrow. You will select all these goals and now you can hide these. The same can be done with Rose, but here it is cultural shift on the down arrow instead, and we can hide these rows. Do un Heidi's in online you f this handy little arrow. But in the desktop version of Excel, you'll stand in the law school invisible Use cultural shift right again, and then you can unhurried the columns, so height unharmed. I hope you will use this method to make your template look exactly as big as they need to be. Because this is so much prettier and, well, open up your options off, making your formulas as easy and convenient for you as needed, but still keep the sheet as pretty and convenience as needed for young usurers. Well, I hope this lesson was helpful to your sheet making creativeness, and I hope to see you in the next lesson. 8. Lesson 8: Adding the Form quick option and how to use it: welcome back to beautify your excel sheets in a final lesson Number eight, I want to show you a hidden option Many users Off Excel don't know about which is very helpful when working with in putting data into data tables. If you have ever worked with myself access, you will know that you can make forms to input data into tables. But what if I told you the dysfunction is also mostly available in Excel? To show this hidden, functional well, soundly have to go back to my desktop Excel, which is in English, for which I apologize. But I will mention the English name off the function and menus we will be needing. Be warned, however, that this is slightly more false but will be worth your adventurous spirit. As you can see, we're once again back to the table from lesson to, but this time we're going to add to the quick function for a form. To do this, we will go to the quick function bar at the top, where you can usually find your safe bottom you undo and UV do. To add another quick function, you click on this little arrow. Eichel go to more We will pick from all off the functions. And this list is literally every function in excel that you can use and you can add it to your quick bar. In this case, we will look for for in English. In my case, it's gold for millions. You can ask this by using this, Abbott, and it will go into the list off your quick bar. I already have to form in my creep are so we can close the screen, select the entire table and click all form button. And now we have the data table one row at a time in this form, and we can also choose to add a new one. And if you add the i d end for John Fleming Derek, very close this it will be added to the table if you have a little things to do or a very white with a lot of columns table. This form function can be so much more useful, and I also want to point out that in the online stretching version, you can go toe insert, select the form which will be added to your Excel sheet at the start, and this is a table that's connected to a form that will now open that you can set up in your line spreadsheets and vitamins and ask questions. Multiple choice is jack boxes, drop downs, anything you need. And whenever someone will submit this form, it will be added to this table so you can reference the stable from anywhere in your sheet . This is life updated so you can do calculations If you know your form is to avoid, you can even start making your own calculations year. It will only aunt to A and B. It will north do anything with C and further so you can use this form. What your own calculations as well. I hope this lesson was helpful to your shed making creativeness. And I hope in general you enjoyed this course. I hope I waas simple yet all inclusive that you need it to do it yourself. And I really didn't want to over Culture Gate. It's so I hope I have achieved in Laigle and I hope to see the things you have made by using discourse, and I thank you so much for watching this 9. Extra Lesson 9: Auto Translate: Welcome back to beautify your Excel sheets. In these extra lessons, which has been added often the main course, we'll discuss subjects not necessarily improving usability, true looks, but simple, useful formulas. What tips and tricks instead. These actual lessons will stay for the beginner user. And bones involve complicated formulas, VBA, or complex possibilities. Just the easily achievable tips and tricks like the form from less than eight. This first lesson is all about the online spreadsheet, exclusive, embedded Google translate. This can be used in a lot of ways, especially considering online spreadsheets can be used in a much more interactive way. For example, between multiple users, multiple input sources, and of course even multiple native languages that you might not share. Which leads us to this extra lesson, Google Translate, which can be set up in various ways, like translating specific columns. Because the output is needed in a different language. Or translating into your sheets because you're collaborating with others. Or even translating incoming forms which have been set up so that the users can use their own language to answer your forms. But for you, it will automatically translate back to the output you need. Next, let's show how to implement a few simple examples that will teach you how to use the Google Translate formula for yourself. Let's solve these together. You need to export this text in Dutch. In this case, we will use the Google Translate formula, which has three parameters. The first one is what texts are you translating, which in this case is B5. The second parameter is the two letter language code for the language you're translating from. So in this case it is e n, the two letter code for English. Lastly, the third parameter is the two letter code for the language for translating to which in this case is n, l, nl, or nail salons or Netherlands, is the two lateral language code for Dutch. Having this, you can enter the formula and as you can see, it translated it from English to Dutch. Let's do this again. But this line, you know, the incoming text is French. So again, let's use Google Translate with the first parameter being the text you're translating in V8. You know it's French, which has the two letter language code of f r. And again, we want to translate it to English, which is e n. As you can see. Awesome, Thank you for this example. And lastly, you have no clue what language this is, but you'd like to translate it to English. In this case, let's use Google Translate a final time. Select the text you want to translate. But in this case, you don't know the two latter language code. Well. In that case, the Google Translate formula has you covered for the parameter to. You can also use, although for which Google will try to find out what language this is. And the third parameter will still be EM for English. As you can see, Google found out that this is finished and translated into English for you. And like the text says, I hope this was a helpful lesson. And may it assist you in your sheet making creativeness? 10. Extra Lesson 10: Grouping Rows and Columns: Welcome back to beautify your Excel sheets. In this actual lesson, I will show you how to make groups in Excel. The reason this has become an extra lesson, instead of being part of the main course is a recent change in how groups work in online spreadsheets, which I will explain later in this course. Groups can be incredibly useful to keep an overview of a really big list or table in the cases that you need these details, but don't always need them. In this example, let's have a look at the profit and loss statement and why groups can be helpful. As you can see in this profit and loss statement, we have three profit categories and 5 costs categories. You can also see the totals for both. Let's say in this example, you want to keep seeing the total costs, but don't always need to see the specificity of the five cost categories. In this case, let's make a group. All of the costs categories. Keeping in mind that when you make a group in Excel, that group will collapse entirely. If you want the total costs to stay visible, you do not include them in the group. So let's make a group for the cost categories. In this case, we will select rows eight through 12. Right-click and select group rows eight through 12. As you can see, Excel made a group indication and the close and open button. But as you can see, really counter-intuitively, online spreadsheets puts this button at the top, which is opposite from offline Excel. And it doesn't really make sense, does it? I mean, this button involves this total down here. Wouldn't it be much clearer if this button wasn't next to seven, but next to 13. Well, In a more recent change in online spreadsheets, they have added the option to right-click the group and select this bottom option which says move the button plus minus to the bottom. And when we select this, you can see it's now next to what this group is about. So if we close the group, it is very easy to see, oh, this extra details of this group have to do with the total costs right next to it. And if we open it again, it stays there. So that is the reason why I waited to make this an extra lesson instead of adding it to the main course. Because of the option to swap the position of this button. All of these options also work for columns. Should you have a very wide table that you don't always need every column 4. I hope this lesson was helpful and made assist you in your sheet making creativeness. 11. Extra Lesson 11: Slicers: Welcome back to beautify your Excel sheets. This actually lesson will be an offline Excel exclusive. The slicer with buttons. A slicer is an adult to tables in Excel that allows you to make preset filters of the columns you will use often when looking at the table. Let's have a look at an example table and what the slicer looks like and how it can help you. Imagine this table is used by all for division chiefs. On this table, I have applied a slicer on the division and a slicer on the employee column. We all know how to use filters and how to de-select everything and pick what you need. But slices can do this a lot faster for you. And also conditionally, when I pick the Southern division, you can see that it grays out all the employees that are not from the Southern division. Now, let's see how simple it is to start using slices. Here is the table from before. And adding a slicer is literally as simple as selecting the entire table. Going to insert. And picking the slicer, which will prompt a window asking you for what column do you want to make a slicer? You can make two. And that's literally all you need to make a slicer. You now have a slicer for the division column and a slicer for the employee column. That won't behave like we have shown in the example. If you want to use multiple selections, you can click this icon for multi selection, which allows you to pick the Southern division and the northern for example. I hope this shows you how easy it is to add slicers to your tables. And I hope this can assist you in your sheet making creativeness. 12. Extra Lesson 12: Sparklines: Welcome back to beautify your Excel sheets. This actual lesson will be an offline, Excel exclusive. Again, the sparkline. A sparkline is essentially a mini graph that lives within a singular or combined cell, which can show you a lot of information in a very small space. Let's have a look at two sparklines that a lot of using myself. In the first table, you can see the total amount of sales in every month of a given year. If it's important to you to quickly see if time of year is relevant, then looking at a line, sparkline is much more helpful than looking at the raw data. Sparkline will never be as detailed or involved as a full-on graph. But if something simple like the trajectory of data is important to you, then a sparkline and serve you perfectly well. In the second example, it is much easier to see where the DNA or team be bombed by looking at the sparkline in the middle than it is to compare the score. Now, let's show how simple it is to start using sparklines by sending these two up together. In terms of a line sparkline, it is literally as easy as going to insert section for sparklines, selecting line and picking the data. That is all that is needed to insert a singular cell line, sparkline. The second sparkline is a little bit harder. For one, if we show row line. I cheated. I already calculated the difference between DNA and B. Secondly, if you want to make a sparkline, you have to make it in a singular cell first. So let's do that first. Let's go back to Insert section for sparklines. And this time select Profit and Loss, which is basically a positive and negative sparkline. Let's select the data again. And now that we have made it in a singular cell, we can combine it all to all five cells. And as you can see, we have created a simple sparkline that can easily and quickly show you whether DNA or B1. I hope these examples have showed you how helpful sparklines can be and may help you in your sheet making creativeness.