The Excel Skinny

A blog about Excel and its users

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.

I steered clear of creating pivot tables on Microsoft Excel for many years.  Simply hearing the phrase "pivot table" in the office made me run the other way.  I didn't even know what they did, they just seemed extremely complex and daunting.  

I was eventually asked to create a pivot table and within the hour my fears were gone.  With Microsoft Excel for Mac, creating a pivot table is easily achieved in just a few steps using the toolbar.  No formulas or shortcuts necessary! 

Step 1: The original data. For this example our data for the pivot table will be in Sheet 1.

 

Step 2: Select the cell where you want the pivot table to appear. We are going to use A1 on Sheet2.

Step 3: Select the Data tab from the toolbar in Microsoft Excel.

 

Step 4: Click the small arrow next to the PivotTable icon and select the Create Manual PivotTable… option.

Step 5: The Create PivotTable dialog box will appear. Here we must select the original data table on Sheet1 for the “Use a table or a range in this workbook” Location: box.

To do so, click on Sheet1 and highlight your table:

 

This will auto-populate the information into the Create PivotTable dialog box. Click OK.

 

Step 6: Now the Create a PivotTable box will close and a PivotTable builder will appear.

 

In the upper portion labeled Field name, select the fields you wish to add to your pivot table. I have selected Order Number, Product, Unit Price, and Quantity.

I then moved Order Number from the Values box to the Row Labels, because I do not need a value for the Order Number.

Whenever you are satisfied with your pivot table, that should be changing in the background as you modify the options in the PivotTable builder, click the X to close the builder dialog box.

Step 7: Edit the cell with Row Labels to the correct terminology: In my example that would be Order Number.

And there you have it! A quick and simple way to make beautiful pivot tables for Microsoft Excel on Mac. Please let us know if you have any questions!

After 5 years of separating Mac and Windows operating systems in Microsoft Office, 2016 closes the gap of shortcut compatibility.

Excel 2016 brings a few new features over its 2011 predecessor such as:
  • Recommended Charts
  • PivotTable Slicers
  • Analysis Toolpak
  • An improved Formula Builder
  • File compatibility improvements
  • Print options
  • Improved keyboard shortcut overlap
The last feature is, of course, the most important to us at Excel Skin. Before Office 2016, Excel shortcuts on Mac were only functional on Macs. Now you can learn Excel shortcuts on your operating system of choice, and the majority of these shortcuts will carry over if you find yourself using the alternate operating system.

If you have upgraded to Office 2016 on your Mac, now is the time to learn the new shortcuts! You can see a full list of shortcuts here, or use the Excel Skin’s color coded keyboard cover to teach you the most useful shortcuts in the least amount of time.

As always, feel free to email us or comment below with any questions you may have!

As you may know, Microsoft Excel is a great way of keeping track of basically anything. You can track your workouts, travel plans, packing list, holiday shopping, or even your diet.
In this blog post I will show you how I use Excel to track the type of Customer Support tickets I receive on a weekly basis.
My first workbook is the data center. In this workbook I add the number of times a criteria is applied to a label compared to the total number of tickets.

The next step is to make a chart for those that are a bit more visual. The second tab will be your bar graph, and the third tab will be the pie chart. The bar graph is a nice and easy graph for the number of times a label is applied, and the pie chart is more suitable for the percentages.


To set up a chart, click on the Charts tab in the toolbar.

This will bring you to a list of charts that you can create. Click on the one you want. In this case, I will do a vertical bar chart, or Column.

What appears is a blank screen where the chart will be.

Now we must designate the data we want in the chart. To do so, right click the blank window and click Select Data.

This will open a Select Data dialog box.

In this dialog box, we want to designate the data with the Chart data range: space. Click in the blank space, and then go to your data and select the range of data you wish to select.

Click OK when you are done selecting the data, and your chart will appear.

Please let us know if you have any questions, the Excel Skin team is here to help!

If a function key doesn't work as you expect it to, press the FN key in addition to the function key. If you don't want to press the FN key each time, you can change your Apple system preferences:

  1. Go to Apple > System Preferences > Keyboard.

  2. On the Keyboard tab, select the check box for Use all F1, F2, etc. as standard function keys.

    NOTE   This check box affects how the function keys work for your entire computer, not just Excel. After selecting this check box, you'll need to press the FN key to perform the special features printed on the key. For example, you'll notice that the F2 key has a brightness icon. To use the key to change your brightness, you'll need to press FN + F2.

Moving can be a daunting task regardless of the size of your house or apartment. There are hundreds of little tiny objects that do extraordinary things that you’ll want to accompany you in your new abode. What’s the best way of keeping track of these objects? Microsoft Excel of course!

Using Microsoft Excel for moving has two significant competitive advantages over a simple word document or text based list. You can add the values of each item in case of damage, and you can easily check off items that arrived safely

The first step is to list out your items and the values as shown below:

The next step is to, of course, move these items. Once you have all the boxes in your new home you can start sorting through your items. 

The easiest way of doing so is a color coded system. When you unpack an item, color code it in Microsoft Excel to confirm you have received it.

By the end of all your unpacking you’re Excel spreadsheet should look something like this:

Hopefully you have mostly green!

And there you have it, the most organized and efficient way of tracking your household items during a move in Microsoft Excel.

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

Not all things in Microsoft Excel for Mac require a fancy shortcut. Sometimes Microsoft makes our lives much easier with the ever-improving toolbar. The toolbar is that confusing looking thing above your worksheet that looks like this:

As you can see there are multiple tabs on the toolbar labeled Home, Layout, Tables, Charts, SmartArt, Formulas, Data, and Review. For our tip today we are going to be living in the Review tab.

Once you click on the review tab a new set of icons will appear.

The first icon is to check spelling and grammar. Something that is not often required in Microsoft Excel, but if you need it, it’s there. The second icon is what enables us to insert a comment into a cell in our Microsoft Excel spreadsheet.

Now select the cell you wish to insert the comment in.

Once the cell is selected, click the 2nd icon on the Review toolbar labeled New. This will pop open a comment box.

From here you can insert any information you want about the selected cell.

 

Once you click away from the sticky note, the comment will be hidden in the cell so it does not disrupt your spreadsheet too much. To reopen the comments simply slide your mouse over the red triangle in the upper right hand side of the cell and your comment will appear.

You will notice now all of the comment icons on the toolbar are available to use, where as before inserting a comment only the New comment icon was clickable.

Comments are a great way of adding hidden information to cells. The comment will only pop up when you want it to, and you can reduce clutter in your spreadsheet.