Data Portfolio

This simple spreadsheet demonstrates fundamental concepts from week 1, including:

• Data Validation: the ability to limit values that can be entered into a field, in this case limited to a list, which creates drop down menus. 

• Name Ranges: referring to a block of cells by a single name. 

• IfError Handling: providing meaningful feedback to users when value entries result in an error. 

• Data Validation: required all inputs to be a number that was greater than zero, and it also had to be a whole number. 

Download excel file here. 

This spreadsheet was created and used to manage a budget:

• Conditional Formatting: Under the current balance column (E), I formatted any income to change to green text and a highlighted green box. If the input were to be an expense (from any other category), it would be red text. 

• Dependent Menus: Column C could not be filled out unless Column B had an item selected. To do this, I used INDEX in my formula. 

• Balance updating: Used IF statements to either add or subtract from the current balance keeping each row updated as expenses or incomes are inputted. 

Download excel file here. 

This spreadsheet focuses on data lookups - with both variable rows and columns:

• VLOOKUP: Used to determine ink mileage and ink coverage. 

• Tables: Created tables and table arrays by selecting data and renaming them to create groups. 

• MATCH: Used to connect and show data from tables and table arrays. 

Download excel file here. 

This spreadsheet with pivot tables was created using data compiled over multiple quarters of GrC students taking this survey: 

• Comparisons: Used to sort and filter data, I created pivot tables to compare eye and hair color, social media preferences per year, etc. 

• Sorting: Sorted out outliers and irrelevant or too specific of information that did not work with the data set. 

• Organization: Organized data without the need for long formulas and equations. 

Download excel file here. 

This spreadsheet is used for estimating costs and materials for book design. Published in Graphic Arts Guild Handbook of Pricing and Ethical Guidelines. 

• VLOOKUP: looked up values, and given other options if values do not work. Used lookup_arrays and table_arrays.

• Conditional formatting: Blank spaces that have other pieces of the row filled out will highlight red, also warns for errors. 

• MATCH: uses table arrays to match hourly rates to job functions. 

• NOT(ISNUMBER): used for error handling and to aid in conditional formatting. 

Download excel file here. 

Using Google Analytics: 

• Filtering: Filtered data by date to find how many users were on the site at any given time. 

• Demographics: Used sessions by country, area, and time, to learn more about the site users. 

• CPM and Profit: Calculating revenue, average order cost, highest selling products, ROAS (return on ad spending), etc.  

Download file here. 

Using a .csv file on APPL stocks, created worksheets to visually organize data: 

• Formatting: Filtering dates, data points, editing axises, adjusting colors and labels to create a clean, organized visual to tell the story of the data given. 

• Dual Axis: Merged two line graphs into one, to compare values showing different, but related information. 

• Dimensions & Measures: Formatting data rows and columns to either be dimensions or measures and adjusting them to SUM or AVG accordingly. 

Download tableau file here. 

This spreadsheet focuses on data lookups - with both variable rows and columns:

• AND/OR/NOT: using logical error handling statements to format certain columns and certain sections within those columns to return to the first blank value and replace with correct message. 

• VLOOKUP: finding specific values from previously created table arrays

• Conditional Formatting: to turn certain cells colors depending on if a value was missing, or the value number. 

• Conditional Formatting: to keep minimum hours to a zero and if zero, highlighting blue. 

Download excel file here. 

This spreadsheet is used for calculating digital marketing metrics:

• CPA/CPM: using information such as click-through-rate, units/M, and cost-per-click to calculate marketing data. 

• Conversion rates: using conversion rates to determine advertising costs and the most beneficial uses of money for the outcome. 

• Profit per order: determining profit based on calculated metrics. 

Download excel file here. 

This workbook includes multiple sheets, dashboards, and a story to combine data all in one location to provide visuals to the data story being told:

• Mapping: Used latitude and longitude data to create maps, that were then formatted to calculate the distance from a certain location. Depending on the distance, the color would change, and depending on price, the size of the dot would change. 

• Filtering: Certain graphs and maps have interactive filtering capabilities so that the user can interact with the data and understand it better. Specifically for the map, you can slide a bar and change the range of data shown based on distance to a specific location. 

• Graphing: Creative ways to display data such as heat mapping based on price and bathroom count per air bnb, tree maps,  line graphs, etc. 

Download tableau file here. 

This project combined many of the previously learned skills throughout my time in GrC 404: 

• Tableau Prep: Used this software to clean and join large data sources to create tables that were useful for my story.

• Graphs: Organized and created meaningful graphs that were aesthetically pleasing as well as told an accurate story. 

• Filters: interactive filtering capabilities for certain graphs to change the price range, or fields represented in a graph that update as you change them. 

• Tableau: Created worksheets, dashboards, and stories to display data. Connected and joined data sources, as well as wrote supplemental information to support the graphs. 

Download tableau file here.