Search Results

I started posting Excel tips on my Linkedin page in early October 2020, and as of the 16th my "Did you know"post took the form it holds till this day. You can find my posts on Facebook, Instagram and Linkedin, all of which you can access via the links in the right hand column of this website. October 26, 2020 - Did you know that Excel can tell you the date October 23 , 2020 - Did you know that Excel can tell you the date October 22, 2020 - Did you know that Excel can tell you the date October 21, 2020 - Did you know that Excel can tell you the date October 20, 2020 - Did you know that Excel can tell you the date October 16, 2020 - Did you know .. I'm back with a little good to know "DID YOU KNOW" facts about excel. Today we're talking about Conditional Formatting. Did you know that there is a "Copy/Paste" function for conditional formatting. I love this HOW TO: First highlight the area where you just created a conditional format, click the "FORMAT" icon and then click and drag the range where you want to copy the formatting to. 201016 201020 201021 EXTENDED EXCELLENT EXCEL TIPS - October 2020 I started posting Excel tips on my Linkedin page in early October 2020, and as of the 16th my "Did you know"post took the form it holds till this day. You can find my posts on Facebook, Instagram and Linkedin, all of which you can access via the links in the right hand column of this website. October 16, 2020 - Did you know .. I'm back with a little good to know "DID YOU KNOW" facts about excel. Today we're talking about Conditional Formatting. Did you know that there is a "Copy/Paste" function for conditional formatting. I love this HOW TO: First highlight the area where you just created a conditional format, click the "FORMAT" icon and then click and drag the range where you want to copy the formatting to. October 20, 2020 - Did you know ... Did you know that Excel can tell you what day it is. There are some simple formulas that you can type into a cell and it will tell you the day, what time it is or all of the above. =NOW() - mm/dd/yy hh:mm =TODAY() - mm/dd/yy =HOUR(NOW()) - hh =YEAR(NOW()) -same as- =YEAR(TODAY()) - yyyy October 21, 2020 - Did you know ... Today's did you know is more of a "have you heard". So, have you heard about a brilliant thing called Flash Fill in Excel. It is another leap forward in Excel's AI. Lets say that you have gotten a list of names where first and last names are in separate columns and you want to bring them together. You simply fill in the first cell in the "Full name" column and then the magic happens. On a PC flash fill can be set to automatically happen, while on MAC you need to press "control+E" to invoke Flash Fill. You can see the three steps of the process in the attached images. ​ ​ ​ ​ ​ ​ October 22, 2020 - Did you know ... Did you know that there is more to excel than simply "=". You can make "greater than", "less than" and "not equal to" comparisons as well. I have attached an image that shows the simple formulas you can use and how it acts. I also added a simple Conditional Formatting to tell me when the formula is TRUE. Let me know if you want to know more about Conditional Formatting and I will talk about that tomorrow. ​ ​ ​ ​ ​ ​ October 23 , 2020 - Did you know ... I don't know if the rest of you love Conditional Formatting as much as me, but I will tell you one great use for it. Have you ever had to enter so much information into a document that you kind of lost track of what you enter in. And then you are not sure if you have entered something twice. Well there are some simple ways to find and eliminate those duplicates. Now, I know that Excel has a "Remove Duplicates", but that just removes it the duplicates without allowing you to analyze them. By Using Conditional Formatting you can highlight the duplicates so you can find them, analyze them and then change them. I once made a Bingo Sheet template and I had to be certain that I had no duplicates. Check the attached pictures for a bit of a step by step .. and if you have any questions feel free to contact me. ​ ​ ​ ​ ​ ​ October 26, 2020 - Did you know ... Today's little lesson will deal with a nice formula called DATEDIF. What this excellent gem can do for you is calculate time between dates. What you need is to have two cells with separate dates, then in a third cell you enter the following: =DATEDIF(CELL1, CELL2, "XX") CELL 1 - the starting date CELL 2 - the end date XX - y(years), m(months), ym(the months left after calculating years), d(days), md(the days left after calculating months) See attached images below for the formula in action. ​ ​ ​ ​ ​ October 27, 2020 - Did you know ... Today we will look at a situation where you want to just sum up your top or bottom results out of a range. As with everything EXCEL has the answer for this too. And that answer is combining SUMPRODUCT with either LARGE or SMALL. The generic formula would looks like this: =SUMPRODUCT(LARGE(range,{1,2,3})) for top 3 =SUMPRODUCT(SMALL(range,{1,2,3})) for bottom 3 As always I have attached some pictures that will show you the formula in action. Enjoy. ​ ​ ​ ​ ​ October 28, 2020 - Did you know ... Today we will have a look at something to expedite a task like grading results. In the example in the attached images we are looking at this from a school teacher's perspective. We are using an IF formula to quickly tell us if the student in question has PASSED or FAILED the class. The formula in the cells is as follows: =IF(cell>60, "PASS", "FAIL") We have chosen a result of 60% correct or better to be a passing grade. Come back tomorrow to see what we do if the class is not pass/fail. ​ ​ ​ ​ ​ October 30, 2020 - Did you know ... As promised yesterday, I am back today with another step within the IF formula world of EXCEL. Yesterday we created a formula that graded each student as PASS or FAIL based on a numerical test result. Today we will grade them on a scale from A to F. If you are like me and not using Office 365, then you will have to utilize a NESTED IF FORMULA. Here comes the formula I am using in the pictured example: =IF(C5<60,"F",IF(C5<70,"D",IF(C5<80,"C",IF(C5<90,"B",IF(C5>=90,"A",""))))) You can see the grade levels in the formula above and the results of it in the attached images. The nesting basically makes EXCEL check the cell value against criteria from left to right. If you have OFFICE 365 you can use the following formula as well: =IFS(C5<60,"F",C5<70,"D",C5<80,"C",C5<90,"B",C5>=90,"A") ​ ​ ​ ​ ​ 201022 201023 201026 201027 201028 201030

I started posting Excel tips on my Linkedin page in early October 2020, and as of the 16th my "Did you know"post took the form it holds till this day. You can find my posts on Facebook, Instagram and Linkedin, all of which you can access via the links in the right hand column of this website. October 26, 2020 - Did you know that Excel can tell you the date October 23 , 2020 - Did you know that Excel can tell you the date October 22, 2020 - Did you know that Excel can tell you the date October 21, 2020 - Did you know that Excel can tell you the date October 20, 2020 - Did you know that Excel can tell you the date October 16, 2020 - Did you know .. I'm back with a little good to know "DID YOU KNOW" facts about excel. Today we're talking about Conditional Formatting. Did you know that there is a "Copy/Paste" function for conditional formatting. I love this HOW TO: First highlight the area where you just created a conditional format, click the "FORMAT" icon and then click and drag the range where you want to copy the formatting to. 201102 201103 201104 EXTENDED EXCELLENT EXCEL TIPS - November 2020 I started posting Excel tips on my Linkedin page in early October 2020, and as of the 16th my "Did you know"post took the form it holds till this day. You can find my posts on Facebook, Instagram and Linkedin, all of which you can access via the links in the right hand column of this website. N ovember 2 , 2020 - Did you know ... Did you know that CONCATENATE is a word in the world? It is derived from the Latin word directly concatenare, which in turn is formed from con- (meaning "together") and catena( meaning "chain"). But that is not the best thing about it, the best thing is what it does as an EXCEL formula. It can be used to smoothly combine a list of first and last names (or any text strings from cells) in a new column. As always there are pictures attached showing how it is used. The formula being used is this: =CONCATENATE(cell1&" "&cell2) cell 1 = First names cell 2 = Last names " " = quotes with a space to create a space between the first and last name. ​ ​ ​ ​ ​ N ovember 3 , 2020 - Did you know ... Did you know that there is even more we can do with CONCATENATE. Working off of the same list from yesterday we are now looking to create an email list for all the employees at our fictitious company. In this company everyones email address is : firstname.lastname@department.company.com So, by using CONCATENATE again we can achieve this by using the formula thusly: =CONCATENATE(A33&"."&B33&"@"&D33&".company.com") Look at the pictures below to see how this works. ​ ​ ​ ​ ​ N ovember 4 , 2020 - id you know ... Did you know that when countries print/create regular license plates there are certain letter combinations that are not allowed to appear. Since the License plates are randomly generated there needs to be a way to check to make sure that nothing inappropriate makes it onto a plate. In todays example I am using a 4 formulas, nested to help me make sure that a range of text strings never appear anywhere in my cell. Here is my formula: =IF(SUMPRODUCT(--ISNUMBER(SEARCH($A$67:$A$70,C67)))>0,"REJECT","APPROVE") Boy that is a doozy. The IF formula is what helps me return REJECT vs APPROVE in my cell. SEARCH does the actual looking while ISNUMBER coupled with SUMPRODUCT (and the "--", double negative) makes everything into an array that allows me look for every word in the BAD WORDS list. Pictures, as always, attached below. ​ ​ ​ ​ ​ ​ N ovember 5 , 2020 - Did you know ... Well hello there everyone. We are going to revisit some pictures you've seen before (sort of) and address another common problem. Looking for blank cells and/or getting an accurate count on the number of blank cells. Seen from the perspective of a teacher grading assignments this can be a way to see who has not turned in their assignment or that they have missed to enter their grades. I am going to show you two ways to do this. CONDITIONAL FORMATTING to highlight the blank cells, which would work on its own for a smaller sample set. Then coupled with a COUNTIF formula to count the number of blank cells which would be helpful when dealing with larger sample sets. The CONDITIONAL FORMATTING you can see the steps in the pictures and for COUNTIF, the formula is: =COUNTIF(range,"") ​ ​ ​ ​ ​ ​ N ovember 6 , 2020 - Did you know ... Have you ever heard of a little EXCEL gem called SUBTOTALS. SUBTOTALs will essentially do what it sounds like it does. It will allow you to have a SUBTOTAL rows in the middle of a column while still having a total at the bottom that will not count the SUBTOTALS. You can use this formula when calculating SUM, AVERAGE, MAX or MIN value to name a few. Here is the formula in basic form: =SUBTOTAL(1,C2:C4) 1 - is the FUNCTION NUMBER (see first picture) C2:C4 - is the first range to be SUBTOTALed ​ ​ ​ ​ ​ ​ N ovember 9 , 2020 - Did you know ... This week we will begin with the DID YOU KNOW with a VBA code. Something I don't use too often, but that can be super useful. There are plenty of cases where we may not want people to be able to see what formulas we have in certain cells. By using a simple little program, you can password protect any numer of cells from showing their imbedded formulas. The results will still be visible, but no one will ever be able to see how you did it. STEP 1: open VIRTUAL BASIC on the DEVELOPER tab and add a module to the entire worksheet. Enter the programming language shown in one of the pictures. STEP 2: select the cells you want to hide the formulas of. Select MACRO from the DEVELOPER tab and select the MACRO and run it. The formulas are now hidden. STEP 3: to unhide the formulas, go to the REVIEW tab and select PROTECT SHEET, enter the password you entered in the MACRO (pass in this example) and the formulas can once again be seen. As always, pictures that follow will show you everything. ​ ​ ​ ​ ​ ​ N ovember 10 , 2020 - Did you know ... Have you ever decided to make a graph only to get frustrated by the automated choice of color that EXCEL decides to give you. Never does this become more poignant than when you are dealing with category titles that are colors. You might just go to chart design and choose from the CHOOSE COLOR button, but all that lets you do is pick from some pre-programmed color schemes. And you want to color every stack personally. For the longest time I thought this required some serious work-arounds. But that is far from the truth. The truth is that coloring your stacks (or pie slices) is as easy as filling in a cell. If you CLICK on one stack, you will select all the stacks (see the blue dots in the corners of the stack in the pictures). When you then click on another stack you will have selected only that one (again, see small blue dots). Then all you do is select the CELL COLOR FILL button on the HOME tab and select your color. Repeat as many times as needed to get the result you want. As always, pictures attached will show you each step I have written about. If you have any other questions about this, respond here and I will respond in kind. ​ ​ ​ ​ ​ ​ N ovember 11 , 2020 - Did you know ... Excel has, among so many other great things, something called a SLICER. Now, what are SLICERS I hear you asking .. or at least I assume some of you may be asking this. So what do they do, well ... simply put they are a quicker and more visual way to work with filters in a TABLE. STEP 1: Make your RANGE into a TABLE from the HOME tab. STEP 2: In the TABLE tab you can select INSERT SLICER. STEP 3: Select the Columns you want SLICERS for. STEP 4: Resize and place your SLICERS where you want them. STEP 5: Click your SLICER and see what happens. Have fun with it. Look over the attached pictures, and if you have any questions about this function respond to the post and I will answer. ​ ​ ​ ​ ​ ​ N ovember 12 , 2020 - Did you know ... Here comes a simple, but useful EXCEL factoid. As many of you know, when working with EXCEL you might see things like #NUM! or #N/A! , and that means that something is not right. Your sheet will not work and you need to figure out why. Well, one thing that will help you is a little overview of the different ERROR MESSAGES you will see and what they mean. This way you will know where to begin looking. ​ ​ ​ ​ ​ ​ N ovember 13 , 2020 - Did you know ... Have you heard of a nifty little function called CELL. I know that it sounds rather generic, but what it can do for you is anything but. For the purpose of this little demonstration we will be using 3 out of the 13 versions of the CELL formula. And with it we will track any changes made to your documents. With the following formulas entered into cells (any cells really): =cell("address") =cell("contents") =cell("filename") Excel will tell you which cell was changed, what the new content of the cell is, and the path to the sheet where the change took place. For WINDOWS (PC) users you can enter these cells into a WATCH WINDOW (located in the FORMULA tab) and that will allow you to have a small floating window that will allow you to keep an eye on your changes. For MAC users we would have to create a separate EXCEL where we have these formulas entered and adjust the size of the window so it can be "free floating" ​ ​ ​ ​ ​ ​ N ovember 16 , 2020 - Did you know ... Welcome to Monday everyone, time to start a new week with some new EXCEL tips. This whole week I will be talking about DATA VALIDATION; how to use, when to use it and why to use it. In today's first example we will look at a section of a table of information where we have a column that will only ever consist of one or four words. We use DATA VALIDATION here in order for us not to have to write in our option every time. Now, I know that once you have entered every option at least once EXCEL will automatically create a sort of drop menu for you to choose from, but with DATA VALIDATION you will be able to have gaps in the table and still get at your options. DATA VALIDATION is basically sort of like CONDITIONAL FORMATTING in that you must FORMAT every cell where you want to have access to your options. STEP 1: Choose DATA VALIDATION from the DATA tab STEP 2: Choose LIST and enter your options/variables, separated by commas. STEP 3: Use the "DOWN ARROW" now visible in the table to enter the correct option for your row. ​ ​ ​ ​ ​ ​ N ovember 17 , 2020 - Did you now ... Hello, hello, hello. Day 2 of DATA VALIDATION is upon us. And here comes a small be very useful reason to use DATA VALIDATION in large/complex tables or ranges. If you visited my comment yesterday, you should recognize the tables in the pictures that I am using today. And what I will show with my pictures is the simple fact that DATA VALIDATION makes it impossible to enter a something into the column you have formatted that you have not preset to be allowed to be entered. In this example we are attempting to enter "book" into the column, even though it is not one of our accepted options. This factor is particularly useful when you have formulas set up to use that particular column. Enjoy this fact and see you tomorrow for even more DATA VALIDATION craziness. ​ ​ ​ ​ ​ ​ N ovember 18 , 2020 - Did you know ... Moving on with our week of DATA VALIDATION, today we will discuss how we create a larger sample range for our potential drop-down menu. This is something that you would do if you had several different variables. And this range of variables can be placed on a separate SHEET, thereby keeping it out of view for everyday use. The difference here is that instead of entering the different variables you want in you menu, you enter the range where they are written down. Just like I showed you yesterday, you are still guarded from entering a variable that should not be in your table. Come back tomorrow to see how you make a dynamic range where you need to constantly add or remove variables. ​ ​ ​ ​ ​ N ovember 19 , 2020 - Did you know ... Yesterday I wrote about how you can use an entire range to a drop down menu via DATA VALIDATION. Well, what do you do if you are constantly adding to or removing things from that RANGE. You would want your drop down menu to automatically update itself without you having to go in and fiddling with it. Today we will discuss one of the ways you can do that. What you want to do is change your range to a TABLE, using the FORMAT AS TABLE function on the HOME tab in EXCEL. Then you will want to NAME that table via INSERT (menu), NAME and DEFINE NAME. Enter that NAME into the DATA VALIDATION range. With that done, as you add new items to the end of your range, they will automatically appear in your drop down menu the next time you open it. Come back tomorrow to see a way to make a dynamic DATA VALIDATION drop down menu without having to create a TABLE. ​ ​ ​ ​ ​ ​ N ovember 20 , 2020 - Did you know ... Here we are, day 5 of DATA VALIDATION week. And in my opinion I have saved one of the best for last. Here is the formula that you will want to know if you are working with lists/ranges that are prone to changing often. Using this formula: =OFFSET(first cell of the table,1,0,COUNTA(range of the table)) As your DATA VALIDATION source will allow you to add or take away items from the RANGE and it will automatically resize and adjust your drop down menu. Here comes a step by step of the pictures below. STEP 1: Here is our table with a prepared cell for our DROP DOWN MENU. STEP 2: Open DATA VALIDATION under the DATA tab and choose LIST. STEP 3: Enter the formula. STEP 4: Check to make sure your DROP DOWN MENU contains all our factors. STEP 5: We add two new words (cartoon, flyer) and check to see that our update works. If you have any questions about this just write them in the comment and I will get back to you. Come back on Monday for a new themed week. ​ ​ ​ ​ ​ N ovember 23 , 2020 - Did you know ... If, if, if, if .. look forward to a whole week of IF. The amazing range of uses for IF is nearly limitless, and this week I will show you 5 of those uses. They might not be the flashiest things out there, but they are super useful. In this first example we will be working with a range of material that we are selling (see picture). A few basic cell arithmetic formulas are being used to figure out the values in some of the columns, but in column F (Discount or not) we need our first IF function to see if we indeed will be offering a discount and what that discount will be. In that column we are using this formula: =IF(no. sold>=100, cost * 0.1, "NO DISCOUNT") =IF(logical test, IF TRUE, IF FALSE) What the formula is saying is as follows: If the number of units sold is GREATER THAN or EQUAL to 100 take the COST and MULTIPLY it by 0.1 (10%), and if that units sold is less than 100 display NO DISCOUNT. As you can see in the pictures, there are plenty of lovely error messages as well. Come back tomorrow and I will show you how we remove them. ​ ​ ​ ​ ​ N ovember 24 , 2020 - Did you know ... A merry tuesday to you all. Yesterday we left off with a table that a fair number of #VALUE! errors. Why did this happen and what are we going to do to fix it .. simple. More IF functions. Today we will be implementing IFERROR to deal with our error. You can see where we left off in the first picture. The reason we are getting a #VALUE! error is because there is no number in certain places in column F (the places where no discount takes place) Now we could have simply let yesterdays formula return a 0.00 in that column, but that is not as much fun. So now we have a simple subtraction formula that involves text, and the returns an error. Here is the solution. =IFERROR(COST-DISCOUNT;COST) =IFERROR(VALUE; VALUE IF ERROR) This does a lovely job of removing all the #VALUE! errors, but we get a few new #DIV/0! errors in column H. Come back tomorrow for the solution to that beauty. ​ ​ ​ ​ ​ N ovember 25 , 2020 - Did you know ... Welcome back for day three, Wednesday, of our IF function week. Yesterday we left our range with a column that contained a few #DIV/0! error messages, and I said that today we will remove them. Now, we do have one other issue that we will need to address with our choice of formula, and that is the fact that we don't want anything to be calculated here if there is no discount. We only want a figure to be displayed when there is a discount to be counted. If we were simply going to remove the #DIV/0!, then "=IFERROR" would be sufficient. But here we are nesting the IFERROR inside a simple IF to eliminate both issues. Our formula looks like this: =IF(discount?="no discount", "NO DISCOUNT", IFERROR(end price/unit price, unit price)) So basically if we are not giving a discount then we we will display no discount, but if we are discounting the price then we want to be able to see what our new unit price is, and we are guarding against errors by using the IFERROR function there. Tomorrow we will move on to something different. See you then. ​ ​ ​ ​ ​ N ovember 26 , 2020 - Did you know ... Welcome to Thursday, and another great day to think about IF functions. Yesterday we left our range with no errors and looking sharp. Well .. I can't leave it like that. Today I decided to add three new columns. One to determine whether or not the material bought should be delivered or picked up, one to determine whether or not I need to adda surcharge for delivery to the total price and the last one simply giving me the final price due for the purchase. Now, on the surface this would be a simple case of throwing in an IF function asking whether or not column I's cells include the word "delivery" or "pickup". But this can create a problem if the person entering the data accidentally enters a space in the cell. So what to do ... Nesting the TRIM function in the IF function cuts out the spaces and leaves only the actual text in the cell alone, saving us $75 per possible mistake. Here is the formula in the cell so you can copy/paste it for your own use: =IF(TRIM(I2)="delivery",75,0) Come back tomorrow for the weeks final IF. ​ ​ ​ ​ ​ N ovember 27 , 2020 - Did you know ... And here we are now. We have made it to the end of another week and I thought what else to do on a Friday then celebrate with the Cure and an #IF function. So how does that work? I have set up a range that includes the 7 days of the week and 3 rows for the 3 different verses of the song. Then I set up a #datavalidation #dropdownmenuto allow me to select the different days of the week along with a lovely little, albeit long, IF statement with some added spice to tell me what happens on the given day I select. I will share the formula in a comment as it is too long to include here. This might not seem like the most super useful thing, but it is a good example of yet another way to use IF in excel. Feel free to comment to question, and have a great weekend. ​ =IF(C10="Monday","On a Monday I can be "&RIGHT(B3,4)&" or be "&RIGHT(B4,5)&" or "&B5, IF(C10="Tuesday","On a Tuesday I can be "&RIGHT(C3,4)&" or have a "&C4&" or "&C5, IF(C10="Wednesday","On a Wednesday I can be grey "&RIGHT(D3,3)&" or have a "&D4&" or "&D5, IF(C10="Thursday","On a Thursday "&E3&" or I will be "&E4&" or I can "&E5, IF(C10="Friday","On a Friday "&F3&" or "&F4&" or "&F5, IF(C10="saturday","On a Saturday I will have to "&G3&" or "&G4&" or "&G5, IF(C10="sunday","And of course a Sunday "&H3&" or it "&H4&" or it "&H5, IF(C10="", "PICK A DAY","")))))))) ​ ​ ​ ​ ​ N ovember 30 , 2020 - Did you know ... That in the US Thanksgiving was celebrated on the Thursday preceding this weekend. Meanwhile Canada celebrated Thanksgiving much earlier in the previous month. That is because Canada celebrates Thanksgiving on the 2:nd Monday of October and the US on the 4:th Thursday of November. Now, what does that have to do with Excel. Well, instead of mucking about with a calendar when trying to find the date for the upcoming Thanksgivings. We can simply let EXCEL do all the heavy lifting. For Canadians: =DATE(A2,10,15)-WEEKDAY(DATE(A2,10,6)) For Americans: =DATE(A2,11,29)-WEEKDAY(DATE(A2,11,24)) Where A2 is the a cell in a column that denotes the year that we are looking into. Now with Thanksgiving over with, and tomorrow being the 1st of December it is time to get into the Holiday spirit. Starting tomorrow I will start a 24 day countdown to Christmas with a new fun fact everyday, including weekends. ​ ​ ​ ​ ​ 201105 201106 201109 201110 201111 201112 201113 201116 201117 201118 201119 201120 201123 201124 201125 201126 201127 201130

I started posting Excel tips on my Linkedin page in early October 2020, and as of the 16th my "Did you know"post took the form it holds till this day. You can find my posts on Facebook, Instagram and Linkedin, all of which you can access via the links in the right hand column of this website. October 26, 2020 - Did you know that Excel can tell you the date October 23 , 2020 - Did you know that Excel can tell you the date October 22, 2020 - Did you know that Excel can tell you the date October 21, 2020 - Did you know that Excel can tell you the date October 20, 2020 - Did you know that Excel can tell you the date October 16, 2020 - Did you know .. I'm back with a little good to know "DID YOU KNOW" facts about excel. Today we're talking about Conditional Formatting. Did you know that there is a "Copy/Paste" function for conditional formatting. I love this HOW TO: First highlight the area where you just created a conditional format, click the "FORMAT" icon and then click and drag the range where you want to copy the formatting to. 210112 210113 210114 EXTENDED EXCELLENT EXCEL TIPS - January 2021 I started posting Excel tips on my Linkedin page in early October 2020, and as of the 16th my "Did you know"post took the form it holds till this day. You can find my posts on Facebook, Instagram and Linkedin, all of which you can access via the links in the right hand column of this website. J anuary 12 , 2021 - Did you know ... Welcome to a new great year of amazing #facts, #tipsand #tricks that will simply make you fall in love with #excel. Well maybe not love .. but at least learn to appreciate how great it Excel is and what an amazing tool it can be both at home and at work. Come here every weekday for the latest installment, starting tomorrow. #didyouknow #excellenthelp #goodtoknow#microsoft #tasuohoriuchi #excelartist #exceltips ps I've included one of my favorite works by renowned Excel artis Tatsuo Horiuchi. This piece, like all his work, is done using only the tools available in excel. ​ ​ ​ ​ ​ J anuary 13 , 2021 - Did you know ... Did you know that this is my first #didyouknow of #2021. That is, of course, not the actual #exceltip I have for you today. To start the year I thought I would go back to basics and show some simple moves in #excel that can speed up document creation. Today I will show you how to automatically update the width of multiple columns to fit all the information in in every cell. This technique adjusts each column width to the longest thread in any cell in that column. We start with a table where everything fits, but we want to adjust the font size of our title cells and this makes it so our threads no longer fit in their cells. What we need to do then is this: 1. Select all the columns by #clickinganddraging from the first to last column. 2. Move the mouse cursor to one of the lines that separate the columns (get between the letters) 3. Once the mouse cursor changes to the icon shown in one of the pictures, #doubleclick and the width's will be adjusted. ​ ​ ​ ​ ​ ​ J anuary 14 , 2021 - Did you know ... Hello one and all. Yesterday I left you after having showed you how to automatically widen a group of columns to accommodate the longest thread in any cell of that column. But that also resulted in many columns of varying widths. So, did you know that you can use a similar technique to adjust a group of columns to the exact same width. Much like yesterday we will select all the columns we wish to effect, place the cursor on the line between to columns, click and drag to the desired width and when you release the mouse button all your columns will be that width. This is a great way to make sure that your table/spreadsheet looks uniform and professional. ​ ​ ​ ​ ​ J anuary 15 , 2021 - Did you know ... Did you know that both of the last two days tips can also be used for row height. Well, if you didn't ... #nowyouknow. The process is basically the same, aside from the small change in that you highlight the rows you want to change. Then simply place the mouse cursor on the line between two row numbers and either #doubleclick to have #Excel automatically alter the height to adjust to your content or #clickanddrag to adjust the height of all the rows to be the same. ​ ​ ​ ​ ​ J anuary 18 , 2021 - Did you know ... So, there are moments when you want to enter the same formula in multiple cells. And there are a few different ways to do that. #CopyPaste You can enter the formula into one cell, copy the cell, select all other cells and then paste. #BUT If your formula involves a separate cell value you need to make sure that you have made it absolute. Otherwise the paste will move the chosen cell. So what do you? You select all the cells that you want the formula in first. Then you begin entering the formula into the formula bar. And this is where the magic happens. When you are done writing the formula you press Control+Enter (Command+Enter also works on #MAC). And with that simple little move, BAM ... your formula is now in every cell. ​ ​ ​ ​ ​ J anuary 19 , 2021 - Did you know ... Yesterday I made a reference to something called an #absolutereference#absolutecellreferencee). Therefore, today I will give a quick tutorial regarding what that is. Using Absolute Cell References is something that is important to know when you copy/ #clickanddrag formulas into multiple cells. There are three versions for an absolute reference in #excel. $A$2 - The cell remains the same whenever the cell/formula is copied over into other cells. A$2 - The row will not change. Meaning that when formulas are copied across a row, the column will change (A-B-C ...) But when copying down a column, the row value will not change. $A2 - Here the column will not change. Basically the opposite of the previous explanation. ​ ​ ​ ​ ​ J anuary 20 , 2021 - Did you know ... Something that is very common when you are working on a large #exceldocument with many formulas is that you kind of forget what formulas you have in any given cell. Did you know that there is a easy function that allow you to view all your formulas without going through every cell and without risking changing the content of any cell. Simply go to the #Formulas tab and click the #showformulas button. What this button does is change the view on every cell to show the formula being used there. Then you have a great overview of your document. Don't worry about the cell dimensions changing when you press the button, they will revert when you reverse the procedure. To reverse the procedure simply click the same button again. ​ ​ ​ ​ ​ J anuary 21 , 2021 - Did you know ... Did you know that there is a lot of value in the aesthetics of a document as well. It can help both with clarity and appreciation for those interacting with it. One thing that can do a lot for your document is removing the #gridlines and the #headings (the row and column markings). Simply go to the #view tab and #uncheck the gridlines and headings box and you can see the effect that it has on your document. The process is of course reversible for when you need to adjust the document. ​ ​ ​ ​ ​ J anuary 22 , 2021 - Did you know ... Did you know that there is an easy way to freeze your top row, leftmost column or number of cells and columns. In todays example I will show you how to #freeze the top row #freezingg the leftmost column works the same way). When you have your range set up (normally with the top row being your header) all you have to do is go into the #View tab and select #freezetoprow. Now the top row will stay with you regardless of how far down you scroll. Freezing the leftmost column has the same effect. You can scroll sideways as long as you want, and the leftmost column stays in place. Come back tomorrow for the next lesson in freezing cells, rows or columns. ​ ​ ​ ​ ​ J anuary 25 , 2021 - Did you know ... Did you know that there is a way to #freeze more than just the top row or the leftmost column. #Excel actually allows you to freeze as many rows or columns as you wish, though always starting with row 1 and/or column A. All you need to do is select the cell that is one row down from the last row you want to freeze and one column to the right of the final column you want to freeze. Then go to the view tab and select #freezepanes. Once you've #clickedthe icon you can see that the text changes to #unfreezepanes. In the example shown I have selected cell F2. That means that row 1 will be frozen along with columns A-E. That means that the columns with text will never be able to scroll out of screen and I will keep my title header in place at the top of the document all the time. ​ ​ ​ ​ ​ J anuary 26 , 2021 - Did you know ... Did you know that you don't actually have to freeze your top row if you have your range #formatted as a #table. It's true. #Excel, will actually place your table headers in the column title row when you scroll it out of the screen. This is one of those tips where you don't actually have to do anything, this is more of an FYI. You can see in the second picture that I have scrolled down and I still have my headers on screen and they are functional (meaning I can access the #filtering functions). ​ ​ ​ ​ ​ J anuary 27 , 2021 - Did you know ... Did you know that there are tons of great features for organizing a table within the #Excel #filter function that is imbedded in each #columnheader. 1. You can organize alphabetically, both forwards and backwards. 2. Cells that equal certain values, or does not equal that value. 3. Cells that begin with, don't begin with end with or don't end with a certain value. 4. Cells that contain something or don't contain something. It is a great way to quickly organize a lot of information to allow you to quicker get to your end goal with a spreadsheet. And of course, when you filter one column .. the entire row that goes with that cell's column with change right along with it. #excellenthelp #exceltips #didyouknow ​ ​ ​ ​ ​ J anuary 28 , 2021 - Did you know ... Did you know that you can copy any part of your #Excel document and use it in another #Microsoft document. That's right, not just Excel .. but right into a #powerpoint presentation that you are working on. Simply #highlight the cells that you want to take a snapshot of, and then on the HOME tab there is a button called copy. But it also has a down arrow that activates a drop menu that allows you to choose "Copy as Picture". Once you have it copied it is in your #clipboard and then you are free to paste it anywhere you want it. ​ ​ ​ ​ ​ J anuary 29 , 2021 - Did you know ... Did you know that there are several different #pastespecial. One very useful version is copying only #values from a range of cells that includes formulas. Sometimes you just want to lift out a snapshot of your values in a range and use it elsewhere. That is where PasteSpecial comes in super handy. All you need to do is highlight the cells that you want to #copy, copy the way you normally do .. but then. When it is time to #paste, right click and select PasteSpecial. You will be given a number of selections, in this case we are choosing values. ​ ​ ​ ​ 210115 210118 210119 210120 210121 210122 210125 210126 210127 210128 210129

Top of Page October 2020 November 2020 December 2020 January 2021 February 2021 EXCELLENT EXCEL TIPS I started posting Excel tips on my Linkedin page in early October 2020, and as of the 16th my "Did you know"post took the form it holds till this day. You can find my posts on Facebook, Instagram and Linkedin, all of which you can access via the links in the right hand column of this website. O ctober 16, 2020 - Did you know that there is a Copy/Paste function for Conditional Formatting October 20, 2020 - Did you know that Excel can tell you the date O ctober 21, 2020 - Did you know that Excel has a function called Flash Fill O ctober 22, 2020 - Did you know that Excel has more than just "equal to" (=) O ctober 23 , 2020 - Did you know that you can use Conditional Formatting to find duplicates O ctober 26, 2020 - Did you know that Excel has a Datedif formula O ctober 27, 2020 - Did you know that Excel can sum you top/bottom three values from a range O ctober 28, 2020 - Did you know that Excel can grade values by using an IF formula O ctober 30, 2020 - Did you know that Excel can even allow you to nest IF within an IF formula N ovember 2, 2020 - Did you know that Excel can splice together text with Concatenate N ovember 3, 2020 - Did you know that Excel can splice together text and symbols with Concatenate N ovember 4, 2020 - Did you know that Excel lets you analyze text based on a sample set N ovember 5, 2020 - Did you know that Excel Conditional Formatting allows you to color empty cells N ovember 6, 2020 - Did you know that Excel has a great tool called Subtotals N ovember 9, 2020 - Did you know that Excel has a VBA code that lets you hide your formulas N ovember 10, 2020 - Did you know that Excel will let you color your charts however you want N ovember 11, 2020 - Did you know that Excel has an amazing tool called Slicers N ovember 12, 2020 - Did you know that Excel has 7 error messages, here they are N ovember 13, 2020 - Did you know that Excel has a formula called Cell that is super useful N ovember 16, 2020 - Did you know that this is day 1 of 5 with Excel Data Validation N ovember 17, 2020 - Did you know that this is day 2 of 5 with Excel Data Validation N ov ember 18, 2020 - Did you know that this is day 3 of 5 with Excel Data Validation N ovember 19, 2020 - Did you know that this is day 4 of 5 with Excel Data Validation N ovember 20, 2020 - Did you know that this is day 5 of 5 with Excel Data Validation N ovember 23, 2020 - Did you know that this is day 1 of 5 with the Excel IF formula N ovember 24, 2020 - Did you know that this is day 2 of 5 with the Excel IF form ula N ovember 25, 2020 - Did you know that this is day 3 of 5 with the Excel IF form ula N ovember 26, 2020 - Did you know that this is day 4 of 5 with the Excel IF form ula N ovember 27, 2020 - Did you know that this is day 5 of 5 with the Excel IF form ula N ovember 30, 2020 - Did you know that Excel allows you to identify which days are holidays D ecember 1, 2020 - Did you know that this is day 1 of this years Excel Advent calendar D ecember 2, 2020 - Did you know that this is day 2 of this years Excel Advent calendar D ecember 3, 2020 - Did you know that this is day 3 of this years Excel Advent calendar D ecember 4, 2020 - Did you know that this is day 4 of this years Excel Advent calendar D ecember 5, 2020 - Did you know that this is day 5 of this years Excel Advent calendar D ecember 6, 2020 - Did you know that this is day 6 of this years Excel Advent calendar D ecember 7, 2020 - Did you know that this is day 7 of this years Excel Advent calendar D ecember 8, 2020 - Did you know that this is day 8 of this years Excel Advent calendar D ecember 9, 2020 - Did you know that this is day 9 of this years Excel Advent calendar D ecember 10, 2020 - Did you know that this is day 10 of this years Excel Advent calendar D ecember 11, 2020 - Did you know that this is day 11 of this years Excel Advent calendar D ecember 12, 2020 - Did you know that this is day 12 of this years Excel Advent calendar D ecember 13, 2020 - Did you know that this is day 13 of this years Excel Advent calendar D ecember 14, 2020 - Did you know that this is day 14 of this years Excel Advent calendar D ecember 15, 2020 - Did you know that this is day 15 of this years Excel Advent calendar D ecember 16, 2020 - Did you know that this is day 16 of this years Excel Advent calendar D ecember 17, 2020 - Did you know that this is day 17 of this years Excel Advent calendar D ecember 18, 2020 - Did you know that this is day 18 of this years Excel Advent calendar D ecember 19, 2020 - Did you know that this is day 19 of this years Excel Advent calendar D ecember 20, 2020 - Did you know that this is day 20 of this years Excel Advent calendar D ecember 21, 2020 - Did you know that this is day 21 of this years Excel Advent calendar D ecember 22, 2020 - Did you know that this is day 22 of this years Excel Advent calendar D ecember 23, 2020 - Did you know that this is day 23 of this years Excel Advent calendar J anuary 12, 2021 - Did you know that there is an Excel artist named Tatsuo Horiuchi J anuary 13, 2021 - Did you know that Excel allows you to autosize multiple columns J anuary 14, 2021 - Did you know that Excel allows to size multiple columns to the same width J anuary 15, 2021 - Did you know that Excel rows can be manipulated like their columns J anuary 18, 2021 - Did you know that Excel allows you to enter the same formula in multiple cells J anuary 19, 2021 - Did you know that Excel has something called Absolute References J anuary 20, 2021 - Did you know that Excel lets you show your formulas in a sheet J anuary 21, 2021 - Did you know that Excel and aesthetics go hand in hand J anuary 22, 2021 - Did you know that Excel allows you to freeze your top row J anuary 25, 2021 - Did you know that Excel allows you to freeze even more than your top row J anuary 26, 2021 - Did you know that Excel will keep headers at the top of your page in a Table J anuary 27, 2021 - Did you know that Excel has several great functions within the table header filter J anuary 28, 2021 - Did you know that Excel allows you to copy a screen print right to a Microsoft Clipboard J anuary 29, 2021 - Did you know that Excel has several special paste functions F ebruary 1, 2021 - Did you know that Excel has SPARKLINES F ebruary 2, 2021 - Did you know that Excel easily can Remove Duplicates F ebruary 3, 2021 - Did you know that Excel can easily remove blank rows for you F ebruary 4, 2021 - Did you know that Excel F ebruary 5, 2021 - Did you know that Excel October 2020 November 2020 December 2020 January 2021 February 2021

We are here to help! Enter Your Name Enter Your Email Enter Your Subject Subject Phone Enter Your Address Type Your Message Here Submit Thanks for submitting! 500 Terry Francois St. San Francisco, CA 94158 Tel. 123-456-7890 Email. info@mysite.com