Follow

How can I create a report of...from exported CSV?

Everyone wants different reports for different reasons.

 

Sometimes, you might want to know what’s the overall cost of all the parts you’ve used for a month. Sometimes, you might want to know who’s contributing most to quarterly profit. Sometimes, you might want to know the total revenue coming from different marketing campaigns. Unfortunately, because everyone wants different reports, we cannot possibly build all of them.

 

In the meantime, since almost everything can be exported in CSV format (then copy/pasted into the same file), there are a few spreadsheet functions that would help you greatly:

 

VLOOKUP()

 

VLOOKUP() is useful when you’re looking for information for a certain record. For example, you have the job reference and want to know who’s the client. Or maybe you know the client name but you want to know the lead source.

 

See more here: https://goo.gl/RGyPZb

 

COUNTIFS()

 

COUNTIFS() is your best choice if you want to know how many records match the given criteria. For example, you might want to know how many jobs are in the Victoria region or have postcode 1234. Or you might want to know how many visits are assigned to user “Dan” in Feb 2017.

 

See more here: https://goo.gl/l1Efng

 

Filtering  and Ordering

 

You can reorder your data to arrange them by client name, or to only show parts that are chemical solutions. You can then take what you want and make calculations accordingly.

 

See more here: https://goo.gl/fBLJ6V

 

Pivot Tables

 

The honest downside is that pivot table is complicated and sometimes hard to understand. On the other hand, it is probably the single most powerful spreadsheet functionality of all - it allows you to use spreadsheets like a database. You can filter, arrange and sum data based on given criteria. You can automatically calculate the profit rate for all the charges used by employee “John Doe” in March 2017. As long as you have the data in the file, pivot tables can almost do anything (not exaggerated) when it comes to reporting.

 

See more here: https://goo.gl/rd22au

 

Notes:

  1. There might be differences between spreadsheet programs like Microsoft Excel, Apple Numbers, Google Sheet and other open source options, we only use MS Excel ones in this article.
  2. The above are only the ones directly related to most reports, there could be other better options depending on what you’re analyzing
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk