This offer has expired.
Please see the Contextures Recommends page for Excel training recommendations.
This offer has expired.
Please see the Contextures Recommends page for Excel training recommendations.
Are you an Excel Dashboard pro? If not, here’s a great opportunity to improve your skills. I’m having a giveaway, and a terrific new Excel dashboard course is the top prize. There are runner up prizes too, and a free video series.
There are a few upcoming Excel-related events that you might be interested in. These are online sessions, and all of them are free.
You can read the details below, and watch for the availability dates and deadlines.
Last week someone sent me a lovely email, describing a young woman’s career beginnings. They said I could share the story with you, but asked to remain anonymous.
Maybe your career began in a similar way. Did you start out with an Excel career in mind? Did you struggle at first, and did Excel help you move forward? Did you help someone else get ahead, by giving them a few Excel pointers?
Here’s the story from the email:
My daughter had a very rough first year of work after completing a marketing degree, basically having to act as a receptionist and assistant dogsbody to lots of people.
Then she landed a job at a small firm where she found they were doing things extremely manually. I introduced her to pivot tables, and with a bit of help, she has completely transformed their spreadsheets so they can do in minutes what took them days and weeks, without any errors.
The best part is that her new skills and spreadsheets have, for the first time, given her something she can call her own, and respect from her colleagues. She is fiercely proud of her work, and keen to learn more. What a transformation!
It’s hard to imagine that pivots could be girl power, but here they were.
Did you know what a dogsbody was? I’d never heard that before, but Wikipedia explained that it’s someone who does grunt work.
It’s great to see that people are getting recognition for their Excel skills, and saving time, and reducing errors in their work.
If you’re trying to learn more about Excel, but don’t have a free consultant at home, you’ll find lots of free tutorials here on this blog, on my pivot table blog, and on my Contextures website.
If you prefer to learn from a book, there’s an interactive Excel Books list on my website. You can sort and filter the list, and download an Excel file with the data.
And if you want to invest in an online Excel course, I recommend these:
My clients sometimes ask for help with building Excel dashboards, so they can present a summary of their data to their customers and co-workers. In a dashboard, you want to make the best use of limited space, and only show key information. For example, instead of showing all the sales data, you can show just the highest and lowest values.
It’s easy to pull the top and bottom values from a list, by using the MIN and MAX functions. It’s a little trickier though, if you want to show the high and low amounts for a specific product in a long list. In this example, I want to calculate the MIN and MAX for each product, then put that information on the dashboard
There’s no built-in MINIF or MAXIF function, but you can use MIN or MAX with the IF function, to create your own. The steps are shown in the video, at the end of this article.
First, to get the minimum quantity sold for File Folders, the array formula in cell D8 is:
After you type the formula, press Ctrl + Shift + Enter, so it is array entered.
The same technique is used in cell E8, with MAX, instead of MIN:
If you’d like to add dashboard skills to your Excel tool kit, I recommend the upcoming Excel Dashboard Course offered by Mynda Treacy from My Online training Hub. Mynda is an accountant, and her dashboards focus on the numbers, not the fluff.
There are 9 sessions in the course, with video tutorials that are short and to the point. They cover the key steps and features, and you can practise the techniques in the sample files. Replay the videos as often as you need, for up to 12 months. The course includes 6 weeks of support from Mynda, so you can post questions, read comments, and ask her to review your completed dashboard.
This course is not for Excel beginners, because the fast pace could be overwhelming. Lots of material is covered, very quickly. And, if you’re already a dashboard expert, you won’t need this course. It’s designed for Excel users who are beyond the basics, and who enjoy learning by seeing a demo, then practising the new skills.
You can see the course details and a sample video here: Excel Dashboard Course
Registration is only open for two weeks, until August 14th, and if you sign up early, you can get a 20% discount. Check the sign page for the deadline dates.
To see the steps for creating MIN, MAX, MIN IF and MAX IF formulas, please watch this short video tutorial.
They didn’t ask for my advice, but Mike Alexander and Dick Kusleika need a poster for their upcoming Excel and Access Power User Workshop. The event is in Chicago, and who better represents that city than the Blues Brothers? Your Photoshop skills are probably better than mine (non-existent), so here is my attempt at morphing Mike and Dick into The Excel Brothers. (Sorry Mike, I had to widen your face a little!)
I’ve spent many hours with Mike and Dick at Microsoft events, and they are both extremely knowledgeable, and highly entertaining. If you can make it to Chicago next month, and want to power up your Excel and Access skills, I highly recommend signing up for this workshop.
The two day workshop runs Wednesday, May 18, 2011 – Thursday, May 19, 2011, and the schedule is jam packed with sessions that will make your Excel/Access skills even more amazing than they are now.
Bring your laptop to the workshop, so you can follow along, and hone your skills, while Mike and Dick guide you. Here’s a summary of what you’ll learn in the sessions:
And of course, when you attend a power user workshop like this, you’ll learn even more during the breaks and informal sessions, by chatting with Mike and Dick, and your fellow attendees.
There are still a few days left to register and get the Early Bird savings – a $100 discount. The price goes up on May 1st, so check it out now, and save some money.
I’ve been to Chicago a few times, and it’s my favourite city in the USA. Granted, my experience is limited – I’m comparing it to Atlantic City, Orlando, Buffalo and Seattle. If you register for the Excel and Access Power User Workshop, add a couple of days to your trip, and spend the weekend doing touristy things in Chicago.
During a visit last fall, I saw the Blues Brothers’ police car – stalled in the middle of The Magnificent Mile!
The Chicago Mayfest is celebrated May 20-22, and it sounds like fun. Who knows – you might run into Ferris Bueller!
The 17th annual Chicago Mayfest brings in three days of celebration – featuring; Chicago’s BEST Bands, Festival Cuisine, Maypole dancing, pretzels, beer, artisans, and a broad spectrum of super cool entertainment.
Just tell your boss that you want to get to Chicago for some Excel/Access training, and Maypole dancing. Who could argue with that?
Someone emailed me this week, and asked how he could improve his Excel skills.
Here’s what I suggested:
What would you add to that list?
On the Consumerist website last week, they posted Lauren’s Excel budget template, so I downloaded it, to take a look. I’d call it an Expense Tracker, rather than a “Budgeter”, because it’s used to record income and expenses. (Do you know the origin of the word “budget”? I had to look it up.)
Here’s what it looks like, with part of the formula for the Total cell showing in the formula bar. The grey fill colour is added with conditional formatting.
Shown below is the full formula for the Total. You can see that Lauren has named the date headings (_8_10d) and hidden total row (_8_10) for each month.
Wow! It makes me tired just looking at that. Lauren created a lot of named ranges, to set up the file, and she’ll need to do more work to add more months. Because there’s a separate section for each month, her formula needs a SUMIF formula for each range. She might have to upgrade from Excel 2003, or she’ll pass the character limit for that formula.
I don’t know who Lauren is, but she should be commended for setting this up, and keeping track of her income and expenses. Sure, there are many ways to improve her Budgeter, but it seems to work okay, even if it is a bit convoluted. At least she knows where her money is going!
But, there must be better ways to keep track of income and expenses. How would you set up an Excel workbook to do this?
I’d probably create a simple list, with columns for Date, Item, Location, Category and Amount. The last column calculates the year and month, so it’s easy to summarize by month. You could even get fancy, and add data validation to the Category column, with a drop down list of valid categories.
Enter all the items, then create a pivot table to summarize the spending.
A short reminder that Chandoo has opened a new session of his online Excel School. It has many new features, including a live webinar, and a full year of access to the course materials. Registration closes on September 29th, so check it out today, by clicking this link to Chandoo’s Excel School, or click the image below.
If you’re doing Excel training, or writing blog posts, or creating sample files, you might need to find some public data to use in your Excel file. You don’t want to publish confidential client data, even if it’s a bit disguised, or reveal your household finances to the world.
Instead of creating sample data on your own, you can find datasets online. Once source is Google Public Data Explorer (part of Google Labs), where you can access several large datasets, and use those in your Excel example files
When you follow the links to the dataset pages, you’ll find interactive charts and maps, and links to the source data websites.
Of course, each source data website is set up differently, but on some of the source data websites, you’ll find more charts and interactive tables, and files that you can download.
For example, you can download Excel files with economic, environmental and social statistics from the OECD Factbook 2010, published by the Organization for Economic Co-operation and Development.
To see how it worked, I downloaded an Employment rates file with several years of data, for three age groupings. That would give you enough data to use for your own sample charts or training exercises.
Are there other good sources of public data that you use? Please share them in the comments. Thanks!
Do you need to take your Excel skills up a notch (or several notches)? Excel and charting expert, Chandoo, from chandoo.org, has opened another session of his online Excel School. Registration is open until June 21st only.
Would it help you learn more about Excel? Is it right for you?
Update: Chandoo has also started an Excel VBA school and registration is ongoing. You can sign up now, and do the online classes at your own pace.
I can’t be completely unbiased, because Chandoo interviewed me for the Pivot Table Tricks section of his first Excel School, so I know that section is truly awesome! 😉 Also, I’m an affiliate for chandoo.org, and earn a commission if you register through a link on my site.
However, there’s no point in your registering for a course, if it doesn’t cover the material that you need, or if it’s not presented in a way that suits your learning style. Let’s look at the Excel School content and format, and see if it’s right for you.
How much do you know about Excel?
If you’re really new to Excel, or have advanced skills in most Excel topics, you’re not the target audience for Excel School.
If you’re somewhere in the middle, there’s lots to learn in Excel School, including Advanced Charting, Conditional Formatting, Advanced Formulas, Macros and Pivot Tables.
The lessons in Excel School are presented in video format. Chandoo demonstrates the techniques in Excel, as you follow along. Most lessons have a sample workbook that you can download, for use during and after the lesson.
There are also discussion sections for each lesson, so you can ask questions and post your comments.
I checked the prices of classroom training in the Toronto area, and a one-day Excel course (6 hours) is currently about $275 CDN. On top of that, I’d have the commuting time and cost of driving into downtown Toronto, and parking for the day. Also, in a classroom, you can’t skip over the topics you already know, or mute the annoying guy beside you!
So, you’ll save time and money if you opt for online Excel School. There are two registration options:
The content is the same for both registration options. The big advantage to opting for Online + Download is that you can download HD versions of the Excel School videos, and review them at your leisure, long after the Excel School session ends (August 28th).
Remember — registration closes on Monday, June 21st.
Update: In Chandoo’s Excel VBA school you can sign up for a package that includes Excel School, and you could even add the Excel Dashboards class, and become an Excel master at your own pace. Registration is ongoing.
If you’ve decided that Excel School is right for you, or want more information, head over to the registration page for Excel School. Chandoo has a list of Frequently Asked Questions, and more details on the school and sign-up process.
And when you join the Excel School, be sure to watch my awesome presentation on Pivot Table Tricks!