|
|
By Debra Dalgleish, on May 23rd, 2013 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?
The Story
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.
Go pivots!
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.
Excel Resources
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:
Excel Online Course by Mynda Treacy, at My Online Training Hub
Excel School by Chandoo, at Chandoo.org
Excel VBA School by Chandoo, at Chandoo.org
____________
By Debra Dalgleish, on May 21st, 2013 The SUBTOTAL function is great for calculating totals on a filtered list in Excel. Unlike the SUM function, SUBTOTAL ignores the values in rows hidden by the filter, and can even ignore manually hidden rows, so the total includes only the visible cells.
A new function, AGGREGATE, introduced in Excel 2010, is similar to SUBTOTAL, and has a couple of advantages.
More Functions in AGGREGATE
The first advantage is that AGGREGATE has 19 functions, compared to SUBTOTAL's 11 functions.
Ignore Errors with AGGREGATE
Another advantage is that AGGREGATE can ignore errors, as well as hidden rows. You can
Continue reading Sum a Filtered List with AGGREGATE Function
By Debra Dalgleish, on May 16th, 2013 Earlier this week, I copied a list of Excel keyboard shortcuts from the Microsoft website, and pasted it into Excel. Then I noticed that a few objects had come along with the shortcuts list.
In the screen shot below, you can see one of the tiny icons that you can click, to go to the top of the web page. Those are handy on the web page, but aren’t much help on the Excel sheet.
See What Objects You Copied
Sometimes these objects are easy to spot, but if they’re small, or if there are lots of them,
Continue reading Quickly Clear Objects from Worksheet
By Debra Dalgleish, on May 14th, 2013 Pivot table filtering was improved in Excel 2010, when Slicers were introduced. Instead of using the drop down lists in the pivot table headings, you can click on a Slicer, to quickly filter the pivot table.
Slicers take up some room on the worksheet, but you can quickly see what filters have been applied. And, unlike Report Filters, Slicers show you what is available in the other fields, after you have applied a filter.
Slicers for Excel Tables
In Excel 2013, Slicers were enabled for named tables too, so you can filter your data with a single click.
Continue reading Use Slicers to Filter a Table in Excel 2010
By Debra Dalgleish, on May 9th, 2013 There is a new sample file on my website, in response to a lookup question that someone asked on my Contextures Facebook page.
I'm staring at a huge spreadsheet showing the distances in miles between a few hundred job sites…Our data is accurate, but the users often enter the wrong mileage data because it's easy to make a mistake when scrolling…
How can I automate this process so that I can just enter the departure site and the arrival site and retrieve the distance between the two?
The Mileage Table
To find data in a lookup table, based on the
Continue reading Get Mileage from Excel Lookup Table
By Debra Dalgleish, on May 7th, 2013 It’s amazing how all those little expenses can add up over a year. For example:
Upgrade your cable package for an extra $30 per month, and that’s $360 more per year. Buy your lunch for $15 each workday, instead of bringing a $5 lunch from home, and you’ve added $2500 to your annual expenses. Compare Expenses in Excel
Instead of ignoring those extra expenses, you can use Excel to calculate annual totals, and see what happens if you can cut costs.
I’ve created a workbook where you can compare two scenarios, and see the difference in annual costs.
First,
Continue reading Calculate Annual Costs and Savings in Excel
By Debra Dalgleish, on May 2nd, 2013 A few years ago, Ron Coderre created his PivotPlay PLUS Add-in that you can download from my Contextures site.
This free add-in was designed for Excel 2003, and lets you view and edit the connection strings for pivot tables and query tables that are based on external data queries.
Testing in Excel 2010
Several people have asked about an update, so I installed the PivotPlay PLUS add-in in Excel 2010, to test it. Instead of showing up on the menu bar, the start button appears on the Excel Ribbon’s Add-Ins tab.
If the active worksheet has a pivot
Continue reading Counting Query Tables in Excel
By Debra Dalgleish, on April 30th, 2013 If you want to insert a block of cells on a worksheet, what method do you use?
One way to insert cells is to select some cells, right-click on the selected range, and click Insert. Then you can select one of the Insert options.
Insert With the Fill Handle
This week, I was doing some updates on this blog, and found a tip that I posted 5 years ago. You can use the Fill Handle as a shortcut for inserting and deleting cells.
Select cells as a starting point, press Shift, and drag the fill handle to insert
Continue reading Insert Rows with Excel Fill Handle
By Debra Dalgleish, on April 25th, 2013 One of the new features in Excel 2013 is that each file opens in a separate window. Having each file in its own window makes it easier to compare files side-by-side, and most of the time I like the separate windows.
One thing that I don’t like is that, unlike previous versions, there is no Exit button or command, to close all the files. Each window has its own Close button, and if you’ve got lots of files open, it’s a pain to close each window individually.
Use the Taskbar Command
One solution is to use the Close
Continue reading Close All Files in Excel 2013
By Debra Dalgleish, on April 23rd, 2013 It’s been a bad week for Excel, with big news stories about spreadsheet errors. Two Harvard professors were in the news after a student at the University of Massachusetts found errors in their economic research paper.
For example, in one of the formulas, five rows of data were omitted, so the average was incorrect. It’s easy for that kind of mistake to happen, especially if you add new data, or rearrange things, and forget to adjust your formulas.
You’ve probably read all about it, but if not, here are links to my favourite articles on this debacle:
BBC: The Mysterious
Continue reading Catching Your Excel Errors
|
|
Recent Comments