Excel 

intro concepts

of cleaning data

Week one consisted of an introduction to cleaning data on excel in order to make a comprehensive mailing list.

​

• Name manager was utilized to clean honorifics of names.

• Data Validation was introduced to ensure the proper value was used. 

• Text to Columns was used in order to split columns. 

• Flash Fill was used to recognize patterns and apply it to the rest of the data. 

• Filtering and Sorting tools were used to ensure that all data was properly cleaned. 

• Trim tool was used to ged rid of extra spaces in the raw data.

sumifs and vlookups

Week 3 introduced the concept of Sumifs and vlookups for ink cost estimating. Zoning in on the complications when using a vlook up with variable rows and colums.  

​

• Vlookups utilized to locate dependent variable for differnt material and ink uses were written into many of the cells. 

• Table arrays, lookup arrays, and menus were first introduced in this lab.

• Using Match Statements was valuable in locating correlating information with unknown column numbers. 

• SUM if statements were utilized in order to apply a SUM to a particular range of values. 

Calculating Cpm and profit
 

Sampled after a real digital mareketing specialist test used in an interview, concepts of digital advertising such as CTR, CPC, conversion rate, CPA, and Profit per impressions were applied to find the CPM and profit.

​

• Given a particular problem set with CPC, CTR, conversion rates, and profit per sale we were asked to find the cost of the ad and the profit after considering the ad cost.  

• Using basic calculations of multiplication and division within excel, the unknown variable were easy to calculate. 

INtro to calulating costs of production

Week two focused on tools within excel used to estimate and track costs of production. 

​

• Data Validation ensured certain fields could only be one of the designated stock type along with the pricing basis. 

• Basic If error handling was used in order to call attention to areas that were filled out incorrectly. 

• Basic calculation cells using already entered values with the sheet were used to do specific price estimates. 

Advanced vlookups and conditional formating
 

This spreadsheet tracking a companies job order for a printing company. This tracted customer informations, items and jobs ordered in association of the project, and price estimation. This cemented skills pertaining to advanced vlook ups, conditional formating, and basic calculations with nested if statements. 

​

• Multiple table arrays were created along with accompanying lookup arrays and menus that were specific to production designs of front and back cover, design hourly rates, and production hourly rates. 

• Vlookups often used MATCH statements in order to reference corresponding information for the contextulaized job.  

• Calculations with built in error handling ensured that easy calculation and signaling if there was missing information in a row. 

• Data validation was used to make certain cells a drop down menu that was used to accompany Vlookup factors. 

• Conditional formatting was built in to highlight cells red with corresponding message, such as "Qty?", when one area of the row had information but the other cell did not. 

creating pivot tables

This project using a data set of student preferences allowed for the making of different pivot tables comparing different categorial and numeric data points. ​

• Pivot tables allow for specific factors to be compared within big data sets to draw connections between them

• Filtering abilities in the charts allow for a more specific viewing of data 

• Pivot splicer tool allows for the quick viewing of specific and interactive data 

• Grouping through Pivot Analyze allows for grouping of repeat data easily without cleaning it.