Skip to main content

Data Management and Visualization

A visualization of data regarding San Diego Airbnbs.

The following screenshots and files are projects from a data management and visualization course taken in the Fall of 2021. Skills learned include data cleaning, table relationships, Excel lookups, and Tableau visualization.

  • Data management
  • Data visualization
  • Microsoft Excel
  • Tableau

September — December 2021

Basic Excel

Basic Excel

💾 Download this spreadsheet

This basic cost estimating spreadsheet features drop-down menus (data-validation) linked from another sheet through named ranges. Additionally, errors are caught through IFERROR() functions, preventing the user from breaking the spreadsheet.

Basic Excel sheet of cost estimating.

Contextual Menus

Excel: Contextual Menus

💾 Download this spreadsheet

The same spreadsheet as above includes another sheet which serves as a personal budget tracker. This tracker uses contextual menus (validated from tables in another sheet) with the INDIRECT() function to direct the user toward the appropriate selection. IF() and IFERROR() functions keep the sheet clutter free when no entry is present. Additionally, conditional cell formatting clarifies the state or purpose of a cell.

A contextual menu on an Excel budget tracker.

Pivot Tables

Excel: Pivot Tables

💾 Download this spreadsheet

A collection of 350+ data points from previous Cal Poly Graphic Communication students was cleaned, aggregated, and organized into pivot tables. On one sheet, I evaluate the preferred social media platforms with a filterable pivot table and associated pie chart.

An Excel dashboard with filterable pivot tables and charts.

Data Cleaning

Data Cleaning

💾 Download this spreadsheet

This spreadsheet of 5000 randomized data points from the Faker Python library includes fictitious names, titles, addresses, and contact information. All of this data was batch cleaned in Excel. In order to clean the data effectively, some techniques used were text-to-columns, SUBSTITUTE(), TRIM(), and SEARCH() with named groups.

An Excel sheet with cleaned addresses.


Excel: VLookups

💾 Download this spreadsheet

Moving from simple context menus to referencing large tables, this spreadsheet employs the VLOOKUP() function with a MATCH() to find the associated value from 20x7 table array. This requires defining the proper named ranges as menus, lookup arrays, and table arrays.

Excel sheet using the VLOOKUP() function.

Advanced Lookups

Excel: Advanced Lookups for Cost Estimating

💾 Download this spreadsheet

Previous practice with VLOOKUP(), MATCH(), and conditional formatting are put to use with this book graphic design cost estimating tool. This spreadsheet builds on the last with the addition of HLOOKUP() and conditional data using AND/OR/NOT booleans. Data from tables across four sheets can be accessed on the main worksheet through drop-down menus, context-sensitive menus, and automatically updating time and labor cost cells.

An Excel spreadsheet using VLookups and HLookups.

Tableau Prep

Cleaning and Joining in Tableau Prep

💾 Download this spreadsheet

A data set of 10,000+ rows and 14 columns were cleaned, joined with another set, and outputted to a .hyper file for use in Tableau.

Data sources being cleaned, joined, and exported in Tableau Prep Builder.

Tableau Tree Graph

Tableau: Tree Graphs

💾 Download this spreadsheet

From the above cleaned data, a tree graph was made to display the most frequently occurring publishers in the top books of the last 50 years (according to Goodreads).

A grouped tree graph in Tableau.

Airbnb Filters

Tableau: Filters on Airbnb Data

💾 Download this spreadsheet

In this visualization of San Diego, California Airbnb data, interactivity is worked in through filters. Additionally, geographic data points are used to map out a story for better understanding.

A collection of charts and maps of Airbnbs in San Diego using interactive filters.

Tableau Story

Tableau: Story

💾 Download this spreadsheet

Multiple data visualizations approaching different angles create a data story. Charts and graphs are combined in this file for a compelling analysis of the top books of the last 50 years.

A data story of the top books of the last half-century.

You've reached the end!