Contextures Blog

Do you ever use the Watch window, to keep an eye on the results in one cell, while changing the data in another part of the workbook?

I used it last week, while working in a client’s price list file, where there was a multiplier on one sheet, and the final price on another sheet. After flipping back and forth between the worksheets a few times, I finally remembered the Watch window, and it made the job much easier!

Coincidentally, Mike “ExcelIsFun” Girvin, recently posted a video tutorial that shows how to use this handy feature. Mike is the author of Ctrl+Shift+Enter: Mastering Excel Array Formulas, and posts lots of Excel videos on YouTube. And he likes to say Boom!

Contextures Posts

Here’s what I posted last week:

• You can drag pictures from Windows Explorer into Word, but not into Excel. So, if you want to drag and drop images, drag them into Word first, and from there into Excel.

• When working with pivot tables, you can click the Refresh All button, to update everything at once. If some updates are taking too long, you can stop one or more of them.

• Instead of playing online quizzes, like “Which Simpsons Character Are You?”, you can create your own fun (or serious) quiz, in Excel.

• Finally, 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.

Other Excel Articles

Here are a few of the Excel articles that I read last week, that you might find useful:

• Mynda Treacy used Excel to design a Minecraft themed cake for her son’s birthday party. Read the comments too, to see what unusual things other people have done with Excel.

• Chandoo would like to know which Excel book you’ll read next. See his pick, and check the comments for lots more suggestions.

• Felienne Hermans, an assistant professor at Delft University of Technology based in the Netherlands, explains why spreadsheets stink, and 4 ways to improve them.

• Doug Jenkins, explains how he fixed a problem with UCase and LCase in VBA

• MF Wong shows how to create drop down lists with employee names sorted by the number of hours they’ve been assigned.

• The Office Watch blog warns that even if you’re only showing a few items in the Most Recently Used (MRU) list in Excel 2013, more are being stored in the registry.

Excel Resources

Here are some upcoming events, courses and new books, related to Excel.

• Registration is open for the Amsterdam Excel Summit. The one-day event runs on May 14, 2014, and features sessions by several Excel MVPs, such as Bill Jelen (Mr. Excel), Ken Puls and Charles Williams. All the sessions are in English, and the limit is 100 participants, so sign up now, if you’re interested.

• The Cleveland Modern Excel User Group meets the second Monday of every month, from 5:30 – 7:30 PM, so that would be tonight! Registration is free and you can get the details here. At the March meeting, Jeff Mlakar from the BI Team at Bennett Adelson is going to speak on Power BI.

What Did You Read?

If you read any other interesting Excel articles recently, that you’d like to share, please add a comment below, or send me an email.

Please include a brief description, and a link to the article.

__________________________________

Have you used Power Pivot yet? If you’d like a quick intro, and a few tips, watch this 15 minute video from Microsoft. Owen Duncan, Senior Content Developer for Power Pivot, takes you through some basics in this video, and talks about best practices.

If you’d like to learn more, their blog article has links to other Power Pivot articles on the Microsoft site.

VIDEO NO LONGER AVAILABLE

Contextures Posts

Here’s what I posted last week:

• Instead of using the default icon sets in Excel, you can create colored Harvey Balls, or other icons, with conditional formatting and custom number formats.

• Did you know that you can accidentally create calculated items in a pivot table? Learn how it happens, and how to remove them.

• If a list has blank cells, it doesn’t work well as a source for a drop down list. Use formulas to create drop down list with no blanks cells.

• Finally, 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.

Other Excel Articles

Here are a few of the Excel articles that I read last week, that you might find useful:

• You’ve seen Excel’s #DIV/0! error a few times, and Mike Alexander explains why it’s mathematically impossible to divide by zero.

• If you like trains, as much as you like Excel, the National Railway Museum (UK) is looking for volunteers to enter historical data into spreadsheets.

• Scott Lyerly lists his favourite books and websites for getting started with Excel programming. What would you add to the list? And if Dick Kusleika is “Sam Malone”, who are the other characters at the Daily Dose of Excel?

• Have you ever built a convoluted workbook, with formulas that make even your head hurt? John Rougeux shares his 3 Excel pro tips for helping others not hate you.

• Jeff Weir explains Robert Mensa’s technique for creating robust dynamic drop downs, without VBA. Just remember, the best we can do is build things that are idiot resistant, not idiot proof.

• PowerPoint MVP Geetesh Bajaj shares his recommendations for using Excel and PowerPoint together.

• Power Map for Excel is now out of Preview, and generally available, if you’re an Office 365 ProPlus customer. Meagan Longoria, from Data Savvy, takes a look at the new features and bug fixes.

Excel Resources

Here are some upcoming events, courses and new books, related to Excel.

• Registration is open for the Amsterdam Excel Summit. The one-day event runs on May 14, 2014, and features sessions by several Excel MVPs, such as Bill Jelen (Mr. Excel), Ken Puls and Charles Williams. All the sessions are in English, and the limit is 100 participants, so sign up now, if you’re interested.

Excel 2013 for Scientists by Dr. Gerard Verschuuren

This 250 page book is published by Holy Macro! Books, and here’s the intro from Amazon:
”With examples from the world of science, this reference teaches scientists how to create graphs, analyze statistics and regressions, and plot and organize scientific data. Scientists can learn the tips and techniques of Excel—and tailor them specifically to their experiments, designs, and research. They will learn when to use NORMDIST vs NORMSDist and CONFIDENCE vs Z, how to keep data-validation lists on a hidden worksheet, use pivot tables to chart frequency distribution, generate random samples with various characteristics, and much more.”

What Did You Read?

If you read any other interesting Excel articles last week, that you’d like to share, please add a comment below.

Please include a brief description, and a link to the article.

_____________________

If your laptop screen is too small, maybe you’re ready for an 82” touch screen, or start a bit smaller (and maybe cheaper) with a 55” version.

You can see Power Map in Excel on this giant screen, at the 7:15 mark, in the video below. My favourite moment is at 9:10, when the presenter says, “This is not the Excel spreadsheet I grew up with, that’s for sure.”

True! Excel was black and white only, with one sheet, when I started using it.

VIDEO NO LONGER AVAILALBE

Contextures Posts

Here’s what I posted last week:

• Shape Styles are flat in #Excel 2013, but you can change a setting to see rounded options, like the ones in Excel 2010.

• After deleting items from a pivot table’s source data, they can still appear in the pivot field drop downs. See how to remove them.

• If an Excel file is linked to another workbook, you can break the links. If the Break Link button is not available, this might be why.

• Finally, 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.

Other Excel Articles

Here are a few of the Excel articles that I read last week, that you might find useful:

• Are you an Excel Ninja? On the Lifehacker blog, Eric Ravenscraft shows you ​Four Skills That Will Turn You Into a Spreadsheet Ninja. The article links to my page on how to build a simple Order Form in Excel – so it must be good!

• Winston Snyder shares his code for turning the field captions on and off in a pivot table. Tip: If it’s just the headings, “Row Labels” and “Column Labels” that you don’t like, change from Compact Layout to Tabular Layout.

• Office 365 is one year old now, and Ed Bott finds that it has improved steadily since it was introduced.

• Annie Cushing gives us 18 real life examples of when and why you’d want to combine text from multiple cells.

• Should they teach Excel programming in primary schools, instead of text based programming? Miles Berry explains why he likes the idea.

• If you do any programming in Excel, you might discover a few new tips, as Colin Legg takes us on a guided tour of the VBA IDE’s options.

• I use Feedly for my RSS feeds, to find and read Excel articles. If you’re using it too, remember to download your OPML file, to create a backup of all your feeds. You never know when a reader will suddenly disappear (Yes, I mean you, Google Reader.)

Excel Resources

Here are some upcoming events, courses and new books, related to Excel.

• Registration is open for the Amsterdam Excel Summit. The one-day event runs on May 14, 2014, and features sessions by several Excel MVPs, such as Bill Jelen (Mr. Excel), Ken Puls and Charles Williams. All the sessions are in English.

Microsoft Excel 2013 Programming by Example with VBA, XML, and ASP by Julitta Korol

The Amazon listing doesn’t have a “Look Inside” feature, and there aren’t any reviews yet, so I’m not sure what topics are covered. The book blurb says, “a practical how-to book on Excel programming, suitable for readers already familiar with the Excel user interface. The book introduces programming concepts via numerous multi-step, illustrated, hands-on exercises. More advanced topics are introduced via custom projects.”

What Did You Read?

If you read any other interesting Excel articles last week, that you’d like to share, please add a comment below.

Please include a brief description, and a link to the article.

__________________________________

On the Power BI blog, Microsoft Finance Director, Marc Reguera, explains how his department is using the new Power BI tools in Excel. Will you be using them too?

You can read more at the Power BI blog, or watch the video below.

The video is also on YouTube: Interview with Marc Reguera

Contextures Posts

Here’s what I posted last week:

• The heading text was filling in when I typed an “A” code in a column. See how I fixed the AutoComplete problem, and what caused it.

• Find the source data for an #Excel pivot table, to check that all rows are included.

• Select multiple items for a cell, by clicking the check boxes in a popup form. It opens when you select a cell with a drop down list.

• Finally, 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.

Other Excel Articles

Here are a few of the Excel articles that I read last week, that you might find useful:

• Mitali Pattnaik wonders what kind of software could take Excel’s place. You can add your opinion in the comments on her Disrupting Excel article.

• Jon Peltier shows how to create and update a chart, using only part of a pivot table’s data.

• Tim Wolverson share his instructions for plotting a Fibonacci spiral in Excel, and there’s a sample file to download.

• Recently, I shared a formula for finding the last item in a category, in a sorted list. Oscar created a formula that works with an unsorted list.

• If you’re not sure what linear regression is, or how it works, read Mike Alexander’s excellent, and easy to understand, explanation.

• Microsoft’s Power BI pricing was announced, and the New York Times worries about feature creep in the tools.

• You can select numbers on a worksheet, and see a Sum, Count, or other summaries in the Status bar. There’s no Subtraction option, so Dick Kusleika created one of his own.

Excel Resources

Here are some upcoming events, courses and new books, related to Excel.

• If you’re near Tennessee, Rob Collie, from PowerPivot Pro, will be speaking at the first meeting of the Nashville Modern Excel User Group, on Thursday, Feb. 20th. The registration information is here, and there’s free admission to the meeting.

• Bill Jelen is presenting at the Orlando Florida Power Excel seminar, Saturday Feb 22 2014

• Chandoo has re-opened his Power Pivot online course, and you can get the course info and registration details here: Power Pivot Course

• Registration open today for the Amsterdam Excel Summit. The one-day event runs on May 14, 2014, and features sessions by several Excel MVPs, such as Bill Jelen (Mr. Excel), Ken Puls and Charles Williams.

Marketing Analytics: Data-Driven Techniques with Microsoft Excel by Wayne L. Winston

“This practical resource shows you how to tap a simple and cost-effective tool, Microsoft Excel, to solve specific business problems using powerful analytic techniques—and achieve optimum results. Practical exercises in each chapter help you apply and reinforce techniques as you learn.”

What Did You Read?

If you read any other interesting Excel articles last week, that you’d like to share, please add a comment below.

Please include a brief description, and a link to the article.

__________________________________