Someone recently had a huge challenge. He wanted to do analysis on a spreadsheet that was formatted for people to read. It wasn’t set up for data crunching. When we try to sort or add a pivot table we learn that there are merged cells all throughout the document. Save as CSV to destroy any […]
This is a demonstration of what’s possible with Excel’s data validation. Too often spreadsheets have no layer of protection so, the doors are open wide for crap data to enter our datasets. We’ve got to stop being naughty and close those doors Here, I show 3 spreadsheets and demonstrate various protective methods: dependent dropdown lists, […]
Pleasantly surprising. As a lover of sriracha, I had to try these out if for no other reason than to say that “yes, I’ve tried them.” There’s some effort that’s been put in here. After a few chips, I have that warm tingle in my mouth. So, I’m not disappointed like I thought I would […]
UPDATE: Mac users have Slicers as of Excel 2016 New in Excel 2013, a wonderful analytical tool: using Slicers with Tables to analyze and/or filter data sets. Slicers could be used with Pivot Tables and Pivot Charts, and Excel 2013 takes it a step further. EXCELLENT!
Here’s an example of using True in a VLOOKUP because an approximate match is required. We have a list of dates, and the company uses custom start and end dates to mark monthly periods. Here, “June” is officially 26MAY thru 24JUN. In order to get 26MAY to show up as June, we use: =VLOOKUP(A2,$G$2:$H$13,2,TRUE) IMPORTANT […]
Oz du Soleil samples Habanero Ghost Pepper Cheese from Jim’s Cheese Pantry This is a fantastic cheese that balances the heat of the peppers, makes excellent use of the ghost peppers inside of a cheese that stands up and matches well with the peppers. An all-around excellent experience. Add some red wine, and we’ve got […]
Blogpost associated with this video: http://datascopic.net/generate-data/ Often we need to generate a set of bogus data in order to create examples or test solutions. In this video, I show how to generate fake call center data in order to show an example of a pivot table. This includes: RAND, RANDBETWEEN, date formatting, Find-Replace
Simple use of Excel Tables
Quick video to show the use of Excel tables, and how a table can be compromised.