Oz du Soleil covers some of the key features of an effective data tracker. This includes understanding input, storage and output, deciding which data to track and who you enters it, planning your outputs, and anticipating ad hoc uses for tracker.
- [Instructor] To give you an overview of a tracker and what makes one work let's explore this tracker that was originally created to track a nonprofit's direct mail campaign. They wanted to know what did it cost to raise each dollar. The tracker didn't look like all of this in the beginning. Right? We've got a dashboard over here. We got a lot of cool stuff going on. But that's only possible because of the first point I want to make. Keep data in as few places as possible.
This tracker started with data in this range. And then this range. The expenses and then we start tracking the donations as they start coming in. Alright, everything's just stacked up. And you can see the tendency would be to break down the donations as they came in by month. Have an April tab, May tab et cetera. But then we'll have problems later. And you'll see that. So the first tip, keep data in as few places as possible.
Next, everything is dynamic. Let's say that we spent $500 on fancy stationary, hit the Enter key and everything updates. Wow. Now we went from 32 cents to raise every dollar to 39 cents. Let's undo that. The third tip in making an effective tracker, build complex calculations in multiple steps.
Let's look at this 32 cents. That is tied to a shape. But I'm going to unhide this column. We've got calculations in here that help us get to the 32 cents. Also, let's look at the dashboard. We have a histogram of the donation levels in frequencies, highlighted. Where is that data coming from? Design, select Data, it's coming from a table that was setup here, cancel this, this table's data is coming from this table.
So do this in multiple steps so that it's easier to build. If we want to adjust something, it's easier to do. If something breaks, it's easier to troubleshoot. And the fourth tip for making an effective tracker, use Excel's formulas and functions to segment your data. See we could physically segment this into months, but then we'd have a problem. Let's look at the dashboard again. And notice the top five donations.
Those are the top five overall. Now imagine if we had multiple monthly tabs, we would need to build a formula that would look across all of the tabs, not make a mistake and miss one, and then extract the top five donations. But with the data all in one place we can easily write simple functions to get the data that we need. And this gets us back to the main point, when you're building a tracker, keep your data in as few places as possible.
And life will be easy for everybody, and you'll be able to access even more data than you originally planned for.
- Planning your data tracker
- Adding calculations and graphics
- Protecting cells and sheets
- Hiding sheets
- Setting up alerts with conditional formatting
- Merging data
- Categorizing data
- Formatting your tracker
- Putting it all together