Excel: Tips/Tricks For Large Amounts of Data

Uploaded by ltsonlinehelp on 02.02.2012

Hello and welcome to the last of the Learning Technology Services tutorials on Excel Tips
and Tricks. The items in this tutorial are those that I thought were interesting when
managing large quantities of data. The first thing that Iíd like to show you
is how to be able to send this file through email without actually opening Outlook. Under
ëFileí you have ëSave and Sendí. Now, under ësave and sendí you can save as a
PDF, or do lots of other different things in this area. But Iím looking at the top
option, ësend using emailí, and then ësend as attachment.í Now, on this computer I have
Outlook. When I click that selection an email message opened up, and now I could send it
to any person that I wanted to. This avoids the problem of locating your Excel. Oftentimes
when we have an Outlook message open we have to then go ëAttach fileí and find the file.
My next option is formulas. Now, there are lots of formulas, including many that you
wonít have to know. But in column ëIí of this particular worksheet we have everything
in capital letters. Iím going to double click on the line between ëIí and ëJí so that
you can see it more clearly. Letís say you want this to read in proper text, where the
ëRí is capitalized but not the ë-eadí and so forth. First Iím going to make a new
column. I will click on column J, right click on my selected area, and ëinsertí. In this
column Iím going to write the formula. The formulas is = (thatís how you always start
formulas), the function is PROPER. What do I want to make proper? I want to make the
information in this cell (I2) proper. Press enter, and this now has the beginning letter
capitalized and the rest un-capitalized. Going to the handle in the bottom right corner of
that cell I can double click and it fills that formula all the way down.
Another formula that I use quite frequently is ìconcatenate.î Say I want to have a column
that reads ìEnglish.100.001.î That can be accomplished by concatenating those three
cells. Iím going to click in H and right click to insert a new column. In this cell
Iím going to write the formula. However, this time Iím going to go up to formulas
and look for concatenate under the ìTextî area, because then I get this nice little
window that will walk me through making this formula. Whatís the first section of text
that I want to concatenate? Well, itís ìEnglish,î so Iím going to click in there. Notice as
I click in D2 itís beginning to build in this area. Well, I want it to be separated
by a dot, and then I need the course number. The dot goes in text box four. I want it to
read English.100.001. Click OK, and it looks good. Iím going to widen this by double clicking
on that line. Then I can double click on the bottom right corner handle and it will fill
down for the entire column. Looking at this particular cell, you can see it is a formula.
I donít want it to be a formula; I want it to be values. That way I can take this information
and move it anywhere in the spreadsheet that I want. If I click on H, right click, and
copy I can paste right over this, but this time I want to paste as values. Under the
home tab you have paste and then you have the more arrow right below it. I going to
click on it and paste just the values. To get rid of the running ants I can press escape
on my keyboard. ëEnglishí is no longer concatenate; it is the value.
Let me just clue you into one more thing. Iím going to open up column G a little bit.
This is a rather large, complicated formula. I needed to turn what was found in F to have
zeroes in front of it. I needed it to be three digits. Normally in this case (Iím going
to escape out of here so Iím no longer in that cell) you could go under F, right click,
format cells, and I could customize this so that it would be 3 digits This is what I want it to look like, but Iíve
only formatted these cells. When I try to concatenate column F it went back to just
one. How did I find out how to do this? I got some keywords from a coworker, and I went
out to Google and I typed this (padding cells, leading zeros) so that I found this particular
formula, and then I filled in the columns and the cells that fit. I donít expect you
to remember this or even understand it, but just know that if you have to accomplish a
certain thing that others have run into the same problems, and we can always Google and
try to find out the proper formula. Now there are lots of ways of having math
in Excel without using formulas. Iím going to scroll over to column P which is the enrollment.
If I click on P and come down to the bottom portion of my screen I have some calculations
already given. The average enrollment of all these classes is 10.6. I have 262 classes
written. It says 263, but its counting the heading. Here are my minimum and maximum (so
the largest c lass has 39), and the total number of students enrolled is 2,798. Now,
as soon as I click away from that range it goes away. But if I again click on the whole
column Iíll have my information back again. Itís a nice way to work with math without
formulas. The next tip on the list is the quick access
toolbar. I might have icons that you donít see in yours. I like this one. Itís making
a new spreadsheet. If I click on it, it makes a new spreadsheet. If I click on this icon
itís a shortcut to ëopen,í so I can go out to my folders and open a new one. I added
these icons by clicking on the customized quick access toolbar icon and going down to
ëmore commands.í I have popular commands. I can also get all the commands, but there
are a lot of them. If you go to the ìall commandsî go to where I find ëclose.í You
can ëclose allí or ëcloseí one. If I click on the icon and add it, it will be added to
my areas over here. I can move it up or down in my list. Some of the very common things
that you do can be found in the commands. There are a lot of things that you never have
thought to do. You can add them to your quick access toolbar so they are more readily accessible
to you. In Excel 2007 and 2010 I have often seen this
problem: you have your Excel but you wonder where your tabs are. Itís because this particular
workbook is not maximized. I have a minimize, maximize, and close in this area, and then
I also have those icons repeated up at the top. The top is for the application of Excel.
These are for this particular workbook. If I maximize this then my entire screen is filled
with this workbook, and I can also see the tabs at the bottom.
We covered freezing the top row. Once again, to do that, I can click in any cell and go
under ìViewî, ìFreeze Panes,î use the more arrow, and I can freeze the top row.
But letís say that you want to freeze not only the top row, but add a certain column.
Now, Iíd say that column I is pretty importantóyou want to be able to see the class, the section,
and so forth. Iím going to click in cell I2, which means that I want to freeze everything
above it and everything to the left of it. Coming over to Freeze Panes, I select the
top choice (Freeze Panes). I have a line, and what is above that is going to stay as
I scroll down. If I go to the right the columns E-H will remain English 110, even if Iím
over here in the meeting column. Iím going to unfreeze my panes right now
because my next topic is merging cells. Often times it is nice to be able to see one cell
that highlights information in both columns. I can merge this information by clicking on
both cells, going to the home tab, or doing a right click. I can merge and center. Then
my information is over both cells. Even though merging cells looks nice, it causes problems
when you are mail merging, doing formulas, or selecting columns and so forth. So, I would
suggest that you not get in the habit of merging cells. Iím going to unmerge this (once again
by going up here and unmerging). I would rather you use the left and right alignment in your
choices. Letís say in this particular cell you want
to have course on one line and title on the line right below it, but you still want to
stay within the same cell. I have my cursor right after the ëeí of course. Iím going
to press the alt key down on my keyboard and press enter one time. Iím forcing a line
break. If I do that, my cell height will automatically increase. I can always come back and move
it by putting my cursor between the 1 and 2, left-click, hold, and I can move it to
become the height that I desire. Printing an Excel sheet causes many issues.
First of all, my suggestion here is to delete any columns that you donít need to print.
In this case Iím just going to delete A-G. I selected the columns and Iím going to delete
them. Iím getting down to the information that I really need. When you are ready to
print, go under View, Normal, Page Layout. That shows you where the breaks have been.
Most importantly, you have Page Break Preview. I can change my margins by going under Page
Layout and Margins. These are pretty large margins; I only need .25 so I can increase
the space for the area I want to print. I also can make it landscape rather than portrait.
Iím under Page Layout, Orientation, Landscape. Iím in Page Layout and I still have 100%.
But I want this all to appear on one page. Now I go under the View tab to Page Break
Preview. I can move any dotted line (left click, hold, drag to the right) and now I
have forced this to print on one page. But Iíve also reduced the view. Letís go back
to Page Layout. Now Iím 69%. Thatís still very readable. It would be nice if we could
get rid of some of these columns or squeeze some others up. Itís best to get rid of the
columns you donít need. I donít need P so I can delete it or hide it. This will help
with your views. Notice that it hasnít changed anything. I can up the view and go as far
as I can before this becomes a second page. If you only want to print one page I can select
the range that I want to print, go under Print Area, and set that print area. Letís say
that I definitely want my header row to appear on every page that I print. Go to Print Title,
on the sheet tab, and you have ërows to repeat at the topí. Iím going to click on row 1,
and notice now my row 1 will appear at the top. This is also the area to go to if you
want gridlines to appear when you print, if you want your row columns and headings (the
1,2,3, A, B, C) you can click here. And, I have a print preview which allows me to see
my row at the top. As I scroll down to the next page, itís there.
Thank you so much for watching these tutorials. We can readily make more if you have information
that you would like to see in this format. Please contact training@uwec.edu and please
look at the rest of the tutorial that we have online. Thanks so much for watching; hope
to see you soon.