How to Design Good Looking Spreadsheets - Google Sheets

all right so in this video let's talk about some techniques we can use to make our spreadsheets look nicer so in this case we have this data in this worksheet see some information a bunch of columns here so let's try to make this look decent so i'm gonna start by adding a column here on the left and maybe doing a couple of rows on top and the first thing usually you would want to do with your worksheets to make them stand out as far as design goes is to take any existing design out so that means first you want to probably remove the grid lines so i'm going to go under view and do this checkbox gridlines i'm going to remove that and see that's going to take that away the second thing you probably want to do is try to find a better font for your spreadsheet so by default we're gonna have this ariel font so what i'm gonna do i'm gonna click on this left upper corner to select the whole thing then i'm gonna open this fonts and you may have some here but usually the list is pretty small by default but you can always go under more fonts here and get some more fonts from google fonts so basically all these fonts you can see here they're not available unless you check the box next to it so for example if i want this to be available i'm going to check that box if i want this to be available see i can check a few boxes here so then i can hit ok that's not going to change the font it's just going to make those fonts available see now here so now we can just go here and test some of these fonts out to see what we like so maybe this one i'm going to try that so that's the way that looks that's actually pretty good i like this font yeah i think i'm going to keep this so that's going to be the font so you want to play around you know see what you like the next thing i want to do since we removed all the grid lines here i want to have some sort of separation between lines so for that i'm going to use some sort of alternating colors so i'll just select the data so i'll just do control shift right and down in this case and i'll do another control shift down to select all the way and then i'm going to go under format and use this alternating colors and that's going to give us these options now in this case i did not select headers so i'm going to uncheck this headers out of here and then we have this basically see gray white coloring that goes on here now if you don't like that particular coloring you can change the shades here and things like that i like this so i'm gonna keep it as is so i'm gonna hit done so let's now add a little bit of borders so i want to separate my column headers on top so i'm going to select this so ctrl shift right so select the headers and then i'll go under borders here and under borders we're gonna have different border options so you can choose different border colors so i'm gonna do a little lighter gray color here and then the style of the border so i want the little border below this to kind of separate this from the rest i'll try to double border on this to see what happens so i'm going to check that and after i check those i'm going to click on this one to do a border below so you can see how we do this double border now below that header line so that looks pretty decent the next thing i want to do is separate this columns so we're going to add some borders to separate these columns from one another like for example this id column so i'll just do ctrl shift down a couple of times then go here and i don't want double borders anymore so i'm going to change this to a single border like this and then i'll do the right side of my selection so that should basically just add a grayish line as you can see let me just zoom in here see next to this ids and that's same thing i'm gonna do for this one we probably need to do this for most of these columns i assume so let's just try to select the entire data and do it in bulk so i'm going to select this whole data set and then go to borders and then i'll try to do this middle borders vertical that should just add some lines across this area i'm not sure if i like the color being the same gray in this particular case you could play around with this you could spend unlimited amount of time trying to figure out what design you should do to make it look nice that's one of the reasons i don't play around with design way too much but there it is so for example we could do that have some yellowish lines in the middle the next thing i want to do is take care of these dates these are not proper dates so as you can see it's basically like the date and then it's t00 it's a javascript date so i want to take care of that and convert them to a date so i'm gonna select this then i'm gonna go under edit and find and replace and what i'm gonna do i'm gonna basically find the t and remove everything starting from the t and after and i'm gonna do that using regular expression so i'm gonna check that box so i'm gonna find t and then dot star means all the characters starting from t and after and i'm gonna replace it with nothing so i'm gonna select that to replace all see now that converted that to proper dates much better i also want to remove this permit because it's like permit dash something so i don't think we need that so let's just remove permit dash out of here so i'm going to take this to find and replace and we're going to search for permit space dash space and we're going to replace it with nothing so i'm going to replace all there it is that should take care of that let's also make our headers look decent so see they have all these like underscores in them let's get rid of all those underscores really quickly here so i'm going to select control shift right all the headers and then i'll go under edit find and replace and we'll just find the underscore and replace it with a space replace all hit done go back here i think that looks cleaner i think there was an extra space here after the permit because of that extra underscore so i'm gonna get rid of that manually now i'm gonna make all these headers centered so i'm gonna take all of that and do this centered headers and i'm gonna also make them a little smaller in size that's all right so now let's also decide how we're gonna fit this columns and both sizes we're going to apply so the first thing i'm going to do i'm just going to auto fit all the columns to see what it looks like to do that i'm just going to click in this top left corner and go between any two columns in the middle and just double click and that should auto fit all the data in the columns to make the columns whatever size they should be some of them are not going to be great like for example this one at this point i'm just gonna play around with this to get this right this one can be smaller this date columns so i probably want this to be fairly small like this but then this is not going to fit but we can fix that by applying wrap text to this headers so i'm going to select all the headers go to this option right here and use this wrap option the second option and that should basically convert this to a multi-line text see how this sits like this and because of that i'm now also gonna do this vertical alignment to do center to put them like this and possibly even a little smaller font something like that so now it fits in a column like this i will probably select these dates and center them so it sits in the middle see street direction now we have this option to have our columns a lot smaller because we can basically have the headers go as a two liner instead of one liner i'm going to select these three columns together and resize them to have them the same size so when you select multiple columns like this and you resize one it will resize all of them to be the same size which is nice when you have something like this and i'll probably just make them center too like this that's good this column is not looking good this work description it's too large and see even if i try to resize it i'm having hard time so when you have situations like this this is what you do you can right click on the column header and then over here you should have this option resize column this should give you this box where you can just type a number so i'll just do something like 100 as a size and hit okay and that should make it a lot smaller and now i can control the size myself whatever i feel that should be like now these number columns again similar to these i'm gonna do the same thing i'm just gonna select all of these those should probably all be about the same size probably not including this one but i'm gonna select all of them and resize this and i'll just center all of it looks pretty good then this one can go even smaller so the next thing i'm gonna do here i'm gonna take care of this true and false and yes or no columns so when you have this binary columns where you only have two possibilities yes or no true or false you can make them look nicer by basically converting them to a checkbox so to show you how we do that i'm going to just select this and do ctrl shift down or command shift down and go under data and do data validation and here we have this option for check box now for this column we have yes and nose the default behavior for checkboxes is to have trues and falses similar to this a b column but this one is yes and no so what we need to do we need to use this use custom cell values and then for true we're going to make it yes which is the value here and for unchecked we're going to do it no and you can reject other input if you want that's up to you at that point so i'm going to hit save and you should be able to see how we now have see these check boxes and what should happen is that every time it's a yes see we have this checked box and if it's a no basically it's unchecked just like that so we're gonna do the same with this paid true and false so i'm going to select the whole thing go to data validation and use checkbox and this time i don't have to do this custom values because the values are already trues and falses so i'm just going to hit save and see like this one is unchecked because it was a false so that's that the next thing i want to show you is how to make things look less busy by using number formattings in a nice way so for example if you look at all of these numbers over here there are tons of zeros here and that becomes really busy to look in this data because there are so many zeros so it would be nice to just not display the zeros at all and just display the rest so i'm just gonna select this shift right ctrl shift down to select all of these and then i'm gonna go under this one two three and do more formats and i'm gonna do custom number format now when you do custom number formatting this is the formatting that you have by default see this is what it gives us what the default formatting is for what we chose now what we can do we can copy this and then we can do a semicolon and i'm going to paste that after that semicolon and basically whatever you have after the semicolon is the way negative numbers are going to be formatted and then if you do another semicolon and then do another one that should be how zeros are formatted now for zeros i don't want any format i just want to be empty so i'm just going to remove all of these after that semicolon so basically if you look here see it gives us the preview how the zero is just gonna be nothing so i'm just gonna hit apply so now you can see how that cleared all these cells that were zeros now the zeros are still in here if you look here in the formula bar see the zero is still in here it's just not displaying so this way it looks a lot cleaner now because we don't have anything in here this is overflowing so we need to stop that from happening so i'm going to select this column and then i'm going to go here and then we're going to use this clip but i don't want to apply that for the header i'm just gonna select starting from here and down and then go to this and do this clip option which will basically prevent it from overflowing the cell so i'm gonna do the same formatting trick for all of these two so that formatting that i did it's already here see the first one second one and the third one is zero so i'm going to just apply it without having to go back to that box let's see if i have more i believe i do not much happening on this side we do have all of these good now you can see how everything looks a lot cleaner like this because we don't have all those zeros like sticking out and now it's a lot easier to read the spreadsheet because of that i also have all this weird zip code see where it has the dash after the zip code and then i have this other zip codes which i'm not a big fan of right now as i'm looking at it now there are these full zip codes too see in this case now we could either trim this starting from the dash and going right or you might want to keep like these but remove these dashes when there's nothing after the dash i'm just gonna do that i'm just gonna remove anything where it's nothing after the dash so i'm going to select this go under edit find and replace and then because we're trying to do something complicated we're going to do regular expression to do this and what we're going to do we're going to search for basically something that's a dash and then we want after the dash to have nothing so we're going to do this dollar sign in regular expression that means ends with the dash let's try to do replace all and see what happens if we're lucky these would be now looking nice and this will stay the same so let's do that and see what happens very good as you can see we've removed all of those but these stay because we did regular expression to just target the ones that end with a dash community area i'm just gonna make this centered and make it smaller keep the rest as is i suppose and go back to the left let's see what else we might want to do here now once we have this now because we have all of these columns we may want to separate different sections using colors so that we can kind of see that this is different from this other part something like that so for example like these two columns are dates so what we could do we could just give them a little bit of different background color so i'm going to just select from here all the way down and then go to this background color and choose some background color for the states so that it kind of groups it together using the color right so you can either choose one of these existing colors or you can go custom and just pick your own color from this whole thing you can play around with this to see what you like i'm gonna try to do something like this purplish color hit okay again you can spend as much time as you want with colors it's really up to you and find whatever colors you want to do i'm not gonna concentrate on colors too much it's really subjective so with the same logic if you look here we have the street information basically the address so these four columns i'm just going to select all of them and group them together using some sort of color again something like that so as we go through this you may want to color code some things so for example when you have these check boxes see this is unpaid fees so maybe we want when this is checked we want it to go red to highlight that this was not paid to make this check box kind of go red i'm gonna use conditional formatting so i'm going to select this whole column go to format conditional formatting now if you remember this first column of checkboxes was yes and nose behind the checkbox and the second column was true and false now this is yes and no so this checkbox means it's a yes so what i'm gonna do i'm gonna open this and choose text is exactly yes and see how this went green now i don't want a background color so i'm going to take that off and i'm going to change the font color so the check box actually is the font color so i'm going to change the font color to some sort of red see that went red like this you can play around with different shades of red to see what you like hit done so you can see now these are red so you can check this see it goes red uncheck it it goes to the default gray color you could also do some sort of conditional formatting obviously for nose 2. so you could just go back in here and just select the same data range and do add another rule and then this time you're gonna do text is exactly no and now it selects all of these nodes i'm going to make the background color none and then you can play around with the text color to see what you like that's going to be actually the color of the checkbox so you can just see what you like you can turn them green if you want like i said you can spend unlimited amount of time playing around with colors and seeing what shades of colors you prefer but now you can see we have this nice check boxes that have different shades depending if it's checked or not which is the same type of thing you would probably do for this paid and not paid so in this case paid would be the positives and on paid would be the unchecked so again same procedure i'm just gonna do a quick conditional formatting here now this time it's true and false it's not yes and no so what i'm gonna do i'm gonna say is equal to true so that would be all the checked ones i'm gonna remove the background color always keep in mind you can go custom something like that you hit done now you have all of those that turn green so if i check this see that's what's going to happen now you can also use conditional formatting to kind of highlight some numbers so for example let's see what type of numbers we have here we have this paid fees we also have this see this is subtotal these are unpaid right here so maybe you want anything that's um paid highlighted here in a color see how we have right now these numbers so i would just select this area like this and then we can again add some sort of conditional formatting so it seems like any time we have an unpaid fee the fee just pops up here as the amount so i'm just gonna add another rule and this time i'm gonna check if it's greater than zero then we're gonna apply some formatting this time i will probably apply some background formatting too like this and when you do a darker color for your background you probably want to change the color of your font so that you can actually see it these are all the options unfortunately right now it doesn't allow you to do borders hopefully at some point it will so i'm just going to press ok so now it should just pop up anytime there is unpaid fee see it's red and you could even just go with that same color options for all of these like separators in this column so we can just select all of these because these are all on page related stuff and do some sort of border for all of those in that same red color and again that kind of helps it group more together and you can see how that's actually a pretty good strategy sometimes when you don't want to apply a whole background color like this you can just group it using those borders so for example these are unpaid fees i just went with this red stuff and the same way i could go with this waived fees to go with some other color so it kind of groups that part together in some way so i'm just gonna select this and then go here again under the border and we can see what other color we may want to do for this for this to work out so there it is now we have this wave fees here this unpaid fees here and then we got our total fee so i will select this column and let's change the background color for this one just to highlight that more so it pops up a little better i'll go pretty dark on this one and i'll probably just change the text color to white now when you start going really dark on your background color at some point you may want to change the way you apply borders here so there's some sort of separation so what i could do i could just take all of this and add some borders in the middle in a white color so i'm just gonna do this white gray or you can just do a lighter shade of the same color that usually tends to work pretty well so if i for example select the same blue color that i did and then i can just go custom and just go for a lighter shade of that same thing and press okay so now let's apply that in the middle like this to have separate lines so i can click away to see so maybe that was just too much white so i'll just get back to this select this and try to just make it a different shade that's a little closer to our color so i think we should just go maybe a little less but not that much different so i'll just go with that and again you could apply that to the right and left too so maybe i should do that let's see what that's going to look like i think that's decent so we'll go with that you can also apply some sort of color scales when it makes sense so for example this processing time see that basically just goes eight days 31 days zero days and so on so maybe you want to highlight the ones that are taking too long in a particular like shade and the ones that are taking last to be a different color of some sort so i'm just gonna select all of that and go to format and do conditional formatting and we'll go to this color scale option now here we have this minimum midpoint and maximum value by default it will just pick the minimum and maximum from the column and kind of go with this grade see how it went for the minimum it's basically green which is this color and for the maximum it's white which is this color over here because that number is pretty large so let's change this let's have the minimum value to not have coloring like white of some sort and the maximum will go like redder so it kind of see highlights the number something like that and then we can have a midpoint and see that midpoint can be a percentage so you could say like if it's like 50 do this color but for me because these numbers are so different i'm just gonna pick that midpoint as a number and i'm just gonna say if the number is between that minimum value which is 0 and 200 so i'm going to say the midpoint here is going to be this light red color whatever that's gonna be and this one will just go to a whole different like scale we'll go to like purplish so something like that so if i hit done so you should see what happened here we have all of this color scale and then we go to this purple color for things that are taking just way too long so i'll just center this so let's just do a quick before and after to see what it used to be like here it is this is where we started and this is what we ended up with so that's that and this is after all of these changes so that should give you most techniques you need to know to make your spreadsheets look more presentable and just stand out and the more time you spend on design and colors you can make it look nicer so let me know in comments if there are any other ways you like to use to make your spreadsheets look better but for this video that should do it thanks for watching please subscribe and i'll see in the next one

Channel: Learn Google Spreadsheets

Views: 72,041

Keywords: Spreadsheet, Design, Google Sheets, how to, formatting

Id: aA7IYLmm6RU

Length: 28min 59sec (1739 seconds)

Published: Thu Sep 24 2020