Contextures Blog

Forget that old-fashioned mouse! Now you can control Excel during a presentation, by using your phone. Well, assuming that you have Excel 2013, and a Windows phone.

There is a link to the Office Remote app download page in the Excel Articles section below.

Contextures Posts

Here’s what I posted last week:

1. Use MAX and IF with multiple criteria, to find the latest date that a product price was changed, for a specific customer.
2. Set up a pivot table so it shows missing items, and add temporary data, if necessary.
3. No, it’s not the best way to present data, but if you have to make a pie chart, keep it simple and easy to read.
4. 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:

1. The IT Faculty of the Institute of Chartered Accountants in England & Wales has posted a draft version of their Twenty Principles for Good Spreadsheet Practice, and they would like your feedback.
2. Microsoft Research just released its Office Remote application, which lets you use your Windows phone to navigate through an Excel 2013 workbook during a presentation.
3. On the Visual.ly blog, someone shared their map of Australia and a column chart, both created by colouring worksheet cells.
4. It doesn’t have to be complicated! Learn an easy way to make bullet charts and boxplots in Excel, on the Excel Charts Blog
5. If you’re using APIs to pull data for Excel apps, be sure to format the numbers correctly. The Office Developer Team shares some sample code.
6. Instead of using macros, add a hyperlink to a shape, for workbook navigation. Mynda Treacy shows the steps.
7. When you’re programming an Excel file, it’s nice to have a quick way to open the workbook, with everything unlocked and ready to edit. Scott Lyerly shares his trick for adding a back door to your Excel files
8. Do you love pivot tables? Data journalist, Nassos Stylianou loves them too, and shares a few tips on the Digitally Focused blog.
9. If you need to calculate the last day of any month, you can use Chandoo’s simple formula.

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.

___________________

There’s always something new to learn about Excel, and it’s good to be reminded of a few old tricks.

For example, I’m sure you know that you can’t make a holiday meal without a spreadsheet, as this video shows. The spreadsheet section starts at the 1:57 mark, and continues a little later.

Contextures Posts

Here’s what I posted last week:

1. Use MAX and IF to find the latest date that a product’s price was changed. Then, using the product name and that date, we can find the latest price for the product – even if it isn’t the highest price.
2. After you create a pivot table, select one or more fields, and run this macro to change all the selected fields to SUM. Other fields are left unchanged.
3. To look for external links in an Excel file, you can use a free add-in – FindLinks. Or, follow a few steps, and try to fix the links.
4. 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:

1. Do you ever use the Window key on your computer keyboard? Chandoo shows how to use it as a quick way to paste as values
2. For the perfect Christmas (or Thanksgiving) meal, be sure to use a spreadsheet! Read about the spreadsheet man in Sainsbury’s Christmas commercial, and you can download my holiday meal planner here.
3. Do formulas that refer to their own sheet calculate faster than formulas that refer to other sheets? Charles Williams runs a few tests, and is surprised by the results.
4. Even if you aren’t a cricket fan, take a look at the Excel dashboard that Chandoo created as a tribute to retiring player, Sachin Tendulkar.
5. If you ever get a list of dates and tasks in Excel, Jimmy Pena (JP) show how to create Outlook tasks from that list, by using Excel VBA.
6. If your data isn’t set up correctly, you can’t build a flexible pivot table. Microsoft’s Power Query add-in makes it easy to “unpivot” your data, and Ken Puls shows the steps on his Excel Guru blog.

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.

_________________

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

Microsoft has improved the Office Web Apps, and you can read the details on the Excel Team’s blog. The feature that looks most interesting to me is the real-time collaboration in Excel files — you can’t do that in the desktop version.

If you use the MOD operator in Excel VBA, you could run into problems with large numbers. Chip Pearson suggests some alternative formulas and code that you can use.

Dick Kusleika ran into some problems while running code on a pivot table that has calculated items. Lots of problems are caused by pivot table calculated items, in my experience!

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.

________________________