sometimes when you work with Excel if you just knew the right function you might be able to save yourself some time but you don't and so you wind up spending hours if not days doing something that if you just knew how to do it would save you so much time and energy well we're going to look at a couple of those things today one is text to columns although it doesn't necessarily have to be text but certainly taking information that has all been jammed into one cell and spreading it out over severa
l we're also going to look at what you can do to find duplicates in a big list of say addresses or other kinds of information and then we're also going to look at consolidating because you know you don't necessarily have to write a formula to come up with a summary of all of the sheets that are in a workbook it's just amazing what Excel can do and how smart it is hi I'm Terry one of the consumer technology Specialists for Mid-Continent Public Library and we're going to look at some things today
that are going to help you get back your time for our example today we have a short name and address list and in column A we have both the first and last names in column B we have the street address and then column C we have city state and zip and we would like to separate out those three items and place city and column C state in column D and zip in column e now we have some irregularities here it's always best if your data is as clean as possible so even in this short example I can see that in
this name and address there's a comma after the state but as I come down here to Row 5 there is no comma here after this state and it's something that we would either fix ahead of time or we could look at it and say well that's no big deal I can fix it on the back end and we'll just look at some ways that we can work with our data to make this splitting out text to columns work well first of all I'm going to select column C here and then what we want to do is we want to go to data which is here
in the tabs above the ribbon and then when we click on data what we can do is come over to the right to the group that is named data tools and with data tools there are a lot of different kinds of buttons here but we are going to look at the very first one text to columns and when we decide to work with text to columns we're going to go through a wizard that will help us step through that particular process so to get started we'll select column C because that's the one that we want to split the
text out from to other columns and then we're going to go to text to columns and it tells us at the top that is a three-step process and the first one is we need to decide if it's delimited or fixed with and almost all data anymore is delimited meaning that it's separated by a tab or a comma or a space or some other piece of punctuation generally that separates the different pieces that are within the cell well we know that we have commas and we'll click next and then we'll come here and almost
always tab is set as the default delimiter but in this case we know that we have commas and as I look down at this data preview I can see that I also need to account for spaces where I don't have a comma so I'm going to also add space now that doesn't quite look right either but we're going to go ahead and go with those settings for this time so then we'll click on next at the bottom right and as we look at the example it even says that here in the upper left we can decide the format of each co
lumn now as I go to the right it says well General is going to convert numeric values to numbers it's going to convert date values to dates and all of the remaining values are text or I can actually say oh no this is specifically text this is specifically date and it's up to me how I want to Define that data but General is good enough for what we need to do and then at the bottom right we'll click on finish and it has moved our data to different columns now the only thing is when we did this cit
y is absolutely in the correct column now column C and for the most part state is correct and zip is correct however because we had St Louis that is two words so there's a space between those two words and let's see Kansas City which also has two words with a space between the two words it moved everything over a little bit too far so I think we were in the right ballpark but we still need to figure out what to do I've reset our example to look like it did in the beginning and we know that we ne
ed to fix St Louis and Kansas City so that in some way Excel knows that those should be treated as just one entity one idea and to ignore the space in between those two words so I'm just going to use Ctrl F to find and I want to find first of all oh Saint Louis and I want to replace it with quote saint I see I spelled it wrong quote saint Louis quote and let me fix St Louis here so now I'm going to find Saint Louis the way it is currently and replace it with St Louis with quotes around it and I'
m just going to replace all and it said they made two Replacements well while we're here let's find Kansas City and replace it with quote Kansas City quote and replace all I'm going to close out a find and replace now I'm going to select column C I'm going to go back to data go to the right where we have our data tools go text to columns it is still limited we'll click on next we're still going to keep our comma and space delimiters but on the right here it says text qualifier and the default is
quotes and click the drop down here and it could be that we would want a single quote instead or certainly not none but notice that already in our data preview that because we used a text qualifier we know that this is going to work we can click on next if we want to we could yet again Define those columns that are coming in as special kinds of data either text or date and then on the right we'll click finish and now we have a working list where we have names in one column Street in another cit
y in another state in another and zip in another isn't that just remarkable now let's shift gears a little bit and we still have that same address list with the addition of Social Security in the second column and we might decide to be sure to check for duplicates now when you check for duplicates this can become quite a challenge you want to look for duplicates with enough information to make sure that you don't delete something that is in fact not a duplicate even though initially and with jus
t a few cells it looks like it could be so let's look at that a little bit first of all one of the ways that we can look for duplicates and I'm just going to select all of these cells is that we can go to the Home tab and we can go to conditional formatting and when we go to conditional formatting if we go to the Highlight sales rules portion of the conditional formatting down at the very bottom we can in some way add conditional formatting to duplicate values so let's click on duplicate values
and as I look at this before we go any further I can see that there's no value to have selected the city-state and zip because of course there's going to be that more than one person who lives in a particular City in state so I'm going to cancel out and I'm going to go back and select just a b and c columns because I think that's where the value is now I'm going to go to my conditional formatting highlight cells rules duplicate values and I really don't like uh for some reason that light red so
I'm going to go with green fill and click ok now we're going to look at this in some detail because we do have the same name now with really large address lists for example it is absolutely possible that you can have the same name but it is two different people so then you would want to find something like the Social Security which is very unique to just one person and in this case oh the Social Security is the same so while it's a duplicated record to some extent because now we verified that th
is is Kimberly Statham and it's the same thing twice now when we look at the street what does that tell us she's moved and so we don't know which is the current address but we do know that we don't want to see that this is an absolutely duplicated record it's just the same person with two different addresses and so this could really save us we don't want to delete either one of these for fear we delete the wrong one as a first step using conditional formatting and looking for duplicates is kind
of a nice way to figure out if you have say duplicate names and then you can go to another place in Excel to even Explore a little bit more so we're going to go back to data above the ribbon and this time we are looking for duplicates now it's a little hard to see here but it's to the right of text to columns it has a blue row a white row a blue row and that little icon and more importantly it has the X to delete so we'll click remove duplicates and it says to delete duplicate values we want to
select one or more columns that contain duplicates now remember city state and zip you're going to have more than one person with the same city state and zip especially in a large list so we're going to take that off again we're going to look for a duplicate name and in this case we know it's going to be Kimberly we're going to look for a duplicate Social Security number which again is Kimberly so so far same person but then to make sure that we're not deleting something that needs to stay and t
hat we might need to do some more work on we're also going to keep Street selected here now in the right of this little dialog box it says my data has headers so we also want to keep that in mind well we'll click ok and it says nope no duplicate values are found now let's go ahead and select this table again but this time when we go to remove duplicates let's take off Street and city state and zip now remember Kimberly has the same name and social security number twice it's the address that make
s the records unique so now we'll click on OK and one duplicate value has been found and removed and sure enough you can look at that table and the second instance of her name has been removed so when you use this while it is very helpful be sure that you select enough information that you can find the unique values Beyond just kind of the obvious but wow what a Time Saver if you had 10 000 names and addresses and you had to go through manually oh my goodness what a mess but this is so helpful o
ur next set of examples have to do with consolidate and this is a feature that while you need to be a little careful and really know your data well can just be a miraculous fix for you where you don't have to write formulas to say create a summary sheet now there are two different ways that you can consolidate with Excel the first way is by position so basically that means that the worksheets you draw the data from must be set up exactly the same way the categories of information must be in exac
tly the same order so basically identical worksheets just different data within them whereas you could also summarize by category meaning that the categories can be in different order the worksheets don't necessarily have to be identical but it picks up on the categories and then Excel says well for this category we will do this kind of math and come up with a summary so such a nice tool let's look at how we can do this for our first example we're going to look at our consolidate by position and
let's look at January first and in January we have the expenses at least the beginning of the expenses for the month and they are listed in column B column A has the date that they were paid that's in the details here in this worksheet so I don't think I need to relay for a summary also display that information what I want to do is know how much I have spent so far this year so definitely I want to look at the category but then I also want to look at the amount that I have spent this month and
then I want to come to February and look at those expenses as well now the dates have changed but notice that the items are in exactly the same order as January and then I have amounts for those as well and generally a lot of these amounts are going to be the same but I want to add to see what I have spent January and February of this new year so to do that I'm going to go to the summary sheet and then I need to click where I want that summary to begin and I'm going to go to the data tab and ove
r to the right data tools and then in the upper right of that group we have consolidate and I'm going to click on consolidate now the first thing that I need to decide is what is the function that I'm going to use and it defaults to sum and in fact that's what we will do because we want a total but if I click the drop down I could get a count of how many numbers are being summarized within the sheet that I'm creating I could come up with an average so over six months I've spent an average of tha
t kind of thing I might decide to do that Max would look at all of the numbers that have a particular category and it would say here's the largest number of this group of numbers whereas Min is the smallest number within that group of numbers product is you would take all of the numbers and multiply them together count numbers simply counts numbers as opposed to say text standard deviation and variation is more statistical we're going to keep this very simple and we simply want to come up with a
sum for January and February now below that I have a reference box when I click in that box I need to come over to the right to the arrow now there's also a browse button here but that actually kind of takes me out to file explorer and I could find another workbook to find this information but we're just going to stay within the same workbook so I'm going to click the up Arrow so now where am I going to get this consolidate information well I'm going to go to January as soon as I click on Janua
ry in that box where I've clicked it says January but it also has the exclamation point which means this is the name of a worksheet and then what is the information that I want to summarize well I definitely need to include the item and I definitely need to include the amount and so I now have defined that range so I'll go to the far right and click on that down arrow that expands this dialog box out now this is where you can get caught a little bit because it looks like all right I've said I wa
nt January and the sales in January that are going to be added for my summary but in fact what I need to do is come down a little bit farther and on the right click add you know when I click add now it's added it to this box it says all references and whatever is in that oh references box that is what is going to be included in the summary so now let's go back because it's not just January and we're going to go to the up arrow and instead of January we're going to come down to the February works
heet tab click on it and it even removed that January reference and now I have February and again I'm going to click and drag over those items keeping in mind that all of these are in the same order go to the right of that dialog box to expand it out and again don't forget to go to the right and click add so now I have January and February references I definitely have item and amount top row I think I'd like to keep that left column is what is going to be the item so we'll say yes we want to use
the left column we can create links to the data source as well so we'll click that and now in the right we'll click ok look at that let me widen this out a little bit so starting here in A6 I have the rent or mortgage and the total amount this is where it gets even more interesting because I go out to the left of rent mortgage I have a plus sign and in fact Excel has created an outline so when I click the plus sign oh my goodness now it's showing me and it would be kind of hard to tell which is
which but I have the first example from January and the other one from February they were both 800 so I can see this total of 1600 but I also see how I got that total just by simply expanding by clicking on the plus sign now I can come back to the minus sign and all I see is just the total but wasn't that easy no real math involved I just had to say this is what I want to summarize and ta-da here I have my summary by position now let's look at our second example of consolidation which is catego
ry and to understand how that works we need to go to our spreadsheets so let's go to January 2 and when we look at it we can see that we have car insurance through water and oddly enough we have phone in there twice which means well we've made two payments maybe we had to pay for repairs maybe we paid something late and didn't realize it so now we're catching up on our payments but for whatever reason the phone payments for this month actually become 205 dollars also notice that when you look ca
refully at these items they have been sorted A to Z except for this additional phone payment at the end coming over to February we have the same exact names for things in fact we have ISP twice here but we have sorted this list Z to A so let's look at how this works when the items are not in the same position so we'll go to category here's summary sheet category and make sure that we're in the data Tab and in the data tools group we'll go to consolidate and we're going to just get a sum a total
of what is in the two sheets so far so for our first reference we'll go to that arrow that points up and we're going to go to January 2. and then we're going to select B3 which is item C3 amount and then come down and select all of those payments for that month and when they show up in the text box we'll go to the right and click on the down arrow which expands our dialog box and then we always have to remember that we're not finished just by putting in the reference we need to come over to the
right and click add now we also need to work on February so we can't even keep this reference here and simply click on February and let's see what it does no it says that it's B3 yes through C 11 for some reason we have an extra payment in here that we didn't have with the first month and everything is upside down and backwards as well but we need to change that 11 to a 12. so for February B3 through C12 and then just as we did before we'll click on ADD so we have our two sets of references for
our two spreadsheets but we also need to remember to use labels in and if we don't check that left column we aren't going to know if it's water or gas or phone so we definitely want the left column we're not going to create links to the source data this time so we're not going to be able to click and expand to see the entries for all of the months we'll leave that blank and then we'll click ok and it says just that fast it says what the totals are for January through February noting that phone i
s 335 which is when I look at it here's a phone a total of 205 here and as we look at phone 130 which would be 335 by the same token the ISP is a hundred here let me even highlight that a little bit make it bold so the ISP is a 150 plus what was in January a hundred so that should be 250 when we come to our category there it is 250. so it's picked up double entries as long as they were spelled correctly even though they were certainly out of order from the other list so here by category worked a
nd we have a summary for the first two months now this is something that you may really want to explore to learn even more be sure to go to mymcpl.org forward slash online learning for such great resources as universal class Learning Express Library LinkedIn learning and udemy business we also premiered videos every Wednesday and Friday at one o'clock 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 mcpl 360 and you ca
n find us on YouTube by going to our mcplmo Channel
Comments