(Excel 2013: Slicers w/Tables) Slicers to determine trip based on time and budget

/in /by

A travel agent presented this challenge because she was always struggling when a client wanted to go on a trip to a specific destination but when she’d look it up, the client either didn’t have enough money or didn’t have enough time.

She asks:
“We always know the time needed and the budget for each destination. Can I use Excel to look at the budget and time available first, then pick from the remaining destinations.”

YES!

Here is an Excel 2013 solution using Slicers with Tables.

Excel & Data Management

Excel demo: retrieving current and historical stock data from the web.

/in /by

Excel VBA code programmed to estimate the next result of selling a stock based on Current price, estimated purchase date, number of shares, estimated tax exposure.

The script goes online to retrieve the current stock price, the stock price from the estimated purchase date, and calculates the estimated net

Tying a Bow Tie

/in /by

Oz shows how to tie a bowtie and reminds gentlemen that clip-on and pre-tied bowties are unacceptable if you’re over 12 years old.

Unfortunately, good self-tie bow ties are hard to find. Many of mine were custom made. The other were found on eBay.

Excel Data Validation: Why It’s Important

/in /by

slicers and pivots to determine trip destination

/in /by

(This solution requires Excel 2010 (since we use slicers, this isn’t available for any Excel for Mac versions)

Pivot Tables as a Diagnostic Tool

/in /by

This is a companion to a blogpost for EmeryEvaluation.com the full blogpost can be found at: http://emeryevaluation.com/2013/03/18/ozdusoleil/

Here, we expand the use of pivot tables beyond data analysis. Today, we received a report and out first action was to drop it into a pivot table to investigate the data quality … which turned out not to be so good.

Please have a look and share your comments.

Channel Trailer

/in /by

CSV to clear merged cells in Excel

/in /by

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 and all formatting–merged cells, bold font, colors … everything!

Keeping Crap Data Out Of Our Dataset: Demo of Excel Data Validation

/in /by

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, protected cells, protected sheets, cells that can only accept integers between 1 and 12.

I don’t go into the how-to detail. The goal here is to show the uninitiated what can be done. There are plenty of how-to tutorials around the internet. Sometimes we just don’t know what’s possible..

You can find me at: http:DataScopic.net