Five Overlooked Excel Capabilities

Microsoft Excel has numerous capabilities some of which are familiar such as the ability to sum a column, or create a chart. But there are also many that are little-known and yet very helpful. In this article we will examine five often overlooked capabilities of Microsoft Excel. For the purpose of this article, we will be using Microsoft Excel 2016, but many of these are available in earlier versions.  Microsoft has added numerous capabilities to Excel over the years, and we recommend keeping current to gain these advantages.  Hopefully, you find these overlooked Excel capabilities helpful!

Overlooked Excel Capabilities #1: Rollover Aggregates

Often times you might want to look at a range of numbers and determine the sum of those numbers, or average, etc. You may be tempted to create a formula in the spreadsheet in order to do this, but if you do not need to persistently see display that value you can simply highlight a range of cells and Excel will show you the sum, average, and count of values in the lower right corner of the status bar.

Excel Rollover Aggregates

 

Overlooked Excel Capabilities #2: Absolute References

This capability is actually something that has existed since very early versions of Excel, and in fact has been in all spreadsheets since the 1980s. But we included here because often times people do not understand how cell referencing works.

Normally when you create a formula in a spreadsheet it is using what is called “relative referencing”, which means that if you have a spreadsheet with a value in cell A1, and a second value and sell A2, and then you create a formula in cell A3 to multiply the two previous numbers, the formula will be relative.  This then allows you to copy the formula into column B, C, and so forth so that each row three multiplies the values in rows 1 and 2.

However, you may want to always reference a specific cell in your formula. For example, if you have a table of inventory items with their current sales price, and you would like to multiply every item by 1.05 in order to see what the price would be with a 5% increase, you may want to put 1.05 as a value in a single cell of the spreadsheet. This will allow you to change a single point, and the entire inventory would be updated so that you could change it from 1.05 to 1.06.  In order to do this you must use an absolute reference.

First let’s look at what a relative reference looks like in the formula.  In the figure to the right, you can see that cell A3 has a formula which is “=A1*A2”.  This formula is the same in cells B, C, D, and E, but in each case the “A” is changed to the column header (e.g. “B”). In this case we have copied the formula from A3 into columns B through E on the same row, but in each case it converted the “A” into the appropriate column header.Excel Relative Reference

 

 

 

But what if we wanted to create a formula as we describe before with inventory parts and potential price increases? In that case we will need to have an absolute reference. Here is what that looks like:

Excel Absolute Reference

In this case we have a set of parts that we can sell, and each has a current price shown in column H. In column I, we have a formula which takes the current price and multiplies it by the factor which we are increasing it by in this case it is shown in cell “I1”, and is 1.05.

Note that the formula has a ‘$’ character inserted in front of both the column reference and the row reference, so that instead of writing the formula as “=H4*I1”, we have written it as “=H4*$I$1”.  By doing this it will always reference cell I1 instead of adjusting it for relative position to wherever we copy the formula.  This lets us enter the formula one time in cell I4 for the horn, and then copy it to all of the other lines in that column so that each item is increased by the value in I1. If we do not put the dollar signs in the original formula, it will use relative referencing so that the guitar would actually be multiplied by cell I2, which is blank.  You can use absolute referencing with either the column, or the row, or both. So valid examples would include “$I4”, “I$4”, or “$I$4”.

Overlooked Excel Capabilities #3: Format as a Table

Sometimes you may want to use a set of data similar to a database, and be able to sort or filter the data so that you only see what you need to see, in the format that you would like to see it. In this case it is helpful to format the area as a table.

In this example we have a set of data for our employees which looks similar to this:

Excel Data Before Table

 

 

 

 

But this does not allow us to easily sort or filter the data, and in very long or involved data sets, may be hard to read from left to right. In this case it is much easier to work with it if it is formatted as a table.  To do this, select (highlight) the range that represents the table, in this case I will be highlighting from “Name” and then down and over to the last start date for “Kramer”.

While this is highlighted, I then select from the home menu of the ribbon bar in Excel, the option for “format as table”.

Excel data formatted as table

 

When I do this I will be presented with multiple options for how the table will be formatted with various colors, and row highlighting options. Once I have selected one, I will then be asked if my table has headers, and because I chose a set of data that does include column headers, I will click ok. The data area will be reformatted with banded rows and added drop down options next to each column header.

I now have many options to manage the data, including:

  • Sorting the data by clicking the down arrow key next to any of the columns, and choosing “ascending” or “descending”.
  • Filtering by some text or value
  • Filtering the data by clicking the down arrow key next to any of the columns and choosing some of the options with checkboxes. This allows me to turn off and turn on the display of certain areas, so that for example and may only see certain departments in my data set.

By using these features, you can more easily manage a large data set in Microsoft Excel.

Overlooked Excel Capabilities #4: Sparklines

Sparklines, are a function that was added in Microsoft Excel 2010.  These are small graphs that fit into a single Excel cell and represent a range of data, similar to the way you might insert a larger chart into a spreadsheet.

In our example we have a set of data which looks similar to this:

Excel spark line demo data

This is traditional data with information for revenue and customer count per month, and a calculation for how many new customers, or lost customers we may have experienced for the month.

There are three sparklines available to us in Excel each of which can be customized. They are:

  • Line
  • Column
  • Win/Loss

In order to insert a spark line, you must be in an empty spreadsheet cell. It is recommended that it be wide enough to show a nice range of data, but you can adjust the width later.  Once you are in this empty cell, go to the “Insert” tab of your ribbon bar, and find the spark lines available to you. These will look similar to this (from Excel 2016):

Excel spark line ribbon bar buttons

You can see that the three choices: line, column, win/loss are all available to you here. Click on the one that you choose, such as line and Excel will bring up a dialog box asking you for the input data that will be included in your sparkline.  Select the data in your spreadsheet, and confirm that the cell you would like the data in is shown in the dialog box. Click the Ok button and the cell will show a sparkline chart. In our example we have graphed each of the columns of variable data in our spreadsheet and we can see three different spark lines.Excel spark line example output

 

 

Spark lines are very convenient way to quickly show data, and in fact if you have multiple columns of data from left to right you can show an in-line chart for that data, similar to the following:

Excel Sparkline inline demo

Once you have inserted a sparkline, you can change the format by selecting the cell with the sparkline, and an additional toolbar option will be added to the ribbon called “Sparkline Tools Design”, selecting this will give you many more options.

Extra Tip: If you create a sparkling in one cell, you can copy it to others and it will copy the format and use relative data ranges.

Additional Info: https://support.office.com/en-US/Article/Use-sparklines-to-show-data-trends-1474e169-008c-4783-926b-5c60e620f5ca

Overlooked Excel Capabilities #5: Print – Fit to Page

Sometimes your data will not fit conveniently on one page, or one page width when you send it to the printer.  For example, you may have too many columns to fit from left to right, and would like all of them to fit on one page in landscape mode. In order to do this we will take two steps.

  1. Change the orientation to landscape (optional)
    1. On the ribbon bar, click “Page Layout”
    2. Choose “landscape”
  2. Change the print scaling to fit on one pageExcel print options for scaling
    1. Click the Excel “File” option on the ribbon bar
    2. Choose “Print”
    3. At the bottom of the options you will see “No Scaling” by default, click this and you will see more options. Choose one of these each of which is described:
      1. “No Scaling” – the default option and which prints the pages according to the way they are laid out in the spreadsheet for sizing, etc.
      2. “Fit Sheet on One Page” – fits the entire spreadsheet, all rows and all columns, onto one printed page.
  • “Fit All Columns on One Page” – fits all columns on a single page width, but entire print job may span multiple pages top to bottom.
  1. “Fit All Rows on One Page” – fits all rows on a single page but entire print job may span multiple pages left to right.

Using these options will allow you to have a much nicer print output but be careful if you overdo it, it will be too small to read.

Technology Frontiers

The word frontier can be defined as “areas near or beyond a boundary”, and when we think of those who are “frontiersman”, we may think of ancient explorers, or the crew of the Enterprise on Star Trek who were exploring “space, the final frontier.”  These frontiers are new and exciting, but also fraught with risks and unknowns. We have gone through many frontiers in the information technology industry.  Looking back in my life we have had several: the move from mainframes to PCs, from character based operating systems to Windows and Mac graphical user interfaces, from local area network client/server applications to web based applications, and from PCs to tablets and other mobile devices. There are always new technologies and they drive change in how we operate and live and communicate.  Think of how the pony express system and telegraph allowed people to settle out west in the 1800s, far away from the civilization they knew in the eastern cities.  Similarly, today I write this at home while connected to my office and team via web, email, cell phone, and Skype for Business for chatting and sharing documents and screens. I am not 2,000 miles away, but I could be and it would be fine for what I need to do now.  This was not possible 20 years ago and yet it has become commonplace, and in it we see that I am using several of the technology frontiers of my lifetime.

And even now, we at Keystone are working with new technology that you may not even be aware of.  Why do we do this?  Inherently we love technology, so if you did not even need us to do it, we would still geek out at the latest mobile phone, backup software, security patch, and other fun to all or mundane to many technologies. We just can’t help ourselves, but we know that not everybody can stomach the pain of the new frontier.  They call it “cutting edge” for a reason, and sometimes it means “bleeding edge”.  We would not subject our clients to a new technology unless we have a good sense of the risks inherent, and how to overcome them to get the best value in the safest way possible.

Here are some technology frontiers we are exploring now in our Research and Development (R&D) that you may see as commonplace in your future.

3D Printing

So far, this feels like early paper printing technology.  Have you ever sent a job to a printer and nothing happens?  Or have you ever sent a 2 page document only to get 100 pages of what looks like alien communication?  That is what 3D printing feels like now.

3D Printing takes the concept of a data file, with instructions for how an object is shaped, and combines it with plastic extrusion technology to “print” the object.  You load the plastic filament into the 3D Printer, and send the job to it as a set of instructions.  The printer is supposed to print the object by feeding the filament through a hot end extrusion nozzle (the “print head”) and dropping it into a flat surface.  The print head moves up and down, and the flat surface (“the bed”) moves back and forth and eventually your object is sitting there; ready to use.

But it does not quite work that well.  Sometimes it runs for a while and stops, sometimes it slams into the bed and melts a hole, sometimes nothing, sometimes a big mess of plastic, etc.  But when it works it is great!

Think about some of the implications for your life.

  • You want to create a 3D representation of a new factory floor plan to test your kaizen or lean model more fully – just design it in the 3D software by dragging and sizing objects, and send to the printer. This reduces the time to prototype saving costs and improving flexibility.
  • Can’t find the battery cover to the remote control – just go online and download the design file and print a new one. No trip to the store, no tape over the batteries, etc.
  • Your client is not able to visualize what you are describing for your latest design for their building, and you are not going to make the sale because they lack a perspective needed to decide. Perhaps a 3D representation will help?
  • You need a new towel hook for the bathroom, but instead of buying online and waiting, you go browse designs, select and pay for one, and download and print.

At this point 3D printing has already been used to create new organs for your failing body parts, prototype new cars, create functional desk accessories, and help sell ideas.

It is new, it is exciting, and we are testing it now!

Clustered Computing

Most computing is one computer doing one or more jobs, and reliability and performance are based on what is in the machine’s box.  If you need more power, you open the machine and add more memory or disk space.  If the CPU is a few years old and not keeping up, you buy a new machine and rebuild everything. If you need reliability you buy one with at least 2 of everything you can: multiple drives, power supplies, and network cards. Performance and redundancy in this model are built on what is in the machine.

But if you could just add another machine and have it do ½ the work?  Or three more machines and they all share.  You now have 4 machines – 4x the performance, and if one goes down you run on 3 machines and replace the failed one as needed.  This is “clustered computing”.

It is not particularly new, and is sometimes called “Super Computing”, “Parallel Processing”, or “High Performance Computing”.  It was first conceived in the 1960s but required incredibly expensive hardware and custom software, and only accessible to organizations like the National Oceanic and Atmospheric Administration (NOAA) for use in weather studies.  In the mid-1990s new technologies allowed computer clusters to be built from commodity servers (search for “Beowulf Cluster”).  Suddenly organizations could build their own.  So at the same time that the internet was becoming available to everybody, the power of clustered computers became available to build search engines like Google and Yahoo!  (for a quick view of Google’s first cluster that looks like a Lego system, see this: http://infolab.stanford.edu/pub/voy/museum/pictures/display/0-4-Google.htm).

These capabilities are now becoming available in two ways:

  • Build your own local super computer from off the shelf parts. We are doing this now, using about $200 in parts primarily based on the Raspberry Pi motherboards. By linking 4 of these credit card sized motherboards that each have 4 “cores” together in a clustered network and using special software, we have what looks like one computer to a software application. In testing, using one unit in the cluster, it takes about 35 seconds to calculate the value of Pi to 16 digits on one core of one Raspberry Pi, but when we go to 4 Raspberry Pi units (16 cores) we are seeing times of less than 9 seconds!Raspberry Pi Cluster 2 - part of a technology frontiers approach
  • Rent space on a cloud provider’s platform and use it while letting somebody else (Microsoft, Amazon, Google, etc.) do the dirty work of managing the platform and the networking. See this for a quick description of Google’s current platform for this (https://cloud.google.com/solutions/architecture/highperformancecomputing).

One caveat of this is the necessity that your software be developed to run in a multi-node, multi-core environment.  You can’t just grab a copy of Microsoft Excel and expect it to calculate your budget faster (although oddly enough Microsoft has extensions to support this (https://technet.microsoft.com/en-us/library/ff877825(v=ws.10).aspx)!  Your software has to be designed for multi-threading, multicore support (you may see things like “HPC”, “High Performance Computing”).  The leaders in this area now are big data database packages like Hadoop that have to process incredibly high volumes of data in a short time.

This technology may not be ready for the average small to medium sized business, but it shows what is possible and could help with growth and seasonal needs.

Latest Applications, Operating Systems, and Devices

This is the most basic technology we test – the thing you see next week, or next month, or next year.  We have multiple devices and many different operating systems and applications that are in beta form, and we are trying them out so we have a perspective on what you may see, when you should move to it, and what the risk and reward will be.

In fact, I just had a lock up when I was writing this article using Windows 10 in an advanced preview copy, and Microsoft Word 2016 latest version; it does not occur often, but does happen.  We are evaluating the features and capabilities, the user interface, and the reliability (in this case I lost a few minutes but no data).Windows 10 Blue Screen - The result of working in technology frontiers

Some of the tools we are testing now include:

  • The latest suite of Office 365 Products, including Skype for Business
  • SharePoint and OneDrive for Business
  • Apple MacBook 12” with a beta version of Apple OSX
  • Cloud Based Information Security Systems
  • Amazon Echo
  • Beta Versions of IOS (on iPhones and iPads)

Summary of Technology Frontiers

There are waves of technology shifts that represent new frontiers for users and business organizations, and each represents some questions: What is this?  How can it help me?  What are the risks? We are looking at these so you know we have an eye on what may make a difference for you!

Next time we will catch up some more, and include some other technology frontiers like Internet of Things (IoT) and Voice Recognition!