Excel to English: How to Think in IF Statements

[EXCERPT ONLY, CLICK ABOVE HEADER FOR FULL POST WITH IMAGES]

Ever feel like learning Microsoft Excel is like learning another language?  Don't beat yourself up, it kind of is!  "Keeping track of data in rows and columns is hard enough, and now I have to learn a whole bunch of formulas?"

Mercifully, there is a way to learn Excel that keeps those formulas memorable and (for the most part) in English!  Let's take a very basic example of a conditional formula, an IF function.

Before we even start looking up formulas, watching YouTube videos, and getting stressed over the coding, let's take a breath and articulate what we want to do in plain English.

"If the number to the left is greater than zero, then return the word 'POSITIVE.'  Otherwise, return the word 'NOT POSITIVE.'"

See what we did there?  By putting the objective in plain English, we've identified three key parts of our function:  (1) the condition, (2) the result if the condition is TRUE, and (3) the result if the condition is FALSE.

Excel uses the same convention!

=IF([condition],[do this if TRUE],[do this if FALSE])

Have additional questions about conditionals or other Excel elements?  Reach out today to chat live with someone from our team, email us, or call us by phone!

The Truth about TRUE and FALSE in Excel

[EXCERPT ONLY, CLICK ABOVE HEADER FOR FULL POST WITH IMAGES]

This blog will refer to IF formulas quite a bit, and for good reason--they're important for doing almost anything interesting in Excel!  But it's first important to realize that the whole world of conditional statements relies on a foundational concept that has traditionally been the province of philosophers:  the concept of TRUE and FALSE.

Microsoft Excel doesn't treat a cell containing the words "TRUE" or "FALSE" as regular ol' text strings.  These are values--if we want to get technical about it, they are called Boolean values.  They have special properties that we can use in mathematical formulas (TRUE has a number value of 1 and FALSE has a number value of 0), logical functions (AND, OR), and conditional functions (IF).

Let's look at a few examples.  Expressions that evaluate to TRUE include just typing the word "TRUE" (not case sensitive), typing a true equality (1=1) after an equals sign, typing a true inequality (5>4) after an equals sign, or evaluating a true logical function (AND(1=1,2=2)).  Each of these has a counterpart expression that evaluates as FALSE.

Need additional help with TRUE, FALSE, conditional formulas, or other Excel elements?  Reach out today via live chat, email, or phone!

 

 

Time Puzzles: Building Dates From Pieces In Excel

[EXCERPT ONLY, CLICK ABOVE HEADER FOR FULL POST WITH IMAGES]

We've been learning about dates in Excel for nearly two weeks now, so let's bring it all together with a formula that builds dates from year, month, and day pieces.  I'll give you three guesses what our friends at Microsoft decided to call this function...

Surprise, surprise... the DATE() function!  Knowing how it works won't score you more dates necessarily, but it will allow you to take someone's birth date and turn it into a current-year birthday!

Let's take this in pieces.  First we use the MONTH() function to get the month on its own.

Next, we do the same thing with the DAY() function.

Now we can use DATE() and feed it the current year (2016) along with the precalculated month and day to get our current-year birthday!

Need additional help with DATE(), MONTH(), DAY(), or anything else having to do with dates in Excel?  Reach out today via live chat, email, or call us at 877.851.0132!

Sieze The Moment: TODAY() Formula In Excel

[EXCERPT ONLY, CLICK ABOVE HEADER FOR FULL POST WITH IMAGES]

Suppose we have a bunch of days and we want Excel to dynamically calculate their difference from today's date, whatever that might be.  Obviously, "today" changes every day, so is there a way Excel can keep the calculation current?

Meet the TODAY() function!

If we take a random set of dates in one column,

and then provide the TODAY() function in another column,

the third column can subtract the today's date from the other date to get a past (negative) or future (positive) difference in days.

We could even divide these day counts by 365 and have a distance in years or true "age"!

Do you have other questions on TODAY(), dates, or other excel elements?  Reach out today via live chat, email, or phone!

Birthdays and Weekdays: Converting Dates To Weekdays In Excel

[EXCERPT ONLY, CLICK ABOVE HEADER FOR FULL POST WITH IMAGES]

Ever wonder what day of the week you were born on?  Let's figure it out in Excel!

The first method uses the WEEKDAY() function to return a number from 1 (Sunday) to 7 (Saturday).

The second method uses the TEXT() function to specify a format of "ddd" (for the 3-letter abbreviation) or "dddd" (for the full word) and returns the text day of the week in text.

What day of the week were you born on?  Try it out!

Need additional help with WEEKDAY(), TEXT(), or other Excel formulas?  Reach out to us today by live chat, email, or call us at 877.851.0132.

Comets and Calculations: Finding Years From Dates In Excel

[EXCERPT ONLY, CLICK HEADER ABOVE FOR FULL POST WITH IMAGES]

In 1705, English astronomer Edmond Halley was the first to recognize comet appearances as periodic.  The comet named after him, Halley's Comet, appears every 75-76 years!  Last time by was 1986, so we won't see it again until 2061... unless it's cloudy that night...

Well, meet Derik's Comet!

Yeah, I made it up, but it will help us understand how to use the YEAR() function to breakout the year from a date in Excel.

Easy!  Now if we want to find the number of years between comet appearances, we just subtract the previous year from the latest year.

Vwalah!

Need additional support with the YEAR function, dates, or other Excel elements?  Just reach out via live chat, send us an email, or call 877.851.0132!

MONTH Mania: Finding The Month From Dates In Excel

[EXCERPT ONLY, CLICK ABOVE HEADER FOR FULL POST WITH IMAGES]

Months are important!

If [CURRENT MONTH] = 4 (April), time to pay my taxes!

If [CURRENT MONTH] = 7 (July), time to take a vacation!

If [CURRENT MONTH] = 11 (November), time to eat turkey!

So there we go, it's often useful to check a date in order to know what its month is.  But how?

The first method is to use the MONTH() function, which returns a number from 1 to 12 assuming the function references a valid date.

=MONTH(A1)

The second method, if you need the actual name of the month, is to use the TEXT() function and specify "mmm" or "mmmm" as the format.  This essentially tells Excel to ignore everything else about the date and simply return the month.

=TEXT(A1,"mmmm")

Need additional help with MONTH(), TEXT(), or other features of Excel?  Reach out to us via live chat, email, or call us at 877.851.0132!

Tick Tock: Calculating Workdays Between Dates In Excel

[EXCERPT ONLY, CLICK ABOVE HEADER FOR FULL POST WITH IMAGES]

Christmastime!  The lights, the food, the time with family, and the opportunity to celebrate the One who changed everything...

...but

...it's only April. 

Want to know how many workdays until Christmas in two shakes of a reindeer's tail?  Here's how to do it with the NETWORKDAYS() function.

First we want to make sure we have our start and end dates handy.

Now we know from earlier this week that calculating the straight number of days is just a subtraction of later date minus early date.

But how do we calculate the number of work days?  Easy!

=NETWORKDAYS(startdate,enddate)

Only 186 workdays until Christmas!  JOY!!!

Need help with dates or other Excel elements?  Contact us via live chat, email at support@spreadsheetsherpa.com, or call 877.851.0132 today!

Time Flies: Finding The Days Between Dates In Excel

[EXCERPT ONLY, CLICK HEADER ABOVE FOR FULL POST WITH IMAGES]

The average human lifespan is about 28,000 days.  That means today represents 0.0036% of your life!  So let's make this Excel mumbo jumbo quick so you can get back to the Big Stuff.

Excel gives us two ways of finding the number of days between dates.

The first method is the DAYS() function, which takes an end date and a start date and spits out the number of days between them.

The second method is simple subtraction.  Remember, Excel stores dates as the number of days elapsed since January 0, 1900.  Therefore, when we subtract dates, we're really subtracting a lesser serial number from a greater one, leaving us the number of days in between!

Need additional help with dates, DAYS(), or other Excel elements?  Reach out today via live chat, email, or phone!

Beautiful Day: Finding The Day Of The Month In Excel

[EXCERPT ONLY, CLICK HEADING ABOVE FOR FULL POST WITH IMAGES]

In your life and mine, there are certain days of every month that hold some special significance.  You know... pay day, trash day, dog washing day, and so on!

Excel gives us the DAY() function as a way of referencing a date...

And returning a number corresponding to the day of the month.

So if we wanted to create a simple IF conditional that checked to see if a date is in the first seven days of a month...

We simply use the DAY() function to check if the number returned is less than 8.

Need additional help with DAY(), dates, or other Excel elements?  Reach out today via live chat, email, or phone!

First Date: How To Format Excel Dates

[EXCERPT ONLY, CLICK ABOVE HEADING FOR FULL POST WITH IMAGES]

We all remember that first date... the nervous looks, the awkward silences, and the trusty yawn-and-put-the-arm-around-the-shoulder move (which never worked for me...).

Thankfully, working with dates in Excel for the first time is far easier.  Need to format a date to look different than the default mm/dd/yyyy?

Select the cell(s) you want to look different and click the right mouse button.  From the list of options, you want "Format cells...".

In the Number tab, you'll notice that "Date" is already selected on the left, and the right side provides a whole list of alternate ways to display a date.

Need additional help with Excel dates or other questions?  Contact us now to chat live!

Day By Day: How Dates Work In Excel

[EXCERPT ONLY, CLICK THE ABOVE HEADING TO SEE FULL POST WITH IMAGES]

Ah, 1900... a good year.  L. Frank Baum publishes The Wonderful Wizard of Oz, Max Planck discovers quantum physics, and Louis Lassen invents the hamburger.

This also happens to be the year when Excel starts calculating dates!  But Excel doesn't exist yet, so how...?

Microsoft Excel records dates with a serial number.  Each incremental integer represents the number of days since January 0, 1900.

So the number 1 represents January 1, 1900.  The number 7 represents January 7, 1900.  The number 16229 represents D-Day from World Way II, June 6, 1944.  And the number 37145 represents the day the World Trade Center towers fell, September 11, 2001.

There's nothing special you need to do in order to get Microsoft Excel to record dates this way or show serial numbers as dates.  It's just important to know how Excel works behind the scenes because many of the formulas we use on these serial numbers.  We can even "subtract" dates!

So to recap, if you need to record a date in a cell, best to go with the mm/dd/yyyy format.  Excel will display it that way, but behind the scenes it's stored as a counter from good ol' 1900!

Need additional help with dates in Excel?  Reach out to us on live chat today!

Clean & Standard: Using IF With CONCATENATE In Excel

[EXCERPT ONLY, CLICK HEADER ABOVE TO SEE FULL POST WITH IMAGES]

If you've ever come across a column that is woefully unstandardized and you need to clean it, this post is for you!

Let's say we have a list of hyperlinks, but some of them are missing the initial http://

In the new column, we want to start out with an IF function.

The first term of every IF function is the condition we're testing.  To keep it simple, let's check each of our URLs to see if the first four letters are "http".

If this is true, that means our hyperlink is good as-is and we can just let the answer be the cell's current contents.  If this is false, we want to concatenate "http://" with the cell's contents.

That's it!  We drag the cell formula down through the other cells and we've got our squeaky clean column of hyperlinks.

Need Excel help with the IF function, CONCATENATE operator, or other Excel elements?  Please reach out to us on live chat where Sherpas are standing by to help you!

Destination Excel: CONCATENATE To Build Addresses

[EXCERPT ONLY, CLICK ABOVE HEADER TO SEE FULL POST WITH IMAGES]

In addition to combining names, we often have reason to combine text strings to create full or partial mailing addresses.  Piece o' cake!

As we've already learned this week, we can use the & operator to concatenate any two text strings together.

But that doesn't always make for the prettiest combination... if we just put ampersands between all our terms, the whole thing just comes out as a garble.  What to do?

How about we intersperse commas and spaces in our formula so that the address comes out looking the way we're used to seeing it?

And then from the bottom right corner of that cell, we drag the formula down to populate the remaining cells... beautiful!

Need additional help with CONCATENATE, &, formatting, or other Excel questions?  Start a live chat session with one of our amazing Sherpas today!

Say My Name! Building Full Names With CONCATENATE

[EXCERPT ONLY, CLICK HEADER ABOVE TO SEE FULL POST WITH IMAGES]

Work with Excel long enough, and there will come a time when you'll need to build a full name out of partial names.  Surely there must be a formula for that...

In fact there are two!

Suppose we want to create a column called "Full Name."  First we right click on the column heading letter and choose "Insert."

Now it's formula time.  Let's type
=CONCATENATE
and then choose the cells we want to bring together.

Oops, we forgot to include a space!

That's better.  Feels like a big formula for such a simple, commonplace thing... but there's a quicker way!  Excel provides the & operator as a quick way to concatenate strings.

And now we can grab the little "drag button" in the bottom right corner of the cell...

And pull the calculation down to populate the remaining names!

Need more help with CONCATENATE, &, or other Excel questions?  Contact us!

Cellmates: CONCATENATE Function With Cells

[EXCERPT ONLY, CLICK THE ABOVE HEADER FOR FULL POST WITH IMAGES]

Think of cells like Tupperware.  Each cell is a piece of Tupperware that you can put all sorts of things inside.  Now sometimes, you want to take the contents of two or more pieces of Tupperware and put them all in one.  How do we do that?

The CONCATENATE function!  Here's how it works.  Let's say we have our two pieces of Tupperware and they contain four letters each.  We can start the function in the adjacent cell (or any cell) by typing

=CONCATENATE

Then we need to tell Excel which cells' contents we want to schmush together.

Press ENTER and vwallah!  We have our new Tupperware container with eight letters.

You may be wondering if we can select a range of cells instead of each cell individually?

Sadly, no... it generates an error with CONCATENATE.  But there is a silver lining... Office 2016 introduced the TEXTJOIN function for just this purpose.  Ah, the joy of upgrades!

Need help with CONCATENATE or other features of Excel?  Reach out today!

Better Together: CONCATENATE Function To Merge Text

[EXCERPT ONLY, CLICK ABOVE HEADER FOR FULL POST WITH IMAGES]

Peanut butter and jelly.  Nachos and cheese.  First names and last names.  Some things are just better together!

Microsoft Excel gives us a couple of powerful ways to combine text strings in one cell.  One of those ways is the CONCATENATE function.

How does it work?  Pretty simple... we start by typing
=CONCATENATE(
and then provide all the text terms that we want to schmush together.

=CONCATENATE{"Payton","Manning")

What's the problem with the above example?  We're missing a space!  Not to worry, we simply go back into the formula and add a term for a space text string: " ".

And that's all there is to it!  A simple function, but one of the most versatile and powerful for manipulating text data.  This baby can even be used to convert numbers into text in a jiffy... but more on that later!

Need help with CONCATENATE or other functions?  Just ask!

Feeling Spacey? Removing Extra Spaces In Excel Text Data

[EXCERPT ONLY, CLICK THE ABOVE TITLE TO SEE THE FULL POST]

"Extra spaces, what's the big deal?"  May not be, unless you ever want to do searching, matching, sorting, or anything else to the data.  Of course it's a big deal!  Good data is clean data.  Here's the 1-minute answer on how you remove trailing spaces.

Eyeball Method

Do your friends call you "Eagle Eye"?  Well, they'll soon be calling you "Bloodshot Eye" if you spend your days squinting for extra spaces.  As the video shows, most space inclusions are completely undetectable unless you place the cursor at the end of the text.  For large datasets, that would be, well, impractical to say the least.

The SUBSTITUTE() Function

Thankfully, Mr. Gates and friends have provided a handy way to swap out bits of text for other bits of text.  Or remove text completely!

Let's say we wanted to turn "Brady " (with a trailing space) into "Brady" (with no trailing space).  Out in a blank cell somewhere, we would type:

=SUBSTITUTE(D12," ","")

This points to cell D12, looks for " ", and substitutes a null string if it finds it.

What if it doesn't find a space?  Glory be, rather than return an error it simply returns the string unchanged.

Need help with any of these concepts? We're just a phone call away!  Rather than spend your time trying to become an Excel expert, we can fix it for you in minutes!  Give Spreadsheet Sherpa a call at 877.851.0132 today.

Who Needs Mice? Smart Selection In Excel

Mice were good for Cinderella.  For Excel users?  Not so much.

Here at Spreadsheet Sherpa, we work with clients all the time who take the loooooong road using their mouse because the shortcut--using the keyboard--feels just a bit too scary.

Let's start simple!  As the video shows...

SHIFT + ARROWS

This turns multiple cells dark gray, letting you know they are ready for all kinds of fun activities... cutting, copying, bolding, deleting, and so on.

CTRL + LEFT MOUSE BUTTON

Ok, so we couldn't completely get rid of our mouse... but you won't use this one that often.  You're MUCH more likely to use...

CTRL + SHIFT + RIGHT/LEFT ARROW

No lie, this feature was my turning point from hating to loving Excel.  Holding CTRL, then holding SHIFT, then pressing the RIGHT ARROW selects every cell in the row until it hits a blank cell.  WHOA!

CTRL + SHIFT + UP/DOWN ARROW

Selects all the filled cells upwards or downwards.  YES!

CTRL + SHIFT + RIGHT + DOWN

Selects a whole block of text, great for copying and pasting everything you're working on.

Need help?  Just call!  Rather than spend your time trying to become an Excel expert, we can fix it for you in minutes!  Give us a jingle at 877.851.0132 today.

Forcing It: Converting Excel Numbers Into Text

Remember Dad's advice not to force it?  Well, sometimes you just gotta.  Excel numbers to text, that is.  Microsoft Excel wants to help, but sometimes it wants to help a bit too much.  Down, boy, down!

The bad news is, most people go up to the top and try to monkey with the cell settings.  Don't do it... that is, unless you want the cells permanently jacked for future generations using this file.

The good news?  It's easy to do in the cell itself!  Just use an apostrophe at the front of your number!

'00001

Need help with Excel?  We're just a phone call away!  Rather than spend your time trying to become an Excel expert, we can fix it for you in minutes for as little as $10!  Give us a jingle at 877.851.0132 today.