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.
EXTENDED EXCELLENT EXCEL TIPS - November 2020
Jag började dela med mig Excel tips på min Linkedin sida tidigt i Oktober 2020, och i och med den 16:e så tog mina "Did you know" posts den form dom har idag. Idag kan du hitta mina tips på Linkedin, Facebook och Instagram. Alla dessa sidor kan du komma åt via menyn till höger i skärmen.
-
November 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.
​
​
​
​
​
-
November 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.
​
​
​
​
​
-
November 4, 2020 - Did 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.
​
​
​
​
​
​
-
November 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,"")
​
​
​
​
​
​
-
November 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
​
​
​
​
​
​
-
November 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.
​
​
​
​
​
​
-
November 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.
​
​
​
​
​
​
-
November 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.
​
​
​
​
​
​
-
November 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.
​
​
​
​
​
​
-
November 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"
​
​
​
​
​
​
-
November 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.
​
​
​
​
​
​
-
November 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.
​
​
​
​
​
​
-
November 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.
​
​
​
​
​
-
November 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.
​
​
​
​
​
​
-
November 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.
​
​
​
​
​
-
November 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.
​
​
​
​
​
-
November 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.
​
​
​
​
​
-
November 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.
​
​
​
​
​
-
November 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.
​
​
​
​
​
-
November 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 #dropdownmenu to 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",""))))))))
​
​
​
​
​
-
November 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.
​
​
​
​
​
​
