How to articles for basic excel, VBA and SQL.

Sarah Schlott

Mom | Wife | FP&A Senior Executive | PE Growth | SaaS | FINTech | Supporter of Women’s equality | #Coffee addict.
,

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 micros 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!