The Excel Skinny

A blog about Excel and its users

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!

 

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! 

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!

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!

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) Select All with a click

Instead of executing the Select All shortcut, there is a simple button built into every spreadsheet that will select all data in that spreadsheet.

 

2) Using the Formula Builder

We can’t always remember every formula in Excel, which is why we have the Formula Builder! With the Formula Builder you can search for specific formulas by name, insert the function, add your data, and receive any assistance through the Formula Builder. Open the Formula Builder by clicking the fx Insert button in the Formulas tab, or by executing the Formula Builder shortcut.

You will be able to search categorically when clicking the button in the Formulas tab.  

3) Remove Duplicates

Excel is one of the best programs for removing duplicate values. All you must do is copy your data into an Excel spreadsheet, and click on the Remove Duplicates button. I found this especially useful when I found out I had duplicate .jpg files taking up space on my computer, Excel identified and helped me remove those files with a click.

How do we do it? Select the data you’d like to analyze, then click the Remove Duplicates button in the Data tab.

A dialog box will appear telling you how many duplicates were found. Click Remove Duplicates in the bottom right to erase these duplicates from the spreadsheet.

How to remove characters in an Excel cell                       

There have been multiple times where I export some data from a website, and the export adds unwanted characters in each cell. There is always that slight pause and dilemma of going through each cell and manually deleting these characters, or just leaving them as they were exported. In this blog post we will tackle how to remove those characters with one simple Excel function.

For this example, our export added the abbreviation “cust_” before each customer ID:

Next to the first cell type the following formula:

=RIGHT(A1, LEN(A1)-5)

 

This formula tells Microsoft Excel to count 5 characters in from the right of cell A1. Once you click enter, it will return the remaining characters (in this example the customer ID number).

 

Now click the cell B1 with only the customer ID number showing. Double click the square in the bottom right of the cell and this formula will be applied to the entire column.

And it is as simple as that! You can edit the =RIGHT(A1, LEN(A1)-5) formula to target any cell or number of digits.

Here is the raw formula: =RIGHT(CELL, LEN(CELL)-CHARACTER SPACES)

Just replace CELL with the cell you are targeting, and CHARACTER SPACES with the number of spaces from the beginning of the cell.

Note, you can also count from the end of the cell by replacing RIGHT with LEFT in your formula.

As always, please let us know if you have any questions and we will be happy to help!

Nutrition Tracking in Microsoft Excel

Here at Excel Skin we are always looking for ways to use Microsoft Excel on our Mac computers.  With nutrition being such a complex but major part of our lives, tracking that nutrition is very important to maintaining health.  In this post we will go through a few steps to create an easy to use spreadsheet that updates calorie count and food intake automatically.

Step 1: The first step is to create your nutrition tracker layout.  In the example below, I have used a horizontal layout for the days, and labels for each meal + any snacks.  The idea is we will write the types of food next to the meal, and the number of calories directly under it so we can calculate calorie totals.

I used the Select Row and Select Column shortcut to make the gray border between each week. 

Step 2: Add a total calorie calculator cell under each day, and next to each week. 

We have now designated row 16 to be our daily calorie counter, and next to Week 1 will be the weekly caloric total. 

Step 3: Create the calorie calculator formula’s in the designated cells.

While in the total calorie calculator cell, insert the AutoSum formula.  Then select the cells you want to sum.  In the example above, we want B5, B8, B11, and B14 for Monday’s daily total.

Once you’ve selected the cells, press Enter. 

Step 4: Drag this formula across all weeks.  To do so, click and drag the square at the bottom right of the cell with the AutoSum formula in it. 

This will duplicate your =SUM formula for each day accordingly. 

Step 5: Create an AutoSum formula for your weekly total.  To do so, execute the same shortcut you used to create the daily calculator next to Week 1:

This will add up all the days so you can see your weekly caloric total.

Step 6: Start tracking! 

Once we add some data to the table we have created, calories will start being added automatically.  Here is an example of my week 1 for Monday and Tuesday:

As you can see, the daily totals have been calculated, and my weekly total is updated automatically as well.  This spreadsheet takes almost all the work out of the calculations, all you have to do is input the calories!

You can copy this Week 1 calorie counter to as many weeks as you want to stay healthy and keep tracking your performance!

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

1 2 3 8 Next »