The Excel Skinny

A blog about Excel and its users

In previous blog posts we learned how to use the VLOOKUP and HLOOKUP functions to find exact matches in a table. Today we are going to learn how to use the VLOOKUP function to find the closest match, instead of an exact match.

This is accomplished by changing the last part of the formula syntax, [range_lookup].  Setting this argument to FALSE returns only exact matches, but if set to TRUE, the returned value of the function will be the closest match.  Note, the table you are pulling data from must be in ascending order for the closest match to work correctly.

When would you want the closest match rather than an exact match?  Calculating grades is a great example for the closest match VLOOKUP. Keep in mind is the VLOOKUP matches the value that is less than or equal to the lookup value.  

 

So we have a score of 81, but we want to know the actual grade:

This searches the Scale table and finds that 81 is closest to a C, so it returns the letter C in the cell that we added our formula. 

 

And that's all there is to it!  Now you can use the VLOOKUP function to search for exact matches, or closest matches in your spreadsheet.

 

As always, please let us know if you have any questions! 

Have you noticed the window that pops up when you open Microsoft Excel? I usually ignore it and click on the blank “Excel Workbook” or just click the Cancel button so I can get started as fast as possible.

Lets take a second to explore the options of the Excel Workbook Gallery.

In the template list there is a plethora of different pre-loaded options.

Time Management Templates:

  • Multi-Page Calendar
  • One Page Calendar
  • Pros vs Cons
  • To-do List 

Business Essentials Template:

  • Consultant Time Tracker
  • Customer List
  • Inventory Tracker
  • Services Price List
  • Weekly Time Sheet 

Personal Finance Templates:

  • Household Budget
  • Loan Calculator
  • Wedding Budget

Business Finance Templates:

  • Business Budget
  • Expense Report
  • Invoice
  • Marketing Budget
  • Twelve Month Cash Flow
Graph Paper Templates:
  • 10x10 Grid
  • 5x5 Grid
  • Plain Grid Large
  • Plain Grid

Out of these templates, the Excel Skin team has a few favorites.

 

The Consultant Time Tracker is an awesome pre-made time sheet. You can even use this as a simple employee time tracker or as an invoice for customers.

 

The Household Budget is a bit more complex than I could handle for a budgeting spreadsheet, but if you want to keep track of all your spending with pre-made graphs included this is a gem.

Last but not least, everyone needs to keep track of wedding spending. The Wedding Budget template lets you add your desired budget, and even creates graphs on overall estimated costs. Don’t let your wedding go overboard!

Explore these templates and see how they can help you. Template creation is one of the biggest time consumers when building out your processes for business or personal use. 

As always, please let us know if you have any questions

The Excel Skin team is here to help!

Overview

In order to use all of the available shortcuts within Excel 2011 (or any other application), you must turn off a few default operating system shortcuts.

 

Follow these simple steps to set up your Mac for Excel.

Go to: System Preferences

Click: Keyboard


 

On the left side, there is a list that includes Launchpad & Dock, Mission Control (only in recent operating systems like Lion and Mountain Lion), Keyboard & Text Input, et cetera. 

Select Mission Control. On the right you’ll see a list of check boxes. Uncheck any the Mission Control group, Show Desktop, Show Dashboard. If at any time you want to revert to the original settings, you can return to this menu and click “Restore Defaults.”


 

While you are in the Keyboard menu, check to make sure the Radio Button next to “All Controls” under Full Keyboard Access is selected. Proper use of this setting will help you reduce your use of the mouse. 

To learn more about navigating from the keyboard, be sure to check out our upcoming post, You Gotta Have More Keyboard Baby.

 

VLOOKUP on Mac Excel

The VLOOKUP function, when mastered, is one of the most useful functions in Microsoft Excel.  A VLOOKUP is a function that works off the first column in a list of data.  When would you use a VLOOKUP?  When you are trying to pull specific data from a list into another cell.  For our example we will use an Invoice List: 

For a VLOOKUP to work you must have a unique identifier and that unique identifier must be in the first column of your list.  In this example our unique identifier is the Invoice Number.  Once the VLOOKUP is executed if we put XL_SKIN2013 in a cell with the function it will return to us all the information we want.  Please note the VLOOKUP function has no restrictions whether you want to pull information into the same spreadsheet, same workbook, or different workbooks.  

To start, we put our Unique Identifier into a new cell.  This will be the new list where we want to pull this information into.  

The cell next to XL_SKIN2015 will be where we enter the VLOOKUP formula.  Refer back to the Formula Builder Shortcut to open the Formula Builder.  

Type VLOOKUP in the Search for a Function and double click VLOOKUP to start the function: 

The formula builder will ask you for lookup_value, table_array, col_index_num, and range_lookup(optional).  For these values insert:

  • Lookup_value = the value to be found in the first column of the table, and can be a value, a reference, or a text string.  
    • If you have you click the white box next to the lookup_value you can then go to your new list (where you will be pulling information into) and select the Unique Identifier you have already inserted:

    • Table_array = Tell the VLOOKUP where the original database/list is.  Select the entire list for this. Do not include headers.  

    • Col_index_num = this part of the VLOOKUP is to specify which information from the original database or list we want.  In our second list we said we want the Type of invoice, so we put 3 for Column 3.  Please note the col_index_num is always the number of columns in from your database or list.  It does not matter where the list starts or ends in relation to the Columns and Rows laid out from Microsoft Excel.  
    • Range_Lookup = This is either True or False.  Use the following parameters to decide if you need a value in the Range_Lookup: 
      • If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.
      • If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument
      • Our first column in the database is sorted so we do not need a value here.  

 

Press Enter and the information should appear in the VLOOKUP cell!

If you would like to add more information to the new list simply drag the VLOOKUP down and keep adding Invoice Numbers.  The information will automatically populate: 

 

Excel 2016 attempts to bridge the gap between Excel for Windows and Mac.

As many of you may have noticed, the vast majority of the shortcuts mirror each other on both operating systems. With this gap shortened a bit, here are a few tips that will make your life easier in Excel for Mac 2016. 

Formula Builder:

Formula Builder received a nice upgrade.  Instead of remember syntax, now you can simple click Shift + F3 to pop open the Formula Builder side bar.  This pop up will help you find the exact formula you'd like to insert, as well as walk you through the syntax step by step.  

 

Full Shortcut List:

As mentioned above, Windows and Mac key assignments are now quite similar.  Shortcuts like Ctrl+O for Open, Ctrl+F for Find and Ctrl+C for Copy now also work in the Mac version—no need to remember to press Cmd instead of Ctrl. If you want to clear the content of the selected cell or range, just press the delete key. This even works on your Mac laptops, where delete is actually the backspace key. Some popular shortcut keys are listed below; a complete list can be found here.

Touchbar Support:

The new Touchbar is now supported in Office for Mac 2016!  

Typing an equals sign into a cell in Excel immediately pulls up the most recently used functions in the Touch Bar. For example, with a tap (for the formula) and another tap (for a named range) in the Touch Bar, you can quickly sum a range in your spreadsheet. The Touch Bar also provides quick access to borders, cell colors and recommended charts—making it easier than ever to organize and visualize your data.

 

As always, please let us know if you have any questions! 

 

How do you compare data in Microsoft Excel? Logical Formulas!

Excel uses logical operations to ease the pain in comparing data. These formulas will return what we tell them to, based on specific criteria.

We will teach you how to run less than, greater than, and equal to formulas below.
For the first formula, we will focus on the “Greater Than” formula. This is used when you want to isolate cells that contain values greater than the parameters in your formula.
For the example below, I have a series of numbers between 1 and 10. All I want to know is if the number is greater than, or less than 2.

As shown above, my formula is: =IF(B1>2,"Greater","Less") for the first cell. This is telling Excel, if the data in B1 is greater than 2, input the word “Greater”. If it is less than 2, input “Less”.  I then pulled this formula down by the square in the lower right hand side of the D1 cell to fit my list.  

This is a very simple way to use the =IF logical formulas, but they can become extremely complex if needed.

 

As always, please let the Excel Skin team know if you have any questions!

 

So we’ve covered how to execute a VLOOKUP function, but what about a HLOOKUP?  HLOOKUP is the same function as VLOOKUP, only horizontal.  Note, the letter in front of “LOOKUP” indicates whether the search will be a vertical or horizontal search.

With HLOOKUP’s we are working with rows instead of columns.  So when would you use this function over a VLOOKUP? Follow along to find out.

The syntax of the function is: HLOOKUP( lookup_value, table_array, row_index_num, [range_lookup] )

Definitions of each argument:

lookup_value - The value that you want to search for, in the first row of the supplied data array.

table_array - The data array or table, containing the data to be searched in the top row, and the return values in any other row.

row_index_num - The row number, within the supplied table_array, that you want the corresponding value to be returned from.

[range_lookup] - An optional logical argument, which can be set to TRUE or FALSE, meaning:

TRUE - if the function cannot find an exact match to the supplied lookup_value, it should use the closest match below the supplied value. Note: If [range_lookup] is set to TRUE, the top row of the table_array must be in ascending order).

FALSE - if the function cannot find an exact match to the supplied lookup_value, it should return an error.

Now, lets put this into practice.

I have a spreadsheet that shows what each person spent on 3 specific categories.  I want to pull the Entertainment category for each person and list those out with my HLOOKUP function:

 

And with the HLOOKUP formulas revealed (=HLOOKUP(A10,A2:E5,4,FALSE): 

 

In the above example, the HLOOKUP function searches through the top row of the table_array (the range A2-E5), to find a match for the lookup_value (the name in A10-A13). When the the name is found, the function returns the corresponding value from the 4th row of the table_array.  We added FALSE so the name matching has to be exact.

 

As always, feel free to reach out with any questions!  

 

Microsoft Excel provides the perfect platform for an on-demand pricing calculator.  Instead of fumbling with your calculator while you're on the phone with a prospect, I will show you how to create a pricing chart that automatically adjusts all possible options based on just a few cells. 

In this example I will assume we are charging on a per-seat basis, similar to most SaaS companies.

List price will be $50/core seat, and $25/ lite seat, but I am willing to discount up to 20% off.  I will setup my spreadsheet so that all I have to do is adjust the number of seats, and the rest will be calculated for me. 

Here is my simple price calculator.  The only thing I have to change on each of my calls is the seat number, and the Totals per Month will automatically re-calculate.

Now, lets reveal the formulas in each cell.

 

The Standard pricing formula is straight forward.  All it is doing is multiple the number of Core Seats by $50, the number of Lite Seats by $25, and adding the two numbers together.

The discounted pricing formulas do the same thing, but they first calculate the discount and then subtract that from the Standard Pricing row.  

There are a few different ways to accomplish this calculation so feel free to play with the formulas as needed.  

And there you have it! A complete pricing calculator that only needs you to change the seat numbers to update completely.  You can set this up for unlimited possibilities in discounts or upsells to save you time and money in the future.

As always, let us know if you have any questions!  

 

Proficiency in Microsoft Excel is no longer a boost to your resume, it is a necessity.  

A firm grasp on a few Excel functions can be the difference between a 20 minute task and a 3 hour project.  With employers asking more out of their employees, there is no excuse for your Microsoft Excel skills to be subpar.  

Where do we start?  

      • The SUM formula: =SUM(A1, B1)
        • There is no secret as to what the SUM formula does.  It allows you to add 2 or more cells that contain numbers together.  Gone are the days of typing =5+5 in the cells, or even doing the calculations outside of Excel and inputting the answers.  With =SUM you can create a dynamic formula that can change with the ebbs and flows of your spreadsheet.    
      • The VLOOKUP formula: = VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
        • Daunting complex at first, but extremely simple and useful once you complete a few, the VLOOKUP is used to pull specific data from one list to another.  Read more about the VLOOKUP use cases here.
      • The Pivot Table function: Arguably the most powerful tool in Excel.  An entire company, Tableau, is basically one extremely complex Pivot Table company.  
        • This is the best way to analyze large amounts of data in Microsoft Excel.  Read more about Pivot Tables here.
      • IF Statements: =IF(logical_statement, return this if logical statement is true, return this if logical statement is false)
        • IF statements can be used in every way imaginable.  We can create an IF statement to return the word Yes or No if a customer has bought the product more than once, or even create a customer health score based on usage data. 
        • Here are a few sample IF statements to get started.  

As always, please let the Excel Skin team know if you have any questions!

When you think about Microsoft Excel, art isn't the first thing that comes to mind.  "Spreadsheets are made for numbers!" is what my parents would say, but these artists had different plans.   

Tatsuo Horiuchi:

Tatsuo is a 73-year old Excel art master.  His pieces of work are so unbelievable, that he has posted two Excel files online (here) so you can see for yourself.  His website: http://www2.odn.ne.jp/~cbl97790/

Danielle Aubert:

Danielle takes a different approach to Excel art with pixelated images and drawings.  

His personal website: http://www.danielleaubert.com/

 

Various Artists:

A quick google search and you will find a plethora of amazing Excel pixel art.  Here are a few that the Excel Skin team thoroughly enjoyed!

 

 

1 2 3 8 Next »