Business Intelligence & Analytics for Digital Transformation

10 Excel basic formulas and functions to supercharge your reporting skills.

Recommended Posts

How to use pivot tables in Excel and that has made all the difference. It enabled me to take a series of data and analyze it in a variety of different ways rather than waiting for a programmer to create a static report. As I was establishing KPIs for my company, this helped me make sense of the data.

This really saved incredible amounts of time and I started enjoyed playing with data.

Sharing with you the ten basic formulas and functions that one can’t live without when it comes to building reports to track the KPIs that are essential to managing their programs. This isn’t meant to be a tutorial but more of a “hey, did you know that you can do that in Excel or Google Sheets?”

1. Filter

Filtering allows you to quickly sort any column and include/exclude rows containing certain data. For example, if you have a list of all of your phone calls for the month, you can use filtering to only display the calls handled by certain agents.

2. Conditional Formatting

Conditional Formatting is a great way to add visuals to your spreadsheets based on the data. For example, if you want to rank a set of data, you can have all rows within a certain parameter be one color and anything outside of that parameter another color. It’s great for seeing where you’re hitting your goals and where you might need to improve.

3 Format Painter

The format painter is a simple tool that allows you to copy just the formatting from a certain spreadsheet and quickly apply it to a new set of cells.

4. Date

The DATE function is incredibly useful in Excel. It’s a common thing to have a bunch of rows with dates but it can be difficult to group them by month. You can quickly create a column for month next to your date column by using this formula:

=DATE(YEAR(A2),MONTH(A2),1)

A2 represents the cell that has the date in it. If the date in that cell looks like 2016-04-03 the month cell will look like 2016-04-01. Regardless of the date, the month will display the first day of each month and now you can quickly filter or group in your pivot table by month.

Bonus: Once you do this in your first cell, double click in the bottom right hand corner of that cell and it will copy the formula all the way to the bottom of your spreadsheet. You don’t have to write that formula 25,000 times. Win.

5. Dollar sign

The dollar sign is an important little symbol to remember in your formulas. In the previous point, Excel has the ability to customize the formula in the cell relative to the row it’s in. Perhaps you’re doing a formula where you want to multiply the cells in a particular column by a value in another single cell. The dollar sign turns off that relativity and makes sure that the cell number doesn’t change in your formula. So your formula might look something like :

=A2*\$D\$16

If you copy that formula to the next cell in the column, the formula will look like:

=B2*\$D\$16

6. Lookup & HLookup

Learn this formula and you will really feel like an Excel wizard. This allows you to grab data from another sheet using a common identifier. For example, you may have one sheet that has all of your data for your support tickets for the month including the name of the agent who handled the ticket. You’d really like to group ticket results by the team the agents work on. In another sheet, you can create a simple, two-column sheet that has the name of the agent and the team they are on.

Once you have that sheet created, go back to your main sheet and create a “team” column. In this case you will use VLOOKUP because it’s vertical. HLOOKUP is if your data is laid out horizontally. The VLOOKUP will look up the name of the agent and match it with the name on the other sheet and fill in the corresponding team name. A formula might look like this:

=VLOOKUP(A2,’OtherSheet’!\$A\$1:\$B\$25,2,FALSE)

Note that the aforementioned dollar signs are often really useful in this formula.

7. Data Validation

Data Validation allows you to create cool things like drop down lists in a cell. Consider a time where you had to enter “yes” or “no” in a certain column or possibly a wider range of options. A drop down list allows you to quickly select from the options you want in a couple clicks rather than several key strokes and it ensures consistent formatting.

8. Sum, Average, Count

These simple functions are great to remember so you don’t have to hunt around for the function button. Add an “=” before each and you can count, add, or average any range of cells that you select. These are essential building blocks to know for the next tip.

9. IF and IFS

Add IF or IFS after Sum, Average, and Count to add some criteria to those formulas. Adding the “S” allows you to attach multiple criteria to the formula. Let’s say I have a table full of quality scores and I want to count how many perfect 100% scores my team earned. My formula would look something like this:

=COUNTIF(A1:A500,“100”)

10. IF Statements

IF statements allow you to apply logic to a certain cell. For example, if you have a list of all of your phone calls and a column that displays how many seconds each caller waited before their call was answered, you can determine how many calls were answered in less than 60 seconds. Your formula would look something like this:

=IF(A2<=60,“Yes”,“No”)

So if the caller waited 60 seconds or less, the value printed in the cell will be “Yes.” If it was more than 60 seconds, the value would be “No.”

That’s ten rather amazing tools to supercharge the way you use Microsoft Excel or Google Sheets for your  reporting and we barely scratched the surface of some of the things you can do. A rule of thumb I’ve learned with these programs is that if there’s something you’re doing manually that’s time consuming, there’s probably a formula or function that will help you do it faster

What are your go to Excel tips and tricks? Leave us a comment and let us know.

• 3
• 3
Share on other sites

• 1 month later...

Thanks Saurabh,

Can you also give some tips and uses of the pivot tables with which you start your post ?

Thank you

Emilio

Share on other sites

• 4 months later...
On 3/20/2017 at 1:52 PM, EmilioS said:

Thanks Saurabh,

Can you also give some tips and uses of the pivot tables with which you start your post ?

Thank you

Emilio

@Emilio

Regarding use of pivot table

Suppose you have your data stored in the form of excel table. Then by inserting pivot table, you can analyse it or create some summary by using table's headers as columns or rows etc in pivot table as per your choice. Further, you can add slicers and use filters to see the pivot table responding to your choices. You can also add a pivot chart based on this.

Share on other sites

• 8 months later...

Better attach complete /maximum information  for the use of it in PDF form which could be downloaded.

Edited by Ali Khan
Share on other sites

Hi,

thank you for sharing it. I would like to know if you can help me when I am working my projects. I founded my company and I have been developing some interesting projects related with our career.

Share on other sites

• 2 years later...
On 4/25/2018 at 12:15 AM, jcgringmec said:

Hi,

thank you for sharing it. I would like to know if you can help me when I am working my projects. I founded my company and I have been developing some interesting projects related with our career.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

• Topics

• 20
• 0
• 1
• 2
• 0
×
×
• Create New...