Sharon asked: how do I assign people the correct number of days off?
Employees receive a number of Paid Days Off based on how long they’ve been with the company. In this tutorial we use the functions:
YEAR
YEARFRAC and
VLOOKUP with True
(NOTE: the features in this video are not available to Mac users or PC users with Excel 2003 or 2007)
A common blunder in spreadsheet development is the separation of data into categories.
Stop it!
When you have 12 tabs for each month and want to know how Q2 went for a particular office, you’ve got a lot of formulas to write, test, troubleshoot, tweak and manage. HOWEVER …
When the source data is centralized in one location, we can access anything we need.
This video is long, yes it is. But you get to see the value in keeping your data contiguous. Over these 20 minutes we build a simple interactive dashboard, choreographing multiple Excel features into the most beautiful thing you’ve ever seen. Features included:
Pivot Table
Pivot Chart
Logarithmic Axis
Slicers
Slicer settings
SUMIFS
VLOOKUP
Table
Find/Replace
If you have massive spreadsheets and separated categories, spend the 20 minutes and see if you can save some time.
Logarithmic, Scales, Combo Charts & Secondary Axes.
Often we get data that is hard to analyze because the range between the data is huge. Here, we look at 2 examples:
1. City populations that range from 300 to 614,000
2. Volunteer hours that range from 1.5 to 91
To create meaningful views, we use logarithmic scale, combo charts and a secondary axis. All are in excel and ready for your use.
This video accompanies the blogpost: http://datascopic.net/excel-data-model/
Today we have 3 sets of data and 7 data fields related to website users and their activity..
1. User ID, User Type
2. Personal ID, Name, Profession
3. Log-in Date, ID, Time Spent Logged In
What do we do when we want to look at correlations between 2 or even 3 of the datasets? It’d be nice to know if certain professions weren’t using the site much. But Time Spent and Profession are in 2 different datasets.
VLOOKUP would help us stitch this together. But let’s try something new (and easier) in Excel 2013:
– Table Relationships
– Data Models
Check out the video and see how. Afterward, you’ll be a Data Superhero wearing your underwear outside of your pants!
It’s always a student who asks a question that I hadn’t thought to ask.
“So, Oz, since True is approximate can I use it to match Ann Giamatti and Anne Giamatti?”
No! That’s a good intuition but VLOOKUP w/True is used for categories and tiers. Let’s look at it this way:
We’re grading students where 90+ is an A and 80 to 89 is a B.
89 is “approximately” 90 so, shouldn’t an 89 be an A instead of a B?
With True, we help VLOOKUP by sorting our list in order and then making clear categories, tiers, strata, compartments, sections, etc.
Details on the VLOOKUP w/True and the Binary Search Algorithm:
Please subscribe and leave comments, requests or questions. Also, check out my blog at: http://datascopic.net/
In this example we see the AGGREGATE function in this video and use it for managing a list of members and guests.
Challenges: we have the list of members and number of guests, and then people start saying they aren’t attending. GEE WHIZ! And then our row numbers get screwed up.
Solution: use AGGREGATE and hide the rows when people un-RSVP.
Thanks to Mr Excel, Bill Jelen, for his video on numbering un-hidden rows. (http://www.youtube.com/watch?v=M8jEX3HFxus) That pulled me out of a fire.
Please subscribe to this channel. I’ll be putting up more videos on Excel and an occasional video on sriracha. Life isn’t right if you’ve got messy data and no sriracha.
Ask questions, leave comments, let me know if you’ve got cool stuff to share, and visit my blog. :-) http://datascopic.net
NOTE: Data Explorer is now called Power Query
We want a simple bar chart showing U.S. Governors, by party, and the year their terms end.
To do this, we use
1. Excel’s Data Explorer Preview
(a free Microsoft AddIn for Excel 2010, 2013. Sorry, Mac users. This is another feature that’s only on the PC side. Download it here:http://www.microsoft.com/en-us/download/details.aspx?id=36803 )
2. Pivot Chart
3. Group details in the Pivot Table
Please subscribe, leave comments and check out my blog at http://www.DataScopic.net
This is a common problem:
“I received data that’s in a long column but it needs to be in rows that can be sorted.”
To get the data arranged properly, we look for patterns. One pattern is that there’s a space before every new entry. From there, it’s easy. The video shows how to use IF statements the the OFFSET function to get the data the way we need it.
Please comment or ask questions if you need clarification or have a request for other data/Excel challenges.
Course is being given 18MAY13 in Chicago
For details & registration: http://bit.ly/11N66qW
Learn by doing! If you want to learn about wood, woodworking tools, different types of nails, etc., why not build a fancy cabinet? That’s the idea for Excel Uncensored
What you will get from Excel Uncensored:
– If you haven’t done any VBA or macros, you will get a small taste.
– Learn to troubleshoot.
– Formula triggers: keep formulas dormant until there is complete information to calculate.
– Learn to plan your spreadsheet. Some things require thinking 3 steps ahead. Don’t get to step 7 and realize that you must first un-do steps 6, 5 and 4.
– COMFORT with Excel.
– Deeper insight into what Excel can do.
– Integrate your skills into something functional.
Everything you learn in these 5 hours will help you go forward being able to develop solid, organized spreadsheets. And you might suddenly be your community’s Excel guru