The hyperlink function allows you to create a working link with a formula. It takes two arguments: link_location and, optionally, friendly_name.
Working from the inside out, VLOOKUP looks up and retrieves a link value from column 2 of the named range "...Read more
The DAYS function returns the number of days between two dates. Both dates must be valid Excel dates or text values that can be coerced to dates. The DAYS function only works with whole numbers, fractional time values that might be...Read more
In older versions of Excel (before Excel 2016?), the character used for line breaks is different depending on whether Excel is running on a Mac or Windows computer: On Windows Excel, the line break character is ASCII 10. In older versions of Excel on a Mac, the line break character is ASCII 13....Read more
The INFO function can retrieve information about the current environment, including the operating system, the operating system version, Excel version, and so on. INFO takes one argument, type_text, which is a text value indicating the type of...Read more
First, the LEN function counts total characters in the cell B5.
Next SUBSTITUTE removes all "line returns" from the text in B5 by looking for CHAR(10) which is the character code for the return character in Windows. LEN returns the result inside of a second LEN, which counts characters...Read more
Times in Excel are fractional values of the number 1. So, 12 PM is 12/24 = .5, 6:00 AM is 6/24 = .25, and so on. So, to convert a time by a given number, you need to divide the number of hours by 24 to get required decimal value:
...Read more
When date information from other systems is pasted or imported to Excel, it may not be recognized as a proper date or time. Instead, Excel may interpret this information as a text or string value only. In this example, the goal is to extract valid date and time information from a...Read more
In this example, the goal is to set up VLOOKUP to retrieve costs based on a variable vendor name. In other words, we want a formula that allows us to switch tables dynamically based on a user-supplied value. There are two cost tables in the worksheet, one for Vendor A and one for Vendor B. Both...Read more
Shortcuts work great in the Mac version of Excel, but there are differences you should know about. This article explains the 5 key differences you need to be aware of to work productively with Excel shortcuts on a Mac. ...Read more
In this example, the goal is to sum by group, where each group is represented by a different color: Blue, Red, Green, and Purple. The worksheet shown contains two different approaches. In the range F5:G8, we have created a summary table to summarize counts by color. In column D, we are...Read more
