• Facebook - Grey Circle
  • Instagram - Grey Circle

December 13, 2018

November 3, 2018

September 28, 2018

July 20, 2018

Please reload

Recent Posts

I'm busy working on my blog posts. Watch this space!

Please reload

Featured Posts

Spreadsheets and Data Mining

October 10, 2018

 

 

I LOVE spreadsheets. They are my absolute go to for anything data!

 

There are obviously fantastic accounting software tools out there but if they can’t export to a spreadsheet, I am not interested.

Yes, the software is designed to be everything you need (and it is), but sometimes you need to dig a little deeper and mine the data to be able to properly understand what you are being presented.

 

Not to mention, that unless software has been developed specifically for your organization, there will likely be additional information that needs to be stored or worked externally.

Budgeting is a common task that is done external to accounting software and spreadsheets work perfectly for that.

 

 

 

Why is it that whenever data is exported, it never includes all of the information? Often, there is a need to reference something from another export or location in order for it to make any meaning or sense.

 

Spreadsheets are THE BEST for this!

 

Vlookup and Hlookup formulas allow data to be pulled from difference places and combined to increase usefulness and get everything in one place for ease of visualization.

 

Other formulas aid in the view or format of data:

CONCATENATE joins strings of texts together

ADDRESS can combined multiple address fields in to one text string

TRANSPOSE can convert information in a vertical column in to a row.

 

The list goes on…. The formatting options available are endless and the ability to manipulate the data for the correct use is priceless. Pivot tables, graphs, charts...

 

Conditional formatting can provide visual aids making the meaning and representation of the data jump off the page and instantly obvious to the reader.

 

Sorting (ascending or descending) can also help provide a structure for prioritizing lists and focusing data to prevent getting overwhelmed by data overload.

 

So many options!

 

  

And then there’s data mining and filtering. How important is this? Well, how important is it to understand your data and make use of it…. Very! What is the point in having data if you can’t sort or use it?

 

Filtering data in the correct order can have a huge difference in the results you produce. Always make sure that when you filter on one criterion, you are not losing needed selections from another.

It’s really important to make sure you understand the relationships and are filtering in the correct order.

 

Be careful of those ‘(Blank)’ lines too. Just because a cell is blank in one column, doesn’t mean it doesn’t have data in another!

And always make sure any other blank rows are removed before filtering. Leaving these in means the filter will only work down to the first blank row and then the remainder of the data won’t be included.

 

There are also formulas to help with data mining and increasing it’s usefulness.

IF formulas can be used to check the logic of information or to change chosen criteria to be more helpful.

 

 

I have worked with spreadsheets for SOOOO long!

From data entry to complex formulas and formatting, I love them!

 

If you have any spreadsheets you need help with, please let me know, it would make me so happy.