tag:blogger.com,1999:blog-41655877772141158212024-02-20T08:05:20.458-05:00Your Excel ExpertTricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.comBlogger31125tag:blogger.com,1999:blog-4165587777214115821.post-8853406597937470352011-09-21T15:59:00.000-04:002011-09-21T15:59:22.381-04:00Excel 2010<span class="fullpost">I have recently made the move to Excel 2010. Do I like it? I don't know yet. I'm getting older and change bugs me. I'm still getting used to it, and I'm not seeing a bunch of advantages yet. As soon as I learn a new 2010 trick, I will share! </span>Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com1tag:blogger.com,1999:blog-4165587777214115821.post-39275986019069534652011-03-05T08:42:00.006-05:002011-03-05T09:25:40.280-05:00ChallengeSomeone recently asked me to help with an Excel problem he was trying to solve. I don't think I solved his problem, but I added a neat new trick to my bag.<br /><br />The question (not necessarily the question asked) I found a solution to was this: is it possible to extract one precise word from a string no matter where it is in the string? We've already seen that it's possible to extract words or numbers based on their position in a string using mid, left and right.<br /><br />The answer: the SEARCH Function!<br /><a href="http://www.flickr.com/photos/94318674@N00/5499591302/" title="search by triciar98, on Flickr"><img src="http://farm6.static.flickr.com/5132/5499591302_ae936ff026.jpg" width="500" height="254" alt="search" /></a><br /><br />You can read above to see what it does.<br /><br />To get it to actually return the word, you have to nest this with an ISERROR<br /><br /><a href="http://www.flickr.com/photos/94318674@N00/5499605156/" title="iserro by triciar98, on Flickr"><img src="http://farm6.static.flickr.com/5299/5499605156_545bebd70e.jpg" width="500" height="260" alt="iserro" /></a><br /><br />The formula itself is:<br />=IF(ISERROR(SEARCH("credit",A2,1)),A2,"credit")<br /><br />In english, if the SEARCH function does not find the word "credit", return the value in A2, otherwise, return the word "Credit"<br /><br />Here it is in action:<br /><a href="http://www.flickr.com/photos/94318674@N00/5499016593/" title="result by triciar98, on Flickr"><img src="http://farm6.static.flickr.com/5256/5499016593_61c1ac9f56.jpg" width="443" height="311" alt="result" /></a><br /><br /><span class="fullpost"> </span>Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com0tag:blogger.com,1999:blog-4165587777214115821.post-65041575036992991872008-11-05T08:53:00.003-05:002008-11-05T09:01:03.129-05:00MidMID is very similar to LEFT and RIGHT in that it strips specified characters out of a string of text or numbers.<br /><br />So, let's use the example we used yesterday:<br />110308/20/7R13504<br /><br />Here, we know that the 20 in the middle represents a useful piece of information, like warehouse or factory or the number of pieces you need to produce. But, we also know that it's not useful stuck in that long string.<br /><br />It is pretty simple to extract:<br />=MID(A2,8,2)<br /><br />The formula says to go over 8 characters (including spaces) and return two characters starting with the 8th character.<br /><br />The result of this formula is:<br />20<br /><br />This is pretty easy stuff once you know about it. Definitely another good tool to have in your guru belt.<br /><span class="fullpost"> </span>Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com6tag:blogger.com,1999:blog-4165587777214115821.post-26203812796843083172008-11-04T07:41:00.006-05:002008-11-05T09:01:24.391-05:00LEFT, RIGHT...Just Vote!Let's say you've run a report and downloaded it to Excel. Let's also say that this report doesn't have a need by date field, but the need date is part of a reference code that looks like this:<br /><br />110308/20/7R13504<br /><br />Let's also say that you want to do a pivot table where all your records are summarized by need by date and you are OK with the format mmddyy (for now).<br /><br />Here's what you do:<br /><br />=left(a2,6)<br /><br />This formula will return this result:<br />110308<br /><br />The RIGHT function works the same way:<br />=right(a2,7)<br /><br />Will return this result:<br />7R13504<br /><br />These are definitely nice functions to have in your toolbox. And, there is more. I'll look at MID tomorrow.<br /><br />Oh, and if you're in the US and you're on the left or on the right, I don't care. VOTE TODAY! <br /><span class="fullpost"></span>Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com0tag:blogger.com,1999:blog-4165587777214115821.post-50265962139083933402008-11-03T12:42:00.006-05:002008-11-05T09:02:00.471-05:00ORI am going to try and keep this short and simple and to the point. If you knew me at all, you'd know that's a challenge, but I'm really going to try!<br /><br />OR is a function like AND and ISERROR in that it returns a TRUE or FALSE result. So, like AND and ISERROR, OR is pretty worthless unless you couple it with an IF. It's like a Peanut Butter and Jelly sandwich without the bread.<br /><br />If you create a formula like this:<br />=OR(B2="A",C2<=D2)<br />You will either get a TRUE or a FALSE result.<br /><br />If that result is TRUE, you can use an IF statement to have it return a specific value, a result of a formula, or the value in a nearby cell. Or, 60 other things you might come up with. Here's an example where if at least one argument in my OR statement is TRUE, the formula returns the value found in H2. Otherwise, it returns a value of blank.<br /><br />IF(OR(B2="A",C2<=D2),H2,"")<br /><br />Regarding syntax, please note that your arguments in the OR statement are simply separated by commas. I often try to sneak one of these () in there, but that's just silly. Commas only. (The same is true for AND).<br /><br />Finally, your OR statement is limited to only 30 arguments. Just 30. 31 is 1 too many, OK? (The same is true for AND).<br /><br />Just in case I didn't make it clear enough, and because I think I've made my posts on AND and ISERROR too long and complex: <span style="font-weight:bold;">OR, AND, ISERROR are all similar in that they all return TRUE or FALSE results and don't make much sense if you don't use them in conjunction with IF.</span><br /><span class="fullpost"></span>Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com1tag:blogger.com,1999:blog-4165587777214115821.post-55095427120850657732008-10-30T09:20:00.008-04:002008-11-05T09:02:16.534-05:00Page Setup Interface and printing the prettiest report in the officeThese aren't all really tricks, but I used Excel for a year or two before I figured them out or learned them through "tribal knowledge."*<br /><br />When you click File-->Page Setup, you will get this interface.<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Page_setup_interface.jpg&s=1&r=&w=640&h=480"><br /><br />The thing I like to change here, and I've mentioned it <a href="http://excel-expert.blogspot.com/2007/05/printing-tip-1.html">before,</a> is to change Scaling to Fit to and then choose 1 pages wide by BLANK tall. Do this and Excel will scale it so that it's not too wide for one page and will use as many pages as it needs. <br /><br />I rarely do anything with Margins, so I won't cover it here. Basically, if you need to mess with Margins, do it here.<br /><br />Header/Footer is very nice:<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\header_footer.jpg&s=1&r=&w=640&h=480"><br />You can add a header and a footer to print on every page of your report here. Click the pull downs and see some existing options or choose the custom header or footer buttons to get this interface<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\custom_header_footer.jpg&s=1&r=&w=640&h=480"><br /><br />Here's a tour of the buttons;<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\custom_header_footer_buttons.jpg&s=1&r=&w=640&h=480"><br />The first button allows you to change the font in your header or footer.<br /><br />The second button shows the current page number and the third button shows the total number of pages in the report. I like to hit the button for page number type in the word "of" and then hit the button for total number of pages (Page 1 of 20)<br /><br />The fourth button shows the date and the fifth button shows the time. Here, I like to show the date, hit enter and show the time below the date. Not sure why. I just do.<br /><br />The sixth button shows the Path and File. This is really nice if you've buried your file in a complex file structure and need to find is 6 months later when the CEO realizes that it's the best report EVAH!<br /><br />The seventh button shows just the file name. The eighth button shows the tab name. (For the love of VLOOKUP, If you use this, change the tab name from Sheet1!)<br /><br />The eighth button allows you to insert a picture. A logo for example, you Excel fancypants.<br /><br />That last gray button is grayed out. I'm not sure how to un-gray it.<br /><br />The point with Header and Footer is don't try to make your own on the spreadsheet itself. Do it here, instead!<br /><br />Finally, we have Sheet. I like this tab of the Page Setup Interface best:<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\page_setup_print.jpg&s=1&r=&w=640&h=480"><br /><br />Under Print titles, if you define rows to repeat at top, that row will repeat on each printed page of your report. Same is true for columns! This will make the report so easy to work with for those people who like printed copies. Do this for them!<br /><br />Under print, I always check Gridlines. Because I like my gridlines to print without giving everything a border in the cell format.<br /><br />I normally don't use anything else, so I won't bore you with the details because if you've gotten this far, you're a super champ as it is!<br /><br />Good day!<br /><br />*Does my corporate America speak impress??<br /><span class="fullpost"></span>Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com0tag:blogger.com,1999:blog-4165587777214115821.post-540477925614348012008-10-29T07:38:00.004-04:002008-11-05T09:02:37.686-05:00IF and AND, togetherIf statements are a pretty powerful tool in your Guru belt. If you know IF, you probably know more than 50% of the people in your office. We've used IF here in conjunction with <a href="http://excel-expert.blogspot.com/2007/05/iserror.html">ISERROR</a> to format your spreadsheet nicely. Today, I want to use IF with AND because I really like these two functions together.<br /><br />Let's say you work for a company who only pays in increments of 15 minutes. So, if you show up at 2:03, you start getting paid at 2:15. And, if you leave at 5:03, you stop getting paid at 5:00. I know! Corporate America*, right? Geez.<br /><br /><br />Well, if you work in Corporate America and you're punching a time clock, all of that is already worked out in the payroll software, so you wouldn't really have any need to do this exercise. But, it's the best example I can think of...<br /><br />The End Result:<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\IF_AND_MINUTE_TIME.jpg&s=1&r=&w=640&h=480"><br /><br />Here's the formula that will do it:<br />=IF(AND(MINUTE(C2)>45,MINUTE(C2)<=59),TIME((HOUR(C2)+1),0,0),IF(AND(MINUTE(C2)>30,MINUTE(C2)<=45),TIME(HOUR(C2),45,0),IF(AND(MINUTE(C2)>15,MINUTE(C2)<=30),TIME(HOUR(C2),30,0),IF(AND(MINUTE(C2)>0,MINUTE(C2)<=15),TIME(HOUR(C2),15,0),C2))))<br /><br />Well, as they say in corporate America, how do you eat an elephant? One bite at a time! So, let's take this in little elephant chunks.** (Who eats elephants, anyway?)<br /><br />=IF(AND(MINUTE(C2)>45,MINUTE(C2)<=59),TIME((HOUR(C2)+1),0,0)<br /><br />The IF Statement is:<br />IF(AND(MINUTE(C2)>45,MINUTE(C2)<=59)<br /><span style="font-weight:bold;">IF this is true:</span> the minute is greater than 45 AND less than 59<br /><span style="font-weight:bold;">Return this value:</span><br />TIME((HOUR(C2)+1),0,0)<br />What a neat new function! With the TIME function, the first value represents the hour (in this case my starting hour + 1), the second position represents the minutes, and the third value represents seconds.<br /><br />If my IF statement was FALSE, return this value:<br />IF(AND(MINUTE(C2)>30,MINUTE(C2)<=45),TIME(HOUR(C2),45,0)<br /><br />Another IF statement! This is called Nesting IF statements, and you can only nest 7 IF statements in any one formula. But, you can add ANDs and ORs and test more than 7 conditions. Get creative, play with it!<br /><br />In English, this says if my minute value is between 30 and 45, return the TIME of the hour of my original start time and 45 minutes.<br /><br />If the value is false, another IF Statement! I think you get the drift. At the end, if none of the conditions are met in the 4 IF statements, then it will return the value of the original start time. If the formula is written right, it should always be the top of the hour so it falls in line with all the Corporate America BS guidelines. I hate that the man is always out to get me!<br /><br />I did another formula for the end time:<br /><br />IF(AND(MINUTE(D2)>=45,MINUTE(D2)<59),TIME(HOUR(D2),45,0),IF(AND(MINUTE(D2)>=30,MINUTE(D2)<45),TIME(HOUR(D2),30,0),IF(AND(MINUTE(D2)>=15,MINUTE(D2)<30),TIME(HOUR(D2),15,0),IF(AND(MINUTE(D2)>=0,MINUTE(D2)<15),TIME(HOUR(D2),0,0),D2))))<br /><br />I actually just dragged over the original formula, and then changed things up a bit so that the workers don't get paid for any minutes they work past the quarter hour until the next quarter hour begins. Take a close read and if you need the English version, let me know...<br /><br />*I kid about Corporate America! I love Corporate America! Corporate America loves me! I've never seen such a punishing time clock in Corporate America.<br />**I kid again! I love elephants, but I'm not so sure they love me back.<br /><span class="fullpost"></span>Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com0tag:blogger.com,1999:blog-4165587777214115821.post-795169551068015142008-10-28T12:35:00.003-04:002008-11-03T15:33:00.859-05:00Grouping without the subtotal wizardWhen you use the subtotal wizard, your data is grouped, and you can use buttons to expand and collapse your group:<br /><span style="font-style:italic;">Expanded:</span><br /><img src="http://farm1.static.flickr.com/230/508538409_43275c5320.jpg?v=0"><br /><br /><span style="font-style:italic;">Collapsed:</span><br /><img src="http://farm1.static.flickr.com/204/508513940_63279040bc.jpg?v=0"><br /><br /><a href="http://excel-expert.blogspot.com/search/label/Subtotal">Read more about subtotals here</a><br /><br />You can also group data in Excel without using the subtotal wizard. I had an occasion to do this just the other day, in fact.<br /><br />All you do is highlight the rows of data that should be grouped together. Then, choose:<br />Data-->Group and Outline-->Group<br /><br />Repeat as necessary for the different data groups you want to create.<br /><br />Why would you want to do this? I'm not sure. But, I did it to separate the top 90% of my purchased items from everything else. I could have assigned them all an ABC Code (A=top 90%, B=90-95%, C=95-100%) and added subtotals, but I didn't really want to do that, and I didn't need the subtotals. This was quick, easy, and very functional in this application. <br /><br />Pretty Neat Trick!<br /><span class="fullpost"> </span>Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com0tag:blogger.com,1999:blog-4165587777214115821.post-5585546266112007672008-10-27T10:49:00.006-04:002008-11-05T09:04:29.822-05:00Rank and Count, now together in one formula!RANK is a nice function. You can use it to see where an item falls on a list. You can get that same information by sorting the list, but there will be times when another sort takes precedence, and RANK will save the day.<br /><br />Start typing in the formula in the formula bar <br />=Rank(<br />and then hit the fx to bring up the Rank Wizard box<br />(there's other ways to do this. Pick the one you like best.)<br /><br />Here's the wizard box.<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\RANK_WIZARD.jpg&s=1&r=&w=640&h=480"><br /><br />The <span style="font-weight:bold;">number</span> is the value that you want to rank. The <span style="font-weight:bold;">Ref</span> is the range of numbers that are being ranked. Be sure to make this <span style="font-weight:bold;">absolute</span> using the dollar signs or your range will change when you copy and paste your formula down. The <span style="font-weight:bold;">order</span> specifies order. If you leave it blank, it assumes 0 which is descending. If you choose 1, it is ascending. In other words, the largest number in the list will be ranked number 1 if you choose 0 or leave it blank. The largest number in the list will be ranked last if you choose 1.<br /><br />If the list includes values which are the same, they will have the same rank.<br /><br />Assuming that a rank is only important in relation to the number of values, I like to add a count.<br /><br />In this instance, I want to count values in a spreadsheet that I will be adding to over time. So, instead of counting an absolute list such as $B$2:$B$67, I'm counting the whole column. My count formula looks like this:<br />=COUNT(B:B)<br /><br />If I wanted to count an absolute list, it would look like this:<br />=COUNT($B$2:$B$67)<br /><br />When I put the rank together with the count the formula looks like this:<br />=RANK(B2,$B$2:$B$68)&" of "& COUNT(B:B)<br /><br />I used the "&" to concatenate the two functions together with the text string " of ".<br /><br />The spreadsheet looks like this:<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\RANK_count.jpg&s=1&r=&w=640&h=480"><br /><br />Putting the two formulas together means that I can get rid of columns G and H entirely. I just left them in to show my work.<br /><br />I don't use Rank very often. I do use COUNT pretty often. I rarely use the two together. But, when I've needed them, it sure has been nice...<br /><span class="fullpost"></span>Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com3tag:blogger.com,1999:blog-4165587777214115821.post-76857396750563374302008-10-25T09:00:00.003-04:002008-11-03T15:33:47.069-05:00When Grouping by Dates in Pivot Tables won't workI started telling you how much I loved Pivot tables, and then abandoned my blog. I apologize.<br /><br />After I discovered that you can group by dates in Pivot tables, I also realized that sometimes it didn't work. It took me awhile, but I finally figured it out. Basically, if your data set includes blanks, the Pivot table is unable to Group By Dates. Why? Because Blank is not a valid date. I'm hoping Microsoft fixes this in the next iteration. They may already have, but I don't have that version of Excel, yet. <br /><br />Let me walk you through why you would want to include blank rows in your data set, how to get rid of the blanks in your pivot table and how to group by dates in your pivot table without using the group by function.<br /><br />There are times when I create a Pivot Table when I know that my source data will not always be $A$1:$C$67<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot_wizard2of3.bmp&r=&s=1&w=0&h=0&u=1&file=Pivot_wizard2of3.bmp"><br /><br />Knowing that I will continue to add to my source data, I choose A:C as my Pivot table. This would happen if you, as a flea market owner, intend to continue to add rows to your original source data as you continue to sell items. You could use the pivot table wizard to redefine your data set every day, or you could expand your data set and just refresh your pivot table as needed.<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot2of32.jpg&r=&s=1&w=0&h=0&u=1&file=Pivot2of32.jpg"><br /><br />Now, you have a row for (blank) in your pivot table. Bleh.<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot(blank).jpg&r=&s=1&w=0&h=0&u=1&file=Pivot(blank).jpg"><br /><br />And, you don't really want that because it serves no purpose in this application, so you get rid of that blank line by clicking the pull down on the item column, scrolling through until you find blank, and then un-checking it.<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot_uncheck_blank.jpg&r=&s=1&w=0&h=0&u=1&file=Pivot_uncheck_blank.jpg"><br /><br />But, now your nicely summarized months are gone, replaced by specific dates because a blank is not a valid date format. What was once nice to look it:<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot_Group_Example.bmp&s=1&r=&w=640&h=480"><br /><br />Now makes you cringe and frown.<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot_bad_summary.jpg&r=&s=1&w=0&h=0&u=1&file=Pivot_bad_summary.jpg"><br /><br />When you try to resummarize it by month following instructions provided in my post <a href="http://excel-expert.blogspot.com/2007/08/pivot-tables-and-why-i-love-them.html">Pivot tables and why I love them</A>, instead of getting the nice box where you can choose Months, years, hours, quarters, etc, you just get one group called Group 1.<br /><br />But, you've reached the conclusion that you will continue to add to your original data set and it's not feasible to redefine the data set every time you want to see fresh data.<br /><br />Here's what you do!<br />Go back to your original data set and add three calculated fields. One each for Month, Year, and Quarter<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot_month_calc.jpg&r=&s=1&w=0&h=0&u=1&file=Pivot_month_calc.jpg"><br /><br />The calculation for month is above. The calculation for year is pretty straightforward:<br />=YEAR(C2)<br /><br />The calculation for Quarter is more involved:<br />=IF(OR(D2=1,D2=2,D2=3),1,IF(OR(D2=4,D2=5,D2=6),2,IF(OR(D2=7,D2=8,D2=9),3,4)))<br /><br />In English, if the month is 1, 2 or 3, the quarter is 1. If the month is 3, 4 or 5, the quarter is 2. If the month is 7, 8 or 9, the quarter is 3. Otherwise, the quarter is 4.<br /><br />I like that calculation. They're may be a different way to get at quarters, but this works. You could also change the 1 to "Q1", etc, if you want to get fancy.<br /><br />Now, go back to your pivot table, right click and choose "Pivot Table Wizard."<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot_right_click.jpg&r=&s=1&w=0&h=0&u=1&file=Pivot_right_click.jpg"><br /><br />It will start you at Step 3 of 3, and you are redefining your data set here and you need to back to step 3. So, choose Back. You can either drag your cursor from A to F, or just type it in manually:<br />Data!$A:$F<br /><br />Now, choose next and then finish. Now, in your field list, you have the newly created calculated columns:<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot_item_list.jpg&r=&s=1&w=0&h=0&u=1&file=Pivot_item_list.jpg"><br /><br />Drag and drop years, months, and quarter to summarize the data the way that you like. Change up as needed.<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot_view.jpg&r=&s=1&w=0&h=0&u=1&file=Pivot_view.jpg"><br /><br />Do those odd column totals bug you? Me, too.<br /><br />Hover your mouse over those year total columns until you see a black arrow, then right click. This will highlight all year total columns. Now, right click and choose hide.<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot_hover_arrow.jpg&r=&s=1&w=0&h=0&u=1&file=Pivot_hover_arrow.jpg"><br /><br />If you want to get it back, right click on the word, "YEAR" and choose field settings.<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot_field_settings.jpg&r=&s=1&w=0&h=0&u=1&file=Pivot_field_settings.jpg"><br /><br />Under subtotals, click automatic. You can also choose sum (same at automatic), count, average, etc. Or, a combination of any or all of those in the option box. Use this wisely, people. You don't want your pivot table to get too complex or you'll lose your users which may just be yourself and it's no good to lose yourself over a pivot table.<br /><br />So, now it's looking pretty OK.<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot_formatting1.jpg&r=&s=1&w=0&h=0&u=1&file=Pivot_formatting1.jpg"><br /><br />But, you probably want to format it. So, hover your mouse on one of your category totals. Once you get that hover arrow, left click to highlight all category totals as shown in the image above. Now, right click and format as you see fit.<br /><br />That was a little more than When Grouping By Dates in Pivot Tables won't work. But, what can I tell you. I get wordy...<br /><span class="fullpost"></span>Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com3tag:blogger.com,1999:blog-4165587777214115821.post-48665787501531238162008-10-22T20:32:00.002-04:002008-11-03T15:34:06.016-05:00Time CalculationsI was working on a timesheet in Excel the other day, and I was surprised to see that 4:00 PM minus 2:00 PM doesn't equal 2 hours. Very surprised indeed...<br /><br />So, you know what I did? I hit F1. F1 and my husband and my friend Dawn are the three reasons I am the excel guru that I claim to be.<br /><br />F1 was very helpful, but I took what it told me and expanded my new found knowledge to get it to do what I actually wanted my timesheet application to do. What I wanted my application to do was calculate the elapsed time in hours between a start and end time in one 24 hour period (as in, not to exceed a day).<br /><br />Here's what I wanted to see:<br /><br />From To Elapsed Time in Hours<br />2:00 PM 4:00 PM 2<br /><br />When I subtracted 2:00 PM from 4:00 PM like this =(B2-A2), here's what I got:<br />From To Elapsed Time in Hours<br />2:00 PM 4:00 PM <span style="font-weight:bold;"> .0833</span><br />When displayed as time, .0833 = 2:00 AM<br /><br />So, I think I get what it's doing, but it's not what I want.<br /><br />F1 suggested a few functions, including the <span style="font-weight:bold;">hour</span> function. So, I changed my formula to this:<br />=HOUR(B2-A2)<br /><br />And that converted .0833 to the number 2. Which was precisely what I wanted in this particular instance.<br /><br />So, I pulled my formula down, and I discovered that when my start time was 2:00 and me end time was 4:45, my result was still the number 2.<br /><br />I hit F1 again, and finished reading the help article. It was then that I discovered the <span style="font-weight:bold;">MINUTE</span> function.<br /><br />Using the minute function on this data set:<br />From To <br />2:00 PM 4:45 PM <br />=MINUTE(B2-A2)<br />Yields this result:<br />45<br /><br />So, I put the two functions together in this simple formula:<br />=HOUR(B2-A2)+(MINUTE(B2-A2)/60)<br /><br />It now shows me the difference in hours plus the difference in minutes divided by 60.<br /><br />From To Elapsed time in hours<br />2:00 PM 4:45 PM 2.7500<br /><br />Easy, peasy puddin' pie.<br /><br />Just keep in mind, this will not work if the time spans more than 24 hours.<br /><span class="fullpost"> </span>Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com2tag:blogger.com,1999:blog-4165587777214115821.post-53630483376815565762008-01-27T21:07:00.002-05:002008-11-03T15:34:38.578-05:00Recent Keyword ActivityI have to say that the questions I have rec'd here, though few, have humbled me. I still think I know excel better than most people I know. But, it seems the people who find me know more than me. Still, let me respond to some recent Keyword Activity...<br /><br /><span style="font-style: italic;">how to concatenate and keep spaces</span><br />Put them in quotation marks. For example:<br />=Concatenate(a1," ",b1," and then finally ", c1)<br />IF<br />A1=CAT<br />B1=BAT<br />C1=SAT<br />Result=<br />CAT BAT and then finally SAT<br /><br /><br /><span style="font-style: italic;">keep table array when pasting vlookup formula</span><br />Make the table array absolute using $. For example:<br />=VLOOKUP(a1,source!<span style="font-weight: bold;">$A$1:$B$500</span>,2,0)<br />Whenever you pull down the formula, the table array will remain absolute at $A$1:$B$500.<br /><br /><span style="font-style: italic;">how to make excel look at a date as text</span><br />where the value in A1 is a date, use this formula;<br />=text(a1,"mm/dd/yyyy")<br />You can also use m/d/yyyy or mm/yyyy or yyyy/mm/dd or yyyymmdd or whatever date format you want. But, whatever the format you choose, it is now TEXT and will not change to a number if you change the format. Well, you just can't change the format. It's a text field now. The original data, however, isn't a text field. It's still whatever it started out as.<br /><br /><span style="font-style: italic; font-weight: bold;">Cool tip of the Day:</span> From the kicking it old school old school of thought, if you want to make any cell absolute in your formula without typing the $, highlight the formula and hit F4.<br /><span class="fullpost"></span>Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com0tag:blogger.com,1999:blog-4165587777214115821.post-6126796232672035782008-01-08T14:49:00.003-05:002008-11-03T15:34:58.165-05:00Helping You Out, part 2<p>Anonymous said on 1/8/08:</p><br /><p><span style="color:Green"><em>I am in desperate need of help. My firm has a special toolbar they use to make sure all reports we generate are formatted in a specific way. I think it must involve a lot of custom formats because now I keep getting an error message saying "Too many custom formats" whenever I try to do anything. I tried deleting some of the custom formats but that doesn't seem to work. Any ideas?</em></span></p><br /><br /><p>This one is tough because I think the problem is probably the code that formats your report. It's probably in Visual Basic for Applications, and your IT group would have to trouble shoot the problem. Can you tell if the error message is from excel or from the "special toolbar"?</p><br /><p>I do have one suggestion, though. If you try this, please save a copy of your work and perform these steps on the copy, so that you don't lose any of the work you have done so far.</p><br /><ol><br /> <li>Select all entire worksheet (one easy way to do this is to hit shift-ctrl-down arrow-right arrow from cell A1)</li><br /> <li>Select Edit-->Clear-->Formats</li><br /> <li>Now, rerun the script on the special toolbar.</li><br /></ol><br /><p>What I'm trying to have you do is clear all the formats so the macros in the toolbars can have an easier time doing their thing. Without knowing anything, I would guess that you might be working on a sheet that has a lot of formats maybe even in cells that don't contain data. Clearing out all the formats might all the scripts on the toolbar to do the work it needs to do.</p><br /><p>I hope this helps. I suspect that it may not. If you try, though, please do it on a copy!</p><span class="fullpost"></span>Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com0tag:blogger.com,1999:blog-4165587777214115821.post-37023067945237847632008-01-08T14:35:00.002-05:002008-10-30T21:00:34.602-04:00Helping you out!<p>Question:</p><br /><p>Anonymous said on 1/7/08:</p><br /><p><em><span style="color:Green">"I have two fields that I need to compare and bring back data in a third field if they match.<br /><br />Basically, it's a simple VLOOKUP equation.<br />But, these two number fields (which are currently both formatted as text) don't always bring back the data, unless I 'retype' the data, then it recognizes that they are the same.<br />Can you think of why I would need to retype the exact same data for it to recognize it?<br /><br />I'm bringing in the data via copy/paste, but using field format options to ensure they're both the same."</span></em></p><span class="fullpost"><br /><p>You're using the field format option to ensure they are both the same, and that is definitely the right thing to do. You may just have to take it a step further.</p><br /><p><strong><span style="text-decoration:underline">OPTION 1</span></strong></p><br /><p>When you bring in the data via copy/paste, choose Paste Special and <a href="http://excel-expert.blogspot.com/search/label/Paste%20Special">paste as values</a>. If that doesn't work, go back to the original lookup value, select that column, copy it and paste it special as values (don't paste it somewhere else. Paste it right where it is).</p><br /><p><strong><span style="text-decoration:underline">OPTION 2</span></strong></p><br /><p>If the steps in Option 1 don't work, take it to the next level by formatting the fields so they are exactly the same using the Data-->Text to columns wizard.</p><br /><ol><br /> <li>Select the column that contains the lookup value.</li><br /> <li>Click on Data-->Text to Columns. This will bring up a "Convert Text to Columns Wizard."</li><br /> <li>The first step is to choose whether your data is "delimited" or "fixed width." Choose delimited here.</li><br /> <li>Second step is to either choose the delimiter or the width of your text. Make it as long as your longest data.</li><br /> <li>Third step is to choose the data format. Make it a number or a text, or whatever makes the most sense to you.</li><br /></ol><br /><p>Now, select the first column in your table array and repeat the steps, ensuring that you set up this column of data precisely the same way you set up your lookup value. You may need to play with the options in each of the three steps. Choosing delimited always worked for me. But, that might not work for you.</p><br /><p><span style="text-decoration:underline"><strong>OPTION 3</strong></span></p><br /><p>Another thing that may be hinking you up are spaces. If your lookup values should not contain spaces, you can do this:</p><br /><ol><br /> <li>Select the column that contains the lookup value.</li><br /> <li>Hit Ctrl-F (or Ctrl-H to save step 4...either works exactly the same).</li><br /> <li>Enter a space in the "Find What" box.</li><br /> <li>Hit the replace tab.</li><br /> <li>Enter nothing in the "Replace with" box.</li><br /> <li>Hit "Replace All"</li><br /> <li>Repeat these steps the first column in your table array.</li><br /></ol><br /><p>The first option might do the trick. The second option should work all the time (I hope. It's always worked for me.) The third option is a little easier, but you have to be careful and you can't use this if your lookup data is suppose to contain spaces.</p><br /><p>Let me know if you need pictures, and let me know if this works (or not)!</p><br /></span>Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com1tag:blogger.com,1999:blog-4165587777214115821.post-270977737648456972007-12-18T22:28:00.005-05:002008-11-03T15:35:21.526-05:00I need your help!I thought that I knew a lot about Excel, but the truth is, I can't think of one more thing to share with you. Although if you master the things covered here, you WILL be the Excel Expert in your office. You will shock and amaze your office mates, in general. But, I want to answer more questions. I can't think of anything to answer, though. Will you please ask me a question, friend?<br /><span class="fullpost"></span>Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com2tag:blogger.com,1999:blog-4165587777214115821.post-18701124837112381142007-10-05T14:44:00.002-04:002008-10-30T21:01:24.388-04:00How you found meHere are some keywords that brought you here. I wish I knew what you were looking for because I think I could help.<br /><br /><span style="font-weight:bold;">Excel Bring Second Value:</span> I don't know what that could mean, but maybe you could try an If statement? Give me more, <span class="fullpost">and I think I can help.<br /><br /><span style="font-weight:bold;">VLOOOKUP:</span> I must have a typo like you. The problem here, friend, is one too many O's in your function. If you figured that out and still need more information, see here:<a href="http://excel-expert.blogspot.com/2007/05/vlookup.html">VLOOKUP</a><br /><br /><span style="font-weight:bold;">Table Array Vlookup:</span> I hope the VLOOKUP post helped in some way.<br /><br /><span style="font-weight:bold;">VLOOKUP Second Result:</span> I don't know, but might I suggest an IF combined with an ISERROR and VLOOKUP, perhaps? See <a href="http://excel-expert.blogspot.com/2007/05/iserror.html">here.</a><br /><br />My favorite: <span style="font-weight:bold;">vlookup getting col_index_num from a list:</span> You can totally do this, but I don't know why you would want to:<br />It looks like this:<br />=VLOOKUP(A7,Data!$A$2:$F$67,<span style="font-style:italic;"><span style="font-weight:bold;">Sheet1!A12</span></span>,0) When you fill in the formula, it moves down the list, too. That was pretty easy to figure out, though, so I'm sure the super user looking for this solution won't be happy with what I've shown him here.<br /><br /><span style="font-weight:bold;">stop copy and paste data validation:</span> Have you tried to copy and then paste special, paste values?<br /><br /><span style="font-weight:bold;">complicated vlookup returning #value:</span> There could be a lot of things to check here. You might try formatting the lookup values on the original worksheet and the in the table array exactly the same. When I'm really struggling and I know there should be a match, I use Data-->Text to columns to make sure everything is formatted exactly the same. Plus, if you can, highlight your lookup values, do a CTRL-F to find all spaces, and then replace all the spaces with nothing. This only works if your lookup values don't have spaces legitimately.<br /><br />I really am happy to answer questions. It's quite possible I won't be able to help, but if I can, I will be happy to. Click on the Contact Me box, and I will be happy to help you out.<br /></span>Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com0tag:blogger.com,1999:blog-4165587777214115821.post-91546945249302872052007-08-30T08:56:00.000-04:002007-08-30T09:12:02.123-04:00Pivoting the data, Lesson TwoSo, you've got a Pivot Table and you think it's OK. But now, you really want to pivot the data.<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot_Group_Example.bmp&s=1&r=&w=640&h=480"><br /><br />Put your cursor on Item right there in the pivot table. Drag it to Columns section of your pivot table, where the months currently are. Drop it. Now, you have this:<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivoting%20Step%201.bmp&s=1&r=&w=640&h=480"><br /><br />It's an interesting, but useless, way to present data. So, keep pivoting. Click on Months and drag it to the row section of your pivot table. Now, you have this:<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivoting%20Step%202.bmp&s=1&r=&w=640&h=480"><br /><br />I don't really like this view much, either, but there may be instances when you have applications when pivoting the data like this would be beneficial.<br /><br />Next time, we can start looking at formatting the data. It was when I started trying to format the data that I realized that PIVOT TABLES<>Excel.Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com0tag:blogger.com,1999:blog-4165587777214115821.post-28029377833769888442007-08-22T09:03:00.000-04:002007-08-23T13:15:04.523-04:00Pivot Tables and why I Love ThemThe thing that is so fantastic about Pivot tables is that you can summarize huge amounts of data to help you and your users better understand what your huge amount of data has to say to you. You can also summarize small amount of data, too. And, then once you've summarized your data in a Pivot Table, you can "pivot" the data to look at in a different way.<br /><br />I used to hate pivot tables. But that was back when they confused me, and I couldn't understand why they were called "Pivot" tables. Now, I get them. I really, really get them. And, I love them. I hope you will, too.<br /><br />I've alluded to this before. Pivot tables are pretty easy, but they aren't exactly like Excel. I don't know what Microsoft has to say about this, but I personally consider Pivot tables to be an application within an application. If you try to use the data in a Pivot table just like you would the data in Excel, you'll hate Pivot tables. So, before we even start, I want you to commit to thinking of Pivot tables as its own application. Can you do that? Doing that will be the thing that takes you from User to Super-User! Yay, are all your dreams coming true?<br /><br />Assuming you can commit, let's pretend that you are a flea market/pet shop owner. You have been keeping a list of the items that you've sold and when you've sold them, but so far, that data isn't really speaking to you. It's not telling you a story.<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Flea_Mkt_Sales.bmp&s=1&r=&w=640&h=480"><br /><br />Click on Data-->PivotTable and PivotChart Report to bring up this wizard:<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot_Wizard.bmp&r=&s=1&w=0&h=0&u=1&file=Pivot_Wizard.bmp"><br /><br />Because this is a basic lesson, let's go with the defaults, and then click NEXT. Excel is so clever, that it knows where your data is and will highlight it for you, as long as it contiguous. For this example, go with what Excel suggests:<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot_wizard2of3.bmp&r=&s=1&w=0&h=0&u=1&file=Pivot_wizard2of3.bmp"><br /><br />and just click next.<br /><br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot_wizard3of3.bmp&r=&s=1&w=0&h=0&u=1&file=Pivot_wizard3of3.bmp"><br /><br />Go with the defaults here and click Finish. You can choose where you want the pivot table to go right now, if you are so inclined. Doesn't really matter, though. You can copy and paste the whole thing if you need to move it somewhere else.<br /><br />Here's what you will see:<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot_Screen.bmp&s=1&r=&w=640&h=480"><br /><br />Now just start dragging and dropping. <br /><br />Oh, gosh. Before you start, let me give you some definitions:<br /><b>Row Fields:</b> Data that will appear in the Rows.<br /><b>Column Fields:</b> Data that will appear in the Columns.<br /><b>Page Fields:</b> I don't use this very often. And, I'm not missing out on much, either.<br /><b>Data Fields:</b> Here's where things get summarized. So, the data field will always be a number. It can be a sum, average, count, min, max, etc. You can put text fields here and then count them. You can put date fields here and count, min, max or average them. Or you can put a number data here and summarize it however you want.<br /><br />I normally start with the Row Fields first. In this case, I want to see a summary of the items sold. So, click on ITEMS and drag and drop it to the Row Fields Box. Now Drag over Qty Sold to the data box.<br />Check it out! You've summarized your data by item.<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Example1.bmp&s=1&r=&w=640&h=480"><br /><br />So, you can see that you sold 97 Llamas, but only 21 Llama harness. There's an opportunity right there, I think.<br /><br />This view is interesting, for sure. But, it doesn't give you any ideas about when your items were sold. Go back to your field list, drag and drop "Date Sold" to the Row Block. Say, now that's interesting.<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Example2.bmp&s=1&r=&w=640&h=480"><br /><br />But, you'd like to see it with the dates across the top. You can do that. Grab Date Sold, either from the field list or the pivot table, and drag it to the Column Block. Now look!<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Example3.bmp&s=1&r=&w=640&h=480"><br /><br />This is not easy to look at, though, is it? Since Pivot Tables are so great at summarizing, you don't want to do all that paging over. Ick. Pivot tables has a solution. Right click on any date in the column headings. Choose Group and Show Detail and then Group. <br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot_Group.bmp&s=1&r=&w=640&h=480"><br /><br />You get this dialog box. <br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot_Group2.bmp&s=1&r=&w=0&h=0"><br /><br />To make things easy, choose months. You get this view. NICE.<br /><img src="http://ive-got.no-ip.org/photos/ShowImg.asp?img=\Excel_Blog\Pivot_Group_Example.bmp&s=1&r=&w=640&h=480"><br /><br />I hope this has been educational. Tomorrow, I'll show you how to actually Pivot that data, really turning it on its side.<br /><br />Love Pivot Tables.<br /><br />Click here for the sample data to play with:<br /><a href="http://ive-got.no-ip.org/tricia/Flea_Mkt_Pivot_Example.xls">Pivot Data</a>Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com2tag:blogger.com,1999:blog-4165587777214115821.post-18665241354959101182007-06-04T22:39:00.001-04:002008-11-05T09:07:46.513-05:00Advanced FilterOK, let's say you have a list of items. It's a long list, and you know there are duplicates in the list. You want to condense the list to its unique items. You could use a pivot table or subtotals, but that's really too in-depth for you for this list at this point in time.<br /><br />Advanced Filter to the rescue.<br /><br />Quick, highlight your list of items. Or, click on the whole column. Now, click on Data-->Filter-->Advanced Filter.<br /><br />You will see this dialog box:<br /><img src="http://farm2.static.flickr.com/1027/530885894_4b16021201_m.jpg"><br />Click on "Copy to another location"<br /><br />Doing that will un-gray the "Copy to" box. Use your zoomy box to choose where your list with unique records only will reside. Now, click the "unique records only" box. Hit OK. You're done. Now, you have a comprehensive list of unique items.<br /><br />Quick and Easy.Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com0tag:blogger.com,1999:blog-4165587777214115821.post-39421353484111599872007-05-23T23:04:00.001-04:002008-11-05T09:05:03.523-05:00IserrorIf you are obsessed with both formatting and efficiency, you just have to add iserror to your bag of tricks.<br /><br />The way I use iserror is to keep those error messages off my spreadsheets. That isn't the only thing you can do with iserror, but it's how I use it.<br /><br />Let's say that you have mastered <a href="http://excel-expert.blogspot.com/2007/05/vlookup.html">VLOOKUP</a>. But your two lists don't contain all of the same data. You don't really care about that. If your VLOOKUP doesn't find a value, that's OK. You just don't want to see an #N/A. <br /><br />Here's what you do. First, build your VLOOKUP. GO ahead. Use the wizard. You know how.<br />=VLOOKUP(A2,[Phone.xls]Sheet1!$A$2:$B$312,2,0)<br />Now, add the iserror:<br />=ISERROR(VLOOKUP(A2,[Phone.xls]Sheet1!$A$2:$B$312,2,0))<br /><br />This function is just asking a True or False Question. In this case, it is asking is my VLOOKUP returning a value or an error?<br /><br />If it finds a value, the answer is NO or FALSE. If it finds no value and returns an error, the answer is YES or TRUE. You don't want to see TRUE or FALSE, though. You want to see a value or nothing. So, you need to use an IF.<br /><br />=IF(ISERROR(VLOOKUP(A2,[Phone.xls]Sheet1!$A$2:$B$312,2,0)),"",VLOOKUP(A2,[Phone.xls]<br />Sheet1!$A$2:$B$312,2,0))<br /><br />Now, the formula can be read like this:<br />If my VLOOKUP returns an error, enter a blank (""), otherwise, return the VLOOKUP result.<br /><br />Although not slam dunk easy, if you take this in little chunks, it will work out just fine for you. And, it will save you time because you won't have to bother deleting the #N/A. So annoying.Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com5tag:blogger.com,1999:blog-4165587777214115821.post-77065936262733851422007-05-22T21:09:00.000-04:002007-05-22T22:27:22.980-04:00Data Validation (aka, Dropdown Box)If you want to use Excel as a Data Collection Tool, Data Validation is an absolute must. Data Validation allows the user to pick a value from a drop down box. That's not all it does, though. You can also add a data validation that will only allow values that are within a predefined range. You can also add data validation that checks the text length and makes sure it's within a predefined range.<br /><br />When you set up data validation so that the user can pick a value from a list, the user will see a drop down box. In all other instances, the user will NOT see a drop down list.<br /><br />Choose Data-->Validation and you'll see this dialog box:<br /><img src="http://farm1.static.flickr.com/227/510160460_a36f00ebec.jpg?v=0"> <br />If you choose whole number, decimal, date, time or text length, you get these options:<br /><img src="http://farm1.static.flickr.com/229/510163588_a5469e3bba.jpg?v=0"><br />Either enter values in the boxes, or choose a cell that contains the value you'll be using to validate your data. The nice thing about choosing a cell is that you can change what is being validated without actually going into this box.<br /><br />The other two tabs are pretty straightforward. On the input message tab, you have three entry options. The first is a check box that allows you to Show Message when cell is collected. The second is Title, and the last is the message. Here's a neat trick. Let's say you choose Any Value on the settings tab (so, your data validation isn't validating anything. It's just allowing ANY VALUE). Then you check the checkbox on the Input Message Tab. Then you enter a title and message. When your user lands on the cell with the data validation, they get a pop-up message. You get the message without restricting what data goes into the cell. Or, restrict the data, and still get the pop-up message. Either way is fine. But, if you'd been looking for a way, besides comments, to alert your user to something when they land in a cell, use Data Validation for its messaging. It's a good thing.<br /><br />The last tab is Error Alert. You only use this if you are actually setting up a data validation that restricts data (You don't need it if you choose ANY VALUE on the settings tab). When your user enters invalid data, they get your error message that you created telling them what they did wrong. There are 4 inputs on this tab:<br />1. A check box allowing you turn off or on the error message.<br />2. A style: choose between STOP, WARNING, or INFORMATION.<br />3. Title<br />4. Error Message.<br />If you need to inform your user using Excel, be sure to include an error message.<br /><br />I want to focus on lists. I've never used custom, and all the others are pretty straightforward. So, lists.<br /><br />On the settings tab of the Data Validation Dialog box, when you choose Lists, you get 4 other inputs. <br />1. Ignore Blank. Check this and the user can make it through the cell without choosing or entering a value.<br />2. In-cell drop down. Check this, and your user can choose from a list of values.<br />3. Source. Here, you have options. You can enter the list right there in the box, separating the values with commas. Or, click on the zoomy box<img src="http://farm1.static.flickr.com/231/510244416_7a49405d8a.jpg?v=0">, and then highlight the list you want your user to choose from. If you do this, you are limited to a list of data on the worksheet that contains your data validation. If you want to move your lists of data to another tab, you can do that, too, but it's not quite as straightforward. But, it's easy once you've done it a couple of times. <br /><br />Go to another tab and do whatever you have to do get your list together (type it, copy and paste, whatever suits you.) Now, highlight that list. Put your cursor in the name box, and type the name of the range. Name it whatever you please. But, wait! Where is the elusive name box? you ask.<br /><img src="http://farm1.static.flickr.com/220/510215608_928638aacf.jpg?v=0"><br />There it is. In the top left corner. I circled it and called it NAME BOX. If you don't type over it, it normally contains your current cell address.<br /><br />Once you have your named range, in the data validation dialog box on the settings tab, type =NAME BOX (except substitute the name that you have chosen. If you need to change the list in the future, choose Insert-->Name-->Define. This will bring up a dialog box where you can change the cells that are included in your named range. Your named range can only be one column of data if you plan on using it for a drop down box for data validation.<br /><br />Here's what it looks like:<br /><img src="http://farm1.static.flickr.com/203/510246127_c8e5af7841_m.jpg"><br />If you or your user tries to enter a value that is not on your list, they will get an error message. They either get the error message that you defined or a generic message. <br /><br />Drop Down Boxes and Data Validation.<br /><br />Fun for all.Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com1tag:blogger.com,1999:blog-4165587777214115821.post-9793024668994887872007-05-21T19:15:00.000-04:002008-10-28T13:36:08.277-04:00SubtotalUsing the Excel wizard to subtotal data can come in handy, and the final presentation is pretty nice, too. It has recently fallen out of favor with me personally because I've discovered and fallen in love with pivot tables. I say that in real life, too. I love pivot tables.<br /><br />Regardless, here's how you do it:<br />Step 1: Sort the data based on the column that has the data that you want to subtotal. Let's say you have a list of SKUs (Stock Keeping Units), their associated A,B,C code, along with their current value in inventory. You want to subtotal based on the ABC code, so sort by the column that contains that data. Now, highlight your data.<br />Step 2: Select Data-->Subtotals. You will get this dialogue box:<br /><img src="http://farm1.static.flickr.com/225/508525537_6d01ab59ce.jpg?v=0"><br /><br />You can make plenty of changes here. But for this simple subtotal, I like what it's telling you it's going to do. At each change in ABC, it is going to sum up the total in the Value Column. You can see here that if your list isn't sorted, it's going to add a subtotal to the value column every time there's a change to the ABC. (<span style="font-style:italic;">That would be very bad...</span>) Since I have it sorted right, I expect to there to be three subtotals. You can use the following subtotaling functions:<br />SUM<br />COUNT<br />AVERAGE<br />MAX<br />MIN<br />PRODUCT<br />COUNT NUMS<br />STDEV<br />STDEVP<br />VAR<br />VARP<br /><br />Here's what your subtotaled data will look like:<br /><img src="http://farm1.static.flickr.com/230/508538409_43275c5320.jpg?v=0"><br />Click on the + or - sign to expand or collapse data. Here's what it looks like all collapsed.<br /><img src="http://farm1.static.flickr.com/204/508513940_63279040bc.jpg?v=0"><br />You can also use the 1, 2 or 3 in the upper left corner to show and hide data. Hit 1 to collapse it completely, 2 to show the subtotaled information and 3 to show all the data along with the subtotals.<br /><br />There is more to subtotals. Lots more. Let's say you have a much more complex set of data. You want to add subtotals to your subtotals. You can do that with the wizard. Highlight your already subtotaled data, and then choose data-->subtotals once again. This time, uncheck the box that says "Replace Current Subtotals." That will add another layer of subtotals. And, the 1,2,3 you see currently in the upper left corner expand to 4 and 5. I don't know how many times you can do this. But, my advice would be limit it to 3 or 4. <span style="font-style:italic;">(Did I say 3 or 4? I meant to say 2.)</span> Otherwise, you've gotten too complex for your subtotals anyway. <br /><br />You can also do different types of subtotals on one report, though you have to leave the comfort of the wizard for that.<br /><br />If you click on the subtotal function, you will see that it's a simple formula:<br />=SUBTOTAL(9,C2:C30)<br /><br />The thing that makes this one sum up C2:C30 is the 9. If you replaced the 9 with a 1, it would average the data. Replace it with a 4, and it returns the max value. See below for more of what you can do with the subtotal function.<br />1: Average<br />2: Count<br />3: Counta<br />4: Max<br />5: Min<br />9: Sum<br />(See Excel help for the complete list. Choose topic -> Subtotal<br /><br />When using the wizard, you can only have it do 1 function per column. You do not have the option of summing the inventory value and counting the SKUs when you use the wizard. What I have done to get around this and still use the wizard is to tell it to sum the Item and the Value. Once it does it's thing and subtotal my data, I highlight the column containing items, and then do a ctrl-F to Find all cells containing:<br />subtotal(9,<br /><br />Then, I replace that with<br />subtotal(3,<br /><br />There may actually be a better way to do this. If you know of it and feel like sharing, please do. In the meantime, this works!<br /><br />Happy subtotaling!!!Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com2tag:blogger.com,1999:blog-4165587777214115821.post-42886851356142898662007-05-21T11:32:00.001-04:002008-11-05T09:07:23.813-05:00Quick Answers to Searcher's Questions<strong>Can you use concatenate in a VLOOKUP?</strong><br />The answer appears to be YES and NO.<br /><br />=VLOOKUP(CONCATENATE(A6,B6),'Table Array'!$A$2:$D$6,4,0)<br /><br />Your lookup_value CAN be a formula, just like you see above. It does not appear that your table array can include formulas, though. I suggest that you concatenate your lookup value in the VLOOKUP, and then create a column for the same concatenation on your sheet containing the table array. This is a good question, though, and I can see how my solution might not be satisfactory. I'll keep looking into this one.<br /><br /><strong>How do I make a table array absolute?</strong><br /><em>Example: table array is NOT absolute</em><br />=VLOOKUP(CONCATENATE(A6,B6),'Table Array'!A2:D6,4,0)<br /><br /><em>Example: table array IS absolute</em><br />=VLOOKUP(CONCATENATE(A6,B6),'Table Array'!<em>$A$2:$D$6</em>,4,0)<br /><br />The only difference is that you add the dollar signs in front of the cell's column and row references. I recommend, when in doubt, always make the table array absolute. I can't think of any reason not to. But, if it isn't absolute, you may not get the results you expect. So, always absolute!<br /><br /><strong>Excel Label Formatting:</strong><br />Someone searched on this and found me, but did not actually ask a question. I'm going to assume the question refers to mailing labels or something like that. My suggestion is to make your list in excel, and use Mail Merge in Word. I'm not an expert there, but I have used it, and it works very nicely.<br /><br /><span style="font-weight:bold;">Choosing from a Number list</span><br />What this searcher is looking for is data validation. Click on the cell where you want to be able to choose from a number list, and then click data-->validation. This brings up a dialogue box. Under validation criteria, choose List. In source box, enter the cells that contain your list of numbers, or type the number in yourself, separated by commas. If you want your list of numbers to be somewhere on the worksheet, just highlight them. If you want them on a different sheet in your workbook, you have to name the range, and then make your list equal to your named range. Since this is a quick answer, I'll say look at Excel Help for named ranges to figure out how to make this happen. Leave me a comment or send me an email if you need an explanation on this.<br /><br /><span style="font-weight:bold;">Change the format of phone numbers in Excel</span><br />Do this by bringing up the format cell box. Now, on the number tab, click on custom. Type in the custom format in the Type box. Let's say your format is currently:<br />###-###-####<br />This would format 5551112222 as 555-111-2222<br />Change the type so that it looks like this:<br />(1)-###-###-####<br />Now, your phone number looks like this:<br />(1)-555-111-2222<br /><br />Another searcher asks about:<br /><span style="font-weight:bold;">Excel Function to Group like items</span><br />The quick answer is Pivot Tables. If you delve into this on your own, let me say that 1)They are awesome 2) They always work. 3) They're kinda simple, really 3)They don't really act like Excel and that makes them seem complex.<br /><br />I plan on covering a whole set of Pivot Table topics as the days go by, so more on this later.<br /><br />Today, a searcher asked about what seems like conditional formatting. He or she searched on:<br /><strong>If this column date less than show as color excel</strong><br />They found me on the 7th page of their Google Search. I hope they found what they are loooking for and disovered the thing to do is use Conditional Formatting, Formula is Option.Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com0tag:blogger.com,1999:blog-4165587777214115821.post-5753494225098099372007-05-19T09:54:00.001-04:002008-11-05T09:05:44.576-05:00ConcatenateConcatenate is a big word and a handy function! <br /><br />It means to <a href="http://dictionary.reference.com/browse/concatenate">link</a> together like a chain. And, that is exactly what you will do with this handy formula.<br /><br />Click on your formula bar.<br /><img src="http://farm1.static.flickr.com/222/504404278_1c263a60f6.jpg?v=0"> <br /><br />Type in the formula. You can combine your own text, like I did with the "/" and you can use values in your spreadsheet, like I did with the A2, B2, and C2.<br /><br />Neat-o!<br /><br />A few hints: if you use text and you need spaces, include those spaces between the quotation marks. <br /><br />If you are building a date using concatenate, although it will look like a date, Excel thinks it's just a text field. Using format cells to change the format to a date still won't make Excel think it's a date. You'll have to change the formula to look like this:<br />=DATEVALUE(CONCATENATE(A2,"/",B2,"/",C2))<br />And, then change the format to make it look like a date. Now concatenate is a handy function, but Datevalue is even better. It's a guru function. Keep that one in your back pocket.Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com2tag:blogger.com,1999:blog-4165587777214115821.post-5102574855599805382007-05-18T22:19:00.001-04:002008-11-05T09:06:08.138-05:00VLOOKUPVLOOOKUP is definitely a guru function. Here it is...<br /><br />VLOOKUP means vertical lookup. It's a database type function, and allows you to combine data from two different sources. The sources can be different lists on the same worksheet, different lists in the same workbook, or two different files all together. The choice is yours. For the most part, the process is the same.<br /><br />Here's a scenario. Let's say you have a list that contains your classmates, their seat number, and their eye color. You have another list that contains your classmates' phone numbers. You want to combine the lists, but you've never heard of VLOOKUP. So, you try to sort them and then copy and paste the phone numbers manually. When you try this, you find out that your phone number list is shorter than your classmates information list. You start looking through trying to find the holes, but then your realize your list is 150 lines long. This will never work! VLOOKUP to the rescue.<br /><br />You will use the VLOOKUP to find matching values between the two spreadsheets by looking VERTICALLY up and down the second sheet. Once it finds a match, you will instruct it to look to the value on the 2nd column over (in this case) and return it to the first spreadsheet. Life has never been easier.<br /><br />Here's what the formula will loook like:<br />=VLOOKUP(A2,[Phone.xls]Sheet1!$A$2:$B$312,2,0)<br /><u>ANATOMY of this formula</u><span style="font-weight:bold;"><br />A2: </span>Class mate name.<br /><span style="font-weight:bold;">[Phone.xls]Sheet1!:</span> the second spreadsheet that contains the phone numbers<br /><span style="font-weight:bold;">$A$2:$B$312:</span> the "table array." This contains classmate name and their phone number<br /><span style="font-weight:bold;">0:</span> This just means FALSE. Your choices are 1 or 0 or TRUE or FALSE. Choose FALSE to have the VLOOKUP do an exact match. I've never found any value in choosing 1 or TRUE. (CAREFUL! If you leave this blank, it defaults to 1 or TRUE, and you will probably not get the results you expect.)<br /><br /><img src="http://farm1.static.flickr.com/196/503967368_27f92376fb.jpg?v=0"><br />This is called a formula bar. Click on the FX to bring up a wizard to build your VLOOKUP.<br /><br /><img src="http://farm1.static.flickr.com/221/503970060_35d8be476b.jpg?v=0"><br />The lookup value will be your classmates' name. Or, if you doing some other type of activity, it is the value that is common between your two lists.<br /><br />The table array is the 2nd list that has the data that you want to look up. This needs to include the whole list that may contain matching data. So, it goes from the very first name and phone number to the very last name and phone number. <br /><br />The Col_Index_Num (Column Index Number) references the column that has the value you want it return to the first list. In this case, it's 2. But, it can be any value from 1 to 256 (but, gosh, that would get complicated!)<br /><br />The last value in the box, for my money, is always 0. I don't know what to do with results that are a close, but not exact, match.<br /><br />Now, drag that formula down for the whole list. You've saved yourself a lot of work!<br /><br />That's it. I hope it's easier for you than I made it sound. I recommend practicing. And, if you ever find yourself sorting and manually matching things up, STOP! Use VLOOKUP. IT WORKS. IT WORKS EVERY TIME (although sometimes you have to massage it a bit.)<br /><br />A few tips and things to remember:<br />If both lists are in the same workbook, the wizard will not make your table array absolute with the dollar signs. Make sure that you use dollar signs. Otherwise, when you drag down the formula, you will be changing your table array each time you drag it down.<br /><br />VLOOKUP will always return the first value it find in the second list. If the second list contains two entries for a classmate with two different phone numbers, it will only return the first phone number it finds in the list. If you sorted the list differently, it would return the other number. <br /><br />If there is no matching value, it will return #N/A.<br /><br />If you're getting an error and you know there are matches between the two lists, try and change the formatting on both lists so that the lookups are formatted exactly the same.<br /><br />And, if that still doesn't work, imagine me standing over your shoulder. That's always done the trick for folks asking for Excel advice.<br /><br />Good Luck!Tricia Rothhttp://www.blogger.com/profile/16617232875055299624noreply@blogger.com8