by ·2 Comments

Create a Holiday Email in Excel, using HYPERLINK function


Create Holiday Greetings Email in Excel, using HYPERLINK function

         It seems like this year passed by particularly fast, and now we are at a point when people expect to receive Holiday Greetings from us. Did you know to that you can compose your holiday email entirely in Excel, using the HYPERLINK function? We need to meet two basic requirements: 1) set up a default email client (Outlook, LotusNotes, Thunderbird, GroupWise, etc.,) AND 2) your entire message (including To/CC/BCC/Subject/Body fields) needs to be 255 characters long or shorter. While this method requires no macros, nor VBA code, it cannot actually “send” the email for you, it will open it in your default email client, but you still need to click the SEND button.

          HYPERLINK function has the following syntax (link_location,[friendly_name]). This means that the only required parameter is link_location, passing a sample email address argument, we can create a simple email link using this syntax: =HYPERLINK(“mailto:Email1@example.com”). I would strongly advise to also name your hyperlink, otherwise the cell containing it will appear to be blank. Unfortunately, we can find rather limited documentation on HYPERLINK function within Excel Help section. However, Microsoft did publish additional information about this function. This article can help us easily add To,CC,BCC,Subject, and Body fields to our email template.
Continue reading

by ·5 Comments

7 Ways to conditionally calculate sum of values in Excel.


Conditional SUM functions in Excel

         Excel offers different ways to accomplish the same task. This is especially evident in the case of using Excel functions, where we can simply choose the one that offers the best solution, or more realistically, the one that we are more comfortable using. As an example, let’s solve the following scenario: We are offering online Excel courses both: on our internal website, as well as on Udemy’s platform. Udemy charges us 50% fee on all course sales, and also offers promotional rates to increase our volume. As a result, we are selling the same content at different prices. Looking at Thanksgiving week sales performance, let’s calculate our total Net Sales for all of Udemy transactions (highlighted). Let’s use different Excel functions to perform calculation required.

Continue reading

by ·0 Comments

Using SUBTOTAL Excel function

SUBTOTAL Excel Function

         Let’s talk about benefits of using SUBTOTAL Excel function. This function offers too many features to be overlooked. On the surface, it appears that this function does nothing more than its name suggests: calculates a subtotal of your references: constants, individual cells, or ranges of cells. The reality is that this versatile function calculates 11 different subtotals, (think 11 functions in one): AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, VARP. This function works similarly to your typical SUM or AVERAGE, where you need to reference your data range to be used in the calculation, however, you also need to specify function type. As you are typing the name of this function in your formula bar, Excel will automatically display the list of possible function types, or you can look up function types from the table below. The function’s syntax is very simple: = SUBTOTAL(FUNCTION_TYPE, RANGE)

Continue reading

by ·0 Comments

VLOOKUP() vs. INDEX(MATCH()), which should you use?!

VLOOKUP() vs. INDEX(MATCH())

         I recently read an excellent article, where its Excel MVP-author makes a very compelling, albeit biased argument towards using INDEX(MATCH()) function combo, instead of taking the VLOOKUP() route. That article jokingly proclaimed the end of the VLOOKUP vs. INDEX(MATCH()) debate. Interestingly enough, some of the heavy-weight Excel MVPs and expert users weighed in on this issue, in essence continuing the debate. One of the commentators, published an equally brilliant rebuttal, skewing the victory towards the VLOOKUP function. Being a biased VLOOKUP proponent myself, I concur with his conclusion completely. In addition, I’d like to throw in a crazy idea of using SQL as a better, faster, more flexible, and easier solution, comparing to either: VLOOKUP(), or INDEX(MATCH()).

Popularity

         I believe there is no disagreement here: VLOOKUP enjoys tremendous popularity. People have different opinions whether this popularity is justified. I belong to the “ease of use” and “ease of learning” VLOOKUP camp: two of many factors that help us explain high adoption rate of this function among all Excel users.
Continue reading