How to articles for basic excel, VBA and SQL.

Sparkline Charts & Icon Formatting in Excel – Quick Organizing

I live in both sides of my brain and while I was learning Excel I was slow in producing needed documents. Because of my creative, and sometimes perfectionist/control freak nature, I had to learn how to see the forrest through the trees and deliver simply on function instead of presentation. After learning the basics and a few additional features, I was able to deliver both presentation and function.

In today's fast paced business environment it is necessary and advantageous to be able to drive business, revenue and profit while being able to communicate effectively and quickly.  Excel helps us do that quickly. Knowing some of Excel's additional functions helps us do that with a little more style.

Sparkline Charts allow users to analyze trends on the spot, allow creator to design in one click and unlike other charts these sparkline charts print with the spreadsheet. For example take a quarterly review of sales per sales rep and their quota attainment. With these two additional features, my VP of sales and CFO can quickly and easily visually digest given data.

These features allow the user to visually display trends or highlight variances without going through added work of creating, updating and pulling in charts along with our data.  I use these feature to set up summary tabs in excel for use in my presentations. With my data organized, I then can copy over the data to my presentation or simply print the spreadsheet. No additional work required. My presentations are updated in minutes.

Create a Sparkline

  1.  Put cursor in blank cell where you want the sparkline to appear.
  2. From menu click on Insert tab, from sparklines group, select the type of sparkline you want to create.
  3. A pop up screen will appear
    1. In Data Range: select the group/range of data you want to use as your reference (the data you want to trend)
    2. In Location Range: select the group/range of cells you want the corresponding sparklines to appear.

Once the sparklines are created an additional tab will appear in your ribbon titled Sparkline Tools. You can further customize the sparklines using this tab. Select a cell with your sparkline that you want to adjust and you can change the color and attributes of your sparklines.

Create Icon Conditional Formatting

  1. Highlight with your cursor the data you want  your Icons to appear next to
  2. On home tab, click on Conditional Formatting and move cursor to Icon sets

  3. From the drop down select the type of Icon you want to display
  4. The conditions that must be met for each red, green, yellow Icon default.
  5. Adjust conditions for Icons. To adjust conditions (meaning to adjust at what point or break in your data you want to display the different colors - for example green for over 100%, yellow for between 70 and 100% and red for anything below 70%)
    1. adjust by selecting one of the cells with your conditional format
    2. click on Conditional formatting from the home tab and select the Manage Rules
    3. From the Conditional Formatting Rules Manager widow select your set of icons and click on edit rule
    4. From this window you can change your colors, orders, Icon shapes and values that drive the Icons

The Importance of Excel Training & 3 Top Formulas to Get You Started

I can trace my professional career back to one assignment. I worked for a company of 60 employees with buying interest from large organizations. And with the needed push for an increase in sales productivity we worked to improve back end support in sales operations. At this moment, an assignment popped up on the radar and my boss asked me to take a shot. The one assignment that would ultimately shape all other assignments: a multi-functioning excel based ordering template easily used by the sales organization. But there was one problem: zero Excel training.

Hello, crash course in Excel programming. Since there wasn't any one else in the organization that could tackle this project, I regarded the task as one that could propel my knowledge base as well as my visibility within my department. Little did I know, the weeks that I spent investing in Excel training has made all the difference in my professional career development. And after years of experience I know why:

Excel training benefits the entire company

As a finance professional it makes sense that I am experienced with Excel. But, despite popular belief, Excel is not strictly an accounting and finance related program. In fact, Excel helps users track information across the entire company.  And thus Excel, is used anytime money OR data is used.  Since, it is the age of technology and data, most companies are overflowing with data. Because of this, these companies are in need of employees that can organize and interpret the data into useful information. From administration, operations, project management, servicing, logistics and marketing. All departments receive benefits from users who are comfortable with using Excel. As such, Excel training is an excellent way to increase employ-ability while improving your effectiveness within your organization; no matter what department.

When we use our time to invest in these types of skills, we increase not only the companies ability to turn data into useful information, but we also increase our own earning potential.

Most popular uses of Excel

  • Organize and interpret information more easily
  • Creating and outlining business processes
  • Project management tool
  • Budgeting and Forecasting
  • Reporting and Visualizations (charts, graphs, presentations)
  • Analyzing sales, business intelligence and marketing data

Defining beginner and advanced Excel skills

Beginner skills

  • Navigating the user ribbon
  • Creating workbooks
  • Entering and editing data, text or numbers
  • Inserting and deleting rows, columns and fields
  • Creating simple math formulas
  • Building charts and graphs

Advanced skills

  • Using pivot tables
  • Creating macros in Visual basic
  • Keyboard shortcuts
  • Creating complex formulas

While Excel is not a program mastered overnight, Excel does offer skills that can be built upon one another. And simply knowing a few Excel formulas, can get you started right away.  In the following weeks and months, I will be creating a series of Excel basics to help you in your pursuit of Excel training.  For this reason, here are 3 Top formulas that I've used time and time again to help organize and use data within Excel.

 3 Top Excel formulas

 SUM  / AVERAGE

The SUM and AVERAGE formulas do exactly as you would expect. They SUM or AVERAGE 2 or more numbers organized on an excel spreadsheet.

Formula:  =SUM(value 1, value 2) or =SUM(values from one cell through another)
                   =AVERAGE(value 1, value 2) or =AVERAGE(values from one cell through another)

What's interesting about this formula is that it can be used to sum numbers stacked horizontally, vertically, or even sum numbers with in different areas on the spreadsheet.

Let's take a look at a few examples:

The first example looks at SUM values stacked vertically.

SUM values with in different areas of a spreadsheet (not stacked).

As mentioned, SUM and AVERAGE are very similar in application. Simply substitute "Average" in place of "SUM".

And again, you can SUM or AVERAGE values not stacked. Even if the "3" was in column D, row 7 (not pictured below) one could use the same formula = average(C4, D7)

 SUMIF / AVERAGEIF

The SUMIF and AVERAGEIF formulas build upon the formulas above. Not only do they SUM or AVERAGE 2 or more numbers organized on an excel spreadsheet, they sum and average IF a certain criteria is met within the set of data. For example, if you have a list of sales reps with their monthly sales along with the state of sale; say Georgia and Florida. But what you need to do is to sum or average sales only in  Georgia,  not all states listed (your criteria) you could do so with this formula.

Formula:  =SUMIF(criteria range, criteria, sum range)
                   =AVERAGEIF(criteria range, criteria, sum range)

As an example:
What this formula is doing is looking into the range C4:C18 for the state "GA" and adding the values in D4:D18.

And again, substitute "AVERAGE" in place of "SUM".

Month / Year

The Month / Year formula which allows you to pull from a date entered into a designated field, only the month or year of that date. As a result, this formula allows the user to pivot, filter or sum based off of the month or year instead of a set of `30 dates in every month.

Formula: = Year (date reference), or Month(date reference)

Initial data set below.

And now, adding in the Year and Month formula for sorting and pivoting needs.

These Excel formulas should get you started.  If you found this helpful, let me know what you Excel formula you would like to understand or excel problem you may need help with and I'll be sure to include in a recent post as part of our Excel training!