Effortlessly Manage Your Bill Book in Excel for Legal Documents

Bill book in excel for Legal

welcome we're gonna take a look at Microsoft Excel and some of its basic features for lawyers and what it can do first thing to note about Excel is how it handles data entry a little bit differently than most programs as you can see when you open up a blank Excel spreadsheet it opens up a book number one generally you'll see down along the bottom that there are three sheets by default you can add more if you want to by just clicking this or pressing the shortcut key for Shift + f11 you can rename these by double clicking and making it your sheet or whatever you want it to be and these are important because Excel will use these names for other sheets if you want to look up something from this sheet called your sheet over here on sheet 3 you can relate back to that and get the information from your sheet over to sheet 3 so as you learn more about Excel you'll understand a little bit more how these features work together but this is kind of a default setup with respect to the data that Excel uses it organizes its data by rows and columns so if we click here you see that column a is highlighted and all the rows in column a are shown up so the rows are the vertical if you look here I've highlighted row 1 it goes across all of the column headings or if I tap here it will select column a all the way down through the end and so when you're putting your data together or putting your spreadsheet together know that text or images or the things that you are going to host numbers or whatever are housed inside of each of these columns if you see here you can tell that you are inside of a1 and there's a black box around there that indicates that if you started typing like the word hello for example it would show up in that column now you can see that there's a blinking cursor there that means I'm in edit mode on this particular cell if I want to get out of that I either press the return key or the tab key or one of the arrow keys and depending on which key I choose to press there will be different functions so I this time I press the Enter key and it brought me down to row number two in column a let's go back up I will use the arrow key to select a one if I press the tab key then we move over to b1 and you can see that Excel 2010 the version we're looking at here highlights the position of the input box so we're in column number I'm sorry column letter B row number one and then we have to put another word in there and press tab and that moves me over again if I was in that column and press Enter that would move me down Excel is pretty smart and it knows if you're organizing data in a particular way it will kind of guess where you want to go and that can be both helpful and confusing just so pay attention to how you're using Excel generally either use the tab key to get out of edit mode and move over to another cell or column and the same with the Enter key can move you up or down the arrow keys on your keyboard you may not have had much use for them and other programs but they're extremely helpful in Excel as you can move around the grid kind of reminds me of a battleship game board how excel is laid out you can move across things pretty quickly and if you hold the shift key down and move to the right you can select up or down or across very easily which is a nice feature as well using your mouse you can do the same thing you can click and hold and drag to select various ranges and do different things such as put in formatting on a particular selection of cells such as all borders which will in inside excuse me insert formatted lines has shown up here kind of as you may be familiar with in a Microsoft Word table we're gonna go ahead and remove that and take that out so that's kind of the basic data entry information it goes into cells in either rows or columns and depending on what type of information that you're going to put together in what type of report will depend on how you want to set that up whether you want to put a title at the top in those types of things keep in mind that Excel also can use headers and footers and so you can use these areas these worksheets here as your primary area and then put in title and that kind of information and a header or footer later on alright so let's get started with one of the main uses of Excel and that is putting in a list of information let's say that this time we wanted to another portion of our excuse me in another portion of our class we have discussed the use of Microsoft Word to organize medical information related to a injured party and so we've had columns like date and provider and description of treatment now a couple things I'd like to note here about how Excel handles text entry if you look here we have the word date and all that fits very well with an a.1 if you look under b1 provider appears to fit when we go over to see description of treatment we can see that it's kind of extended past the border or the edge of cell c1 and is now covering d1 and e1 we don't know if that's going to look good at the end of the document and when we're ready to print that and so we might want to go ahead and format it now or we can let Excel do that for us now incidentally if we had one more column here that would let's just put a mount service for example you'll see now that that is hidden behind the text here in d1 and if you would like to see everything that's under c1 put your cursor in between C and D till it becomes a little crosshair like that and double tap or double click and that will automatically expand that row to show you everything that's within that cell we don't know for sure that we'll need this that we'll leave that in for a moment and you can do the same thing over here and it will shrink now this is another feature of Excel that can make folks feel like they've lost data and so let me show you what happens when we put in date here I'm gonna put in a date 1 1 of 2015 and press the tab key now you can see that Excel 2010 is smart enough to go ahead and move that over for me to auto size 2 that some other versions of Excel may not be and so if that's size to accommodate the word date but not the data that's in a 2 you'll get these pound or hash marks to show you that the data that's in that cell cannot be displayed and so you can see it up here in the formula bar where you can come in and edit whatever is in a particular cell but when you click out of it and when you print that that won't show up and so what Excel kind of autoexpand that for us so let's put in doctor number 1 and we'll say x-ray here and we'll call that $10 what's kind of neat about Excel in the sense of what it can do is as long as you have the information and you don't really care how it looks you can just quickly fill up a lot of information and organize data in a very very quick way but if you want a little bit more professional look to your document as well it can really help you to use some of the built-in form formatting styles that are available in Excel so if we come over here and hit format as table then we'll give us a whole lot of different options that we can choose from and for this example we'll just choose a light table style and apply that and it's going to ask Excel will ask us if we want to format this area selected by what's called the dancing pants around our data set as a table this information is contained here you can see that cell references that are in here and you can choose to select whether or not the table has headers here we do these are the headers date provider description the amount of service and headers are very important for your data and I'll show you why in just a moment click OK and now we can see that Excel has taken that information and formatted it for us and it's brought us over to the design of the table so we can do different things within that we can change the style make the color change to red instead of gray you can have banded columns or just show the last column or you can add a total row at the bottom by clicking that button and that will total this column for us automatically we won't have to put anything in I'm going to show you a little later how to add that formula so I'm going to leave that out for now but that's very nice that that's in there and incidentally you can do different things with that totals column instead of just total you can average count count the numbers min Max and various different things that are in there which is a very handy feature as well why I like these lists is if you can we'll see here and I'm not sure if the video will show this but at the bottom right hand corner of our list there's a little indicator that shows us we're at a list so when I get to the end of this row and press the tab key excels going to add another level to our list and put everything in there formatted the same way and so if I say 1 - 2015 and we went to dr. o - and we had a follow-up visit and this time there was $25 now when I get to the end of the list instead of pressing enter press tab and it'll move me over to the final line and I'm looking through a bunch of documents now and I have one that shows up from December 31st of 2014 now I don't really want to go through and figure out where that is in the list manually I want to do that automatically but this was for dr. a one as well and this was a follow-up is it notice when I start typing follow-up visit it found that I had already typed that before and suggests that I use it again which is very handy can speed up data entry so I've typed just a few keys fol and it found that I've used that before and it says hey do you want to use it again I'll hit the tab key to select that and this one was $90 all right I pressed enter to get me out of the list now it's because I want to show you a couple of things that are really nice with how Excel works so if we click on the date column header and allow Excel to find that information for us you can see that there's lots of ways to filter this data you can quickly sort it from oldest to nude newest which puts December 31st 2014 at the top of our list very quickly you can also then sort it by multiple levels so if you want to sort it by provider and see all of the treatment that was by doctor one versus doctor two or if you wanted to just look at it the x-rays you could select just the x-rays and have that show up yes you can see the more information that you have in and if you wanted to be able to filter your information with that amount of specificity it's very powerful for doing that I mentioned before that there is a way to do the automatic total on this so again if you click in here and go back over to the design and hit the total rule we can see that Excel is already totaled the amount of service guessing that we wanted this sum here in the bottom and if we wanted to make this look like a number click on the Home tab come over here to the number area right now it's got a general formatting and we could have it do all kinds of things to do an accounting or currency I generally use accounting could do that up here as well change all of these to accounting just to make it look a little bit nicer as well you can change the font put in bold italics or underline and do different things with that as well and you don't need to use lists you can just type in the information in any of the columns and rows and that will show down but list kind of help organize that information and make it easier to maneuver by putting these Auto filters in as you go now let's say you wanted to figure out whether you had any duplicates in a particular area or you wanted to count the number of follow-up as it's show that by color it'd be very it's very easy to use what's called conditional formatting there's different types of value ways to highlight different cells there's top and bottom there are different types of things that you can do with these different areas of Excel including you and putting in different icons and those types of things so if you're looking for duplicate values you can just hit highlight cells tap on duplicate values and you can choose how you want it let's say we want to show up with green fill and hit OK what will happen what will happen once we select the areas that we want Excel to look for duplicate values in is it will highlight those for us in that color and so we can quickly see hey this was a follow-up visit or this was an x-ray this can also be helpful if you've gotten multiple entries for the same area and you're double counting numbers for example because you may be seeing some sort of repeating data set in your information that can be a handy way to format your data and help do some analysis by color as well you can also do that for just whatever sex the document you want to look at or if you want to look at all of it just select it all and they will do some of that formatting for you all right let's take a look then moving this total row that Excel did for us and what we want to do then is also let's take this out of a table so this is just regular text for us and take a look at that now that's just text that's formatted and what we'll do is come in here and unform at some of that okay now we're back to just standard data in a list and so excuse me I want to put in the word total and show you how we can add up all of these there's a couple different ways but using the sum formula is one way to do that to tell Excel we want to use a calculated field or cell we tap the equal key and that shows us that we are in a wanting to put a formula into the cell as you can see up here Excel is already guessed that we probably want a sum because we've got a range of numbers here and so we can start typing the word sum and Excel will give us all the sum formulas that are available if you press the tab key it will put the open perón and and now it's telling you number one number two number three that just means which number do you want to select first and add into your sum range and so now we put those three in because we have those three numbers and we can close that with a closed pairen to close off the formula press the Enter key and we can see that Excel has summed d2 through d4 and taking these amounts incidentally once you've had these select if you look down here to the bottom of your workbook Excel shows you that you're summing three rows or three cells shows you the total and shows you the average kind of nice if you need to just look at a quick subset of data in a larger data set but here we know that we've got our total of 125 dollars and if we wanted to just manually format this range of information we could select just the smaller piece and maybe put this as all borders and then maybe even wanted this one just to be one big border outside box to show that that was the total or when one of this to be a little bit different color and so we go up here and fill it with a lighter gray so that stands out just a little bit different but as you can see that took me several steps whereas before just using the format as table put all of that information together very quickly there is a number of easy functions to use if you wanted to know the average you can start talking or typing the word average excelled guesses that's what you want to do so you select that and then you press the tab key again it opens that parenthetical to say you're starting it select the three numbers that are in our data set up here type in the close parent and press Enter now we know the average cost of service was forty-one dollars and 67 cents type the word average in here and say we wanted this same look to appear on this row we could collect all the information from these cells and click on the little paintbrush tap it down here and that quickly formatted those two exactly the same including the number formatting that shows up over here to make this look like it's in accounting format so those are just some real basic you can take cells and just make them relative to themselves so we want to add D to you know we can add a little plus sign d3 add the little plus sign and then click on d4 and press Enter and get the same result a little bit more cumbersome as you're trying to click on each particular thing but if you need to reference something over here in column F plus G and then back over D that's one way to do that also Excel can multiply divide subtract and do almost any other calculation that you may need Excel is also great at doing charts and putting in information in to just a visual form so let's see what we can figure out for this particular chart very simply let's add a row if I right click here I can insert and I will move all of these cells down and if I wanted to just have a chart of this information I could click on insert and choose the different types of charts that I wanted to do this isn't a great data set for charts but let's choose a 2d bar chart just to see how quickly Excel can put that information together and so what you can see is Excel will grab the information and throw it into a chart that you can use and move around in different ways there's a bunch of different chart tools up here where you can design it using different colors select the data that you wanted to analyze you can move things around by saying switch columns row data where we see all of the different information that is appearing in our chart is the date the provider the description and the amount of the service and we can see that doctor visit one the amount of the service is greater than doctor visit number three where the numbers are different for the different amounts you can also change the layout which is fairly substantial putting in different titles titles of the axis you can put the legends within which are basically the information about your chart all of the different things that you would like to do and you can format it in various ways as well you can get pretty creative here which is fairly time-consuming but can be fun to pull that data out and really make the point that you're trying to make all right as I mentioned Excel is great for not only numbers but also for lists of text so for example if you wanted to say client first name client last name client address client phone client email you could do all of this information and build a pretty robust list and pieces of information now incidentally with Excel it's always the best to have as discreet a piece of information as you can so rather than just saying client name for example if we use first name John Doe that allows us to then filter by last name first name and put those into Microsoft Word later in a merge field and we can even add in a salutation so let's insert in shift the cells to the right and we'll move this over I could have done this a lot more efficiently by inserting it up here and moving all that but I wanted to show you what Excel does but if we say clients how you tation you can type the word mr. now we can go in there again if you have the address and you can break it out by city state and zip those types of things will help you later if you want to filter by zip code for example or particular cities but just for this example we'll put in 1 2 3 4 Main Street and again let me show you the difference here if I select this and hit right click hit insert that can do client city will do it again and one more time client state client zip so we'll say Indy Indiana 4 4 4 4 and 3 1 7 1 2 3 3 3 4 4 and John at dough.com Excel knows that we have an email in here and hyperlinks that for us as well and you can put this information in we'll just duplicate alright so we've selected this let's move that information down the wrong key there ctrl D will will put that information down just so we can have a little bit to play with now that that's in here if we wanted to make this a list so we could filter it again just format that as a table do the light one click OK now we can filter that information fairly quickly if we wanted to change this to Daisy and Kentucky Michigan Ohio and Maine Wisconsin and we'll put that in so now we have kind of a various range of data well we can now select all of that or just like those that are in an Arizona quickly filter that list again all the names you're gonna be the same but when you have something filtered you will you'll know because this row area over here changes the color to blue so you can tell if you came in if you're not looking at a full data set or wondering where your data is somebody has come in maybe didn't filter that down saved it and forgot to open it up so you find that and then go across your headers until you find or the little funnel icon appears click that hit select all and you should see everything that you have again again as you can see this can be very powerful as far as organizing information and then you can sort that by different ways so if we change that to Zoe and noe and LOI and Joey and co putting a couple different in we can quickly sort that a tizzy and have all that information in that entire data set all the way across filtered appropriately which is very handy so those are some of the basic uses for Excel there's a lot more that this program can do and as you play with it you kind of think would really be nice to do this or what if I could get this information from this spreadsheet to the next one those types of things are where Google YouTube lynda.com can really help you take the information that you're looking for and figure out a better way to do that so I hope that's helpful for you hope that gives you a quick overview of how to get into and maneuver around Excel and to start using that