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 - Oktober 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.
-
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")
​
​
​
​
​
