Search Contextures Sites
Excel Weekly News from Contextures June 18, 2013
Simple Box Plot Chart + more Excel tips
In this week's Excel news, you'll see how to create a simple box plot chart, block new items in a filtered pivot field, and other tips. Thank you for reading the Excel news!
- Debra firstname.lastname@example.org
Excel Functions: AVERAGE, MEDIAN, MODE
You probably use the AVERAGE function in Excel, and it is one of the central tendency functions, along with MEDIAN and MODE. There is an interactive workbook on my Contextures blog this week, where you can see how those 3 functions work. Enter a few numbers, and see how the charts change.
Click here to test the interactive workbook: Excel Functions: AVERAGE, MEDIAN, MODE
Block New Pivot Items in Filtered Field
If you refresh a pivot table after adding new items in the source data, those new items might appear in the filtered pivot table, even though they were not originally selected.
To prevent this, you can change a setting for the pivot field.
Click here to see the details, and to watch the video: Block New Pivot Items in Filtered Field
Create a Simple Box Plot in Excel
Excel doesn’t have a built-in chart type for a box plot, but you can build one yourself, using a stacked column chart, and error bars.
Click here to see the details, and to watch the video: Create a Simple Box Plot in Excel
More Excel Tips
Here are a few more Excel articles that I read this week, that you might find useful:
- On the Daily Dose of Excel blog, Dick Kusleika uses a bit of programming so the Ctrl+Home shortcut takes you to cell A1, even if Freeze Panes is turned on.
- For a humorous peek at what other people are saying about Excel, read this week's collection of Excel tweets, on my Excel Theatre blog.
- Last week, Microsoft released an iPhone version of Office, and you can read the details on Microsoft's Office blog. The app is free, but you need an Office 365 Home Premium or Office 365 ProPlus subscription.
Video: Jump to the Next Data Entry Cell
If you're filling in a form, the data entry cells might be scattered throughout the worksheet. You'd like a quick and easy way to move through the cells, in a specific order. To do this, you can create a named range, as shown in this short video.
For more info on named ranges, please visit my Contextures website: Excel Named Ranges
Excel Expert Course
Mynda Treacy, who runs the popular Excel Dashboards course at My Online Training Hub, is opening registration for a new online course today -- Excel Expert.
This course will take you from complete beginner to expert, and if you already know a bit of Excel you can skip ahead and just learn what you need to get your skills up to date. There are a huge range of advanced formulas including array formulas as well as PivotTables in depth, Conditional Formatting tricks and much more.
For a sneak preview, you can watch the sample video, Troubleshoot Excel Formulas.
Once you complete the course you'll be able to confidently list 'Advanced Excel Skills' on your CV/Resume. No more wondering if you have the skills, you will know you do. You will also get a certificate of completion to include in your resume arsenal.
The Excel Expert course has 27 sessions, with beginner to advanced topics. Each tutorial has been categorised into levels; beginner, intermediate and advanced. Check out the syllabus and see what level you are.
Sign up by July 3rd, and receive a 20% discount. After that date, the course will be available at the full price.
My Excel workbooks don't have too much colour in them, but the garden is full of bright flowers, like this clump of Sweet William. Would you ever use this colour in an Excel worksheet?
Recommended Excel Tools
In addition to all the free Excel tips and tutorials, there are other Excel tools that you can invest in. To learn more about the products listed below, click on the links to take a look at their features, and decide if they're right for you.
- Contextures PivotPower Premium Add-in
- Contextures 30 Excel Functions in 30 Days
- Excel Online Course
- Excel Charting Tools
- Excel Dashboard Kits
- Excel Project Management Templates
- Excel VBA School
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2014
All rights reserved.