Excel BTS Project Tracker – Dynamic Named Ranges

We’ve never met before. But I already know a lot about you. You probably work in technology, maybe in a Mobile Network RAN. You already use Microsoft Excel, and you’d like to get better at it. You are not a manager and don’t have a team of people to supervise or manage. Lastly, you want to get more out of your career.

How do I know all these things about you? I didn’t sleep at 221B Baker Street last night, but I do read and watch a lot of Sherlock Holmes stories. Seriously, I’ve shared this article to Telecoms and Excel groups, where Telecoms professionals and people trying to improve their Excel skills hang out. So you’re probably one of them.

And what about not being a Manager, was I right about that? That was easy, it’s a trick question: when was the last time you met any manager who thought there was anything they didn’t already know? Hah!

About getting more from your career, what else should I expect from someone scouring technology groups and blogs trying to learn new skills?

This article will cover one of Excel’s most useful and most difficult topics: Dynamic Named Ranges. I’ve talked about Named Ranges before, how they make your formulas easier to read and to update. Dynamic Named Ranges give you a way to automatically re-size ranges as your dataset changes. Two places where this is often done is with Charts and with Print Areas. In this article, I’ll show you have a Dynamic Named Range can automate the data displayed in your charts. The result is one less step whenever you update the Project Tracker. Continue reading “Excel BTS Project Tracker – Dynamic Named Ranges”

Excel BTS Project Tracker – New Date Format

So far in this series I’ve written 4 articles building our Project Tracker.  I’ve set up the Updates table and created progressively more complex Pivot Tables by using Lookups, Synthetic Data and Bucketization.

But let me stop for just a moment to talk about where I’m going with this.  I’ve written before about Excel Dashboards.  In Stop Monthly Reporting Madness I described a 3-step process for Excel automation: Update, Refresh, Share. This 3-step process works for Trackers and Dashboards and just about any Excel Workbook for managing a fixed dataset that accepts changes over time.  Exactly how you realize this 3-step Mantra really depends on the nature of the underlying data and your own creativity.

Generally, when I create a new Excel project, my objective is to do as much of the work as possible up front, when I’m creating the workbook.  Once completed, the workbook shouldn’t need to be changed, only data added.  The workbook just reliably does what it was setup to do.  Day-in, day-out, as I use the workbook, it is as simple and as automated as possible.  That 3-step process is always my goal: Update, Refresh, Share. Continue reading “Excel BTS Project Tracker – New Date Format”