Last week, someone asked me if there is a menu command for moving the fields in a pivot table layout. For example, if the Region field is in the Columns area, can you use a command to move it to the Rows area, or do you have to drag it with the mouse, in the PivotTable Field List?
Excel Roundup 20150601
In this week’s roundup, fix drop down lists that allow invalid entries, create an interactive chart, get a free Excel book on Kindle, and much more.
If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please let me know. Thanks!
Excel Drop Down List Allows Invalid Entries
Drop down lists make it easier to enter data on a worksheet, and they help ensure that only valid items will be entered in the cell.
For example, in the screen shot below, the Manager column has a drop down list with 5 names. Only those names should be entered in the column.
Continue reading “Excel Drop Down List Allows Invalid Entries”
Excel Roundup 20150525
In this week’s roundup, see how to reverse last and first names, pin a shape to a chart point, learn some Solver history, and more.
If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please let me know. Thanks!
Counting in Excel
The most popular function in Excel is SUM, based on my experience. That’s probably the first function you learned how to use, and in the old days, it was the only function that you could insert with the appropriately named AutoSum button.
Sometimes we overlook how much counting we have to do every day too. Here are a few scenarios that you might encounter at work (or should I say “en-count-er”?):
- Sales: number of orders for a specific product, from a list in a worksheet
- Human Resources: count of employees with 10+ years employment
- Shipping: number of shipments pending, that do not have a zip code
Fortunately, your favourite spreadsheet can help – there are many different ways to count things in Excel.
Excel Roundup 20150518
In this week’s roundup, see how to choose a chart type, check formula speed, work with pivot table source data, and many more tips.
If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please let me know. Thanks!
Pivot Table Source Data Articles
The key to success with Excel pivot tables is having good source data. I’ve written many articles with pivot table data source tips, and this list will help you find the information that you need. Some of the articles are on my Contextures website, and others are on my Pivot Table blog.
Excel Roundup 20150511
In this week’s roundup, choose a file format, get an Excel book sampler, fix dates, and build an add-in.
If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please let me know. Thanks!
Pivot Table Running Total Percent
At the beginning of every month, I download the previous month’s statistics for my web site, to see which pages and files were the most popular. After the data is imported to Excel, I use pivot tables to get a quick overview of the activity.
If a page is getting lots of hits, I might add a new section to it, to make the content even better. And, seeing which sample files get downloaded most frequently gives me ideas for creating new examples.
I’m sure you do something similar for your month end, to see how things are going. And the raw statistics are interesting, but with a pivot table (or 12), you can dig even deeper into the results.
Excel Roundup 20150504
In this week’s roundup, look for formula inconsistencies, save time with keyboard shortcuts, use online Power BI and many more tips.
If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please let me know. Thanks!