hi and welcome to part two of our series on how to print a large Excel spreadsheet and as you've seen it can be kind of a challenge well we're going to look at other ways and actually some of the older more default ways of how we can set up Printing and get that spreadsheet to look the way you want it to look on paper hi I'm one of the consumer technology Specialists for Midcontinent public library and let's look at that part two of Excel spreadsheets and printing our example spreadsheet is exac
tly the same one that we used in part one and while I have reset some of the settings that we used in our final product I have kept some of them just for the sake of time time so for example I've kept our adjustments for the column width exactly the way they were in our example one I've also repeated print titles for each page and I've kept the page numbers as well and as we looked at our spreadsheet last time we realized that if we have a cell that has a lot of lines in it that it is probably b
etter to align all of the text to the top of the cell rather than to say the center or the bottom of a cell and so I've kept that top of the cell alignment as well other than that we're kind of starting from scratch instead of using buttons on the ribbon this time we're going to go to a more perhaps oldfashioned way but certainly a very reliable way to look at how we can set up our Printing and to do that we are going to go to file on the far left and click on that and then we're going to come d
own about halfway in the green to print and as we realized in our last session on the right we have a print preview and at this point it says we have 231 pages that we are previewing which is about half a ring and as I scroll down while these initial Pages have a lot of extra space in them we can see that about Midway we have several columns that seem to fit on one page fairly well and so this middle section looks pretty good and then as we come down even further we have a lot of extra space on
this third section so it looks like we are printing down a whole lot of pages and then we're printing about three pages across for each record well what we want to do first of all as we look at our printing options is that we want to come over to the left where right underneath the print button we have a printer choice and you will use this to choose the printer that you want to use and right now this is the printer that I use but below that printer you have printer properties now these change d
epending on the printer you have but you may be able to use some of the choices here at least on this basic tab for your printer as well well we'll just simply cancel out of the printer settings now let's start with with the options below where we choose the printer and it's a whole settings group that we need to look at first of all our first choice here is print active sheets so that is the sheet that is open or if you have grouped the sheets it would be the grouped sheets that you say Okay I
want to print the active sheets now if we click the drop down here it says we could print the entire workbook and while you can do that here is truly a caution for you and that is make sure that each one of the work sheets is formatted the way it should be so that it can be printed correctly sometimes people choose this print entire workbook and they don't look to see if the worksheets are individually formatted which means means that generally there's going to be at least one spreadsheet that p
rints in a way that is not very conducive to reading it so if you print the entire workbook that's fine but be sure to print preview each one of the worksheets in that workbook first to make sure that they are indeed ready to be printed by the way they've been formatted now another thing that we can do is print a selection so let me click that one and let me go back to my spreadsheet and I have at some point here selected some cells and I've decided that I only want to print these cells and that
can come in really handy if you need a quick answer for someone you just select the cells you click file you click print and then here you only print the selection and and nothing else and because of the way this is currently formatted I have one column that is on a second page but very easy to do now let me go back a second I'm just going to print active sheets here and let me go to another place where we can set up printing only selected cells so let me go back and in this case where we're go
ing to go is page layout and in in the page setup group in the middle of that group we have print area and we set a print area by selecting cells as I've done and click on set the print area now when I go to file and print it will only print those cells now the thing about that ribbon button is these settings save when you save the workbook and so when someone else opens the workbook they are only going to be able to print these selected cells which maybe is what you want but occasionally you wi
ll look at a workbook and you'll say wait a minute I want to print this whole worksheet everything on it why am I only getting these few cells if that's the case be sure to look for print area and clear the print area and then you should be able to go back to file and print and print anything that you want to print if you use the option here for printing a selection then that is just temporary because you only want to print this selection but you still want to save your worksheet as the entire t
hing so a slight difference here that you might want to think about as you work with printing one other option right below where it says print active sheets is to say which Pages now these are actually the physical sheets but which Pages do I want to print so it could be that I say oh I want to print pages two to 20 and then it would actually print as I come here page two and then as I go through all of these sheets through Page 20 I've now reset some of our settings here so we are back to print
active sheets and then we're going to come down below that and this says print onesided when I click the drop down it says I could print on both sides and decide whether I want to flip the pages on the long Edge or the short Edge here's what happens with my printer properties if I set those those are the default but if I choose for example to print one-sided even though the printer properties are set to double-sided I could say no I only want to print one-sided this one time so these settings a
re going to overwrite what is here on the printer options so kind of keep that in mind I can print one-sided or I can print double sided and then I can either collate or I can print uncollated as well but coming down from here we have portrait orientation or landscape orientation well we know we have records that go three pages across and for those who are trying to read those records it may be easier if we say needs to be landscape now of course this is definitely a button on the ribbon as well
but it's kind of a nice place where you may think that you need to just print portrait and then when you actually look at the print preview you realize that oh you know what I need to change that setting and so you can do it here with file print now that takes us to 224 pages so we've saved some space along the way way we're only going to print two across but it's still a lot of pages so maybe we can make some other adjustments even from this place from file print that will help us print fewer
Pages below landscape orientation again the default on our printer is one thing but we might come in here and choose another size at least for this particular time that we print although I'm just going to go with letter and then we have normal margins now we can click the drop down here and we can say okay the last custom setting was a half inch instead of 3/4 of an inch at the top and the bottom let's try that that changed it to 200 pagely didn't it but we could also say oh no I need a wide mar
gin or we also have the narrow margin as well and then we have a choice for custom margins that we're going to look at in just a little bit and then we have scaling now remember that scaling lets us reduce really it's the font size so that it fits on one page but a really small spreadsheet might fit on one page and it would save you from having just a little bit of leftover on a second page fit all columns on one page now we might try that or fit all rows on one page we know this is too big and
then we also have some custom scaling options as well but what we will do for right now is leave it the way it is and then what we can do though is go back to page one and down at the bottom right we have this little button that's called show margins and when I click on it you can see how we get these squares at the top and they let us resize columns or sometimes it's the margins that you need to by eyeing how things look change the margins what it doesn't let you do is change the row height but
you can change the margins and you can change the column widths and sometimes that's all you need to make things fit on your paper even better so don't forget this little button down at the bottom the show margins button now let's go below where it says scaling and go to page setup and this link has some choices on it that we have seen before and anytime when we were looking at these settings choices if it said say for example custom margins or custom scaling anything like that those choices wo
uld take us to where page setup also takes us so let's click on page setup and we get a dialogue box that Excel has just always had so so you may be familiar with this starting at the top of the page tab we have orientation and so we could very easily here select portrait or landscape and we have already selected landscape so that's where that radio button is right now now for scaling we could say that we want to adjust to and right now it's at 100% normal size or we also also have the option to
fit to so many pages wide by so many pages tall for us today let's say that we want to make it two pages wide and just leave it at that this is a choice that is not found any place else and that is what is the print quality and at 600 DPI this is actually the best quality and at 200 DPI it would print much lighter it would save you some ink especially if this is an initial print it's not your final print out but a 600 dots per inch this is a more final copy and then the first page number we cou
ld change that from Auto to say one or two or if we're doing some sort of a print out that maybe this is the second portion of a print then we could start at a different page number and it would pick up on that down at the bottom right we're going to see this options Button as we go through this dialogue box and when we click on options it takes us to the same place every time and that is the printer default settings and that is our page tab now to the right of the page tab up at the top left we
have the margins Tab and this this is where you can individually and uniquely set all of the margins so right now we have the top margin as half an inch but maybe we want to set it to something else now keep in mind that if you have a header and down here at the bottom right a footer you would also want to be somewhat aware of where those headers and Footers are set so if at the top margin I say I want to make it 0.25 well the header is larger than that so let's pull it down a little bit lower
and you'll notice that as I make these changes I can also see the line here that shows how these are going to affect my page now I can change the right margin and maybe I want to make it well this defaults to 0.15 but what if I want to make it 0.25 instead well I I can simply come in and type the number that I want to use so on the left right now it's at 35 maybe I yeah when I click the down arrow it defaults a little bit differently than I'd like so I will put a 0.25 now keep in mind that not a
ll printers allow the same margins some of them will require a certain top and bottom margin or a certain left and right margin so you'll need to work within the default of your printer as well and then down at the bottom I can make that 0.25 let me just even clip right here and put in the 025 and then for the footer we'll make it small as well now here is an option that you only see with this dialogue box and that is if you have especially a small spreadsheet you might decide that you want to c
enter it on the printed sheet and so you can Center it horizontally or vertically on that page which is really nice it's a great option so don't forget that it is here within the margins tab now going on from there let's go to the header footer Tab and here is where we can create custom headers and Footers and while we have a lot of options with headers and Footers by changing our view or even inserting headers and Footers this may be where you want to do your work with headers and Footers so I'
m just going to put in a custom header and let's look at the combination here of the text at the top and then our toolbar here that runs across the center as I rest my mouse on that at a it says well this is format text and then I get information about how to format text up above so it says select the text then choose the format text button so if I were to type something here name of work I would select the text and then click on the A and then it takes me to kind of the old-fashioned way of cha
nging fonts so I could change the font and I get a preview in the lower right I can change the style maybe to bold change the size and then I can even put in underline change the color and put in some special effects but I can click okay and there it is now in the center section it could be that I want to insert a page number or I might want to put in page of and then number of pages so I could do both and again if I'm a little curious or a little mystified by how this works to insert a page num
ber date time file path file name or tab name position the insertion point in the edit box then choose the appropriate button so if I want to put in page of number of pages and then I can even press enter and I could put in the date and I'll put in a space here and time then let me click on the right hand section and notice that it write aligns as well and for this then I can insert a file path and really it's the path and the file let me press enter as opposed to the file by itself I'll press t
he space bar here and then here is the worksheet tab name if we'd rather have that so we have all sorts of combinations of information that we can put into our header or footer they work the same way and then to the right of that you could insert a picture and if you insert the picture and select it then to the right of that you could also format the picture but we're going to click okay and then we have all of this crazy header that it's just going to be kind of a jumbled mess maybe I should ta
ke out some of that but you can see very easily how this works so let me see name of workbook let's take that out page of pages I'm also going to take out this path and click okay all right so now it just tells me that I have one of however many pages and the date and the time and then I could do the same with the custom footer now here's the interesting thing down at the bottom I have some other options so different odd and even pages so I could have say the path on odd pages and the number of
pages on the even Pages or something like that a different first page so again I could have nothing on the first page and a whole lot of other stuff on the rest of the pages I can scale with the document so that the header and the footer don't pop out they don't overwrite anything that is on the regular page and then I could also align with page margins which I don't know why I wouldn't want to do that but let's go ahead and say okay just to see how we're doing here and so now we have 182 Pages
yeah my header kind of was a little short there so you know all I need to do is grab those little boxes that we had from our as we come down to the bottom right the show margins and you can see how I can kind of play with that even though I've gone into that dialogue box and now it gives me some good information and I am really filling out these Pages probably a little better now I only have 200 pages so if I come down to page 100 or so here is the second page the second part of the different Re
c records and it could be looking at this I could say you know what I think I could widen out those margins on the left and the right and I still have 200 Pages let's see if we can maybe give a little more room here for our top margin maybe our bottom margin and I have 224 Pages now so I need to readjust a little bit but you can see how just by adjusting the margins how that can really make a difference in how this works now we did in page setup have something else that we needed to do let's als
o look at the sheet tab now the sheet tab lets you define the print area because of some of the choices that I've already made the print area here is grayed out however if I I hadn't defined this as uh having two pages wide for example I could go to the print area and say I only want to print these cells and no others remember that we've already set up the print titles as the first row but if we hadn't already done that we could also come down to reset the columns now here is where it gets inter
esting sometimes people don't put in borderlines and as a result the data tends to kind of all meld together especially with large spreadsheets and so people will say oh you know what I want the printer to print the grid lines because I've not added in any other kind of lines I can say I don't want to print in color I just want black and white or I want a draft quality now let's see what happens when we do this row and column headings I'm going to click on that then I'm going to click on okay so
first of all you can see that we now have those little dotted lines that are the grid lines that are now going to print but the column headings that we said we want to see are really the ab c d e and it gives you the row numbers now depending on what you need to to do sometimes people need that information for a variety of reasons but for the most part this isn't an option that a lot of people would print especially as a final report so let me go back to page setup and go to sheet our last tab
and I think I'm going to turn off the row and column headings and then I'll need to click okay for that to change in our preview one of the most important settings that people forget about when they have a spreadsheet a large spreadsheet is that the page order is the most important thing that you want to use with a large spreadsheet if you consider that we are going to perhaps print two pages across then we really wouldn't want to go down 100 pages and then try to find where the second piece of
information for a client or for a person is we would want to go first of all over and then down and for large spreadsheets this is one of the most important settings you want your records to go across and if it's across one page or 10 pages you want that consistency so that you can line up and keep track of that one person or that one item all the way across all of the pages and then go down to other pages and keep track of those rows so be sure to use page order if you don't use any other setti
ng here within page setup well let's click on okay Border Lines ready to print and we have the first set of cells going across filling that page very nicely and then in the second half they don't quite fill up the page but still it looks pretty nice doesn't it and so now we still have 200 Pages let's try Custom scaling let's fit all columns on one page and you can see how much smaller that print is and if you're not careful it could be that it just becomes unreadable but we have now 56 Pages if
we do that and depending on what's required of you that could be the choice that you want to make so that is printing using file print defining settings on the printer defining settings here as well and even then going to page s set up as another option when you do your printing of spreadsheets to learn even more be sure to go to mymcpl.org online learning for such great resources as universal class Learning Express Library linked in learning and udemy business we also premiered videos every Wed
nesday and Friday at 1:00 if you miss a video premiere you'll still be able to find it later when it's convenient for you you can find us on Facebook at mc360 and you can find us on YouTube by going to our mcmo Channel
Comments