My Excel & OpenRefine Skills

"Microsoft Excel is the industry leading spreadsheet program, a powerful data visualization and analysis tool."

"OpenRefine (previously Google Refine) is a powerful tool for working with messy data: cleaning it; transforming it from one format into another; and extending it with web services and external data."

Most professionals are familiar with Microsoft Excel’s basic use for managing spreadsheets and flat, rectangular data. However, Excel has a vast assortment of tools and functions for spreadsheet manipulation that many users never venture into. Some of these helpful utilities that I have learned to use include:

• Mathematical and logical functions
• Financial, logical, text, date/time, lookup/reference, mathematical, statistical, engineering, cube, information, compatibility, and web formulas
• Advanced and conditional formatting options
• Column delimitation
• Advanced graphics
• Formulas across multiple sheets
• Pivot tables
• Advanced filtration (unique values, etc)
• Error checking
• File protections

Additionally, OpenRefine offers great solutions for data wrangling and cleaning. Using the web interface, users can import messy, difficult-to-use data, and turn it into an easy-to use, well-formatted file. OpenRefine features include filtration, faceting, clustering, data extension, expressions, regular expressions, and reconciliation. Users can also use General Refine Expression Language (GREL) to transform the data.

It’s possible to perform most of the functions offered by Microsoft Excel and OpenRefine using simple programs written in languages like R and Python, but for a quick simple fix, Excel and OpenRefine are great options.