Why Excel PivotTables get messed up and how to stop it. Time and again we’ve heard from people who try PivotTables and only get nonsense tables. That’s understandable since Microsoft’s hype about PivotTable ignores the basics that we’ll cover in this article. You’ve probably seen a Microsoft demonstration of PivotTables where it looks sooooo easy. […]
How to add and remove data from a PivotTable. Thanks for the great feedback from our article on PivotTables. One concern some of you have is about adding or removing source data after the PivotTable has been created. That’s understandable because the original PivotTable releases where unclear and curiously sensitive about the data ranges used. […]
A bad example of Excel and document sharing online. Over on Reddit there’s a sad example of sharing an Office document that does no credit to the author. A husband started a diary of his attempts at, shall we say, ‘horizontal folk dancing’ plus his partner’s reasons for not wishing to join him. He then […]
A simple, practical and non-scary example of how Excel PivotTables can work for you. Excel’s PivotTables are a great feature but one that seems way too hard for most of us … so here’s a simple and practical example of how PivotTables can help. A little fear of PivotTables is understandable. It’s one of those […]
What is Delve and what does it mean for Microsoft Office? At the recent Microsoft conference there was mention of Microsoft Delve. But what the ?^%$ is ‘Delve’? It’s an enhanced search tool for your documents and online info. Not just standard Office documents but also videos, audio, links to web sites etc that are […]
Some great tips on table formatting. Joey Cherdarchuk has a nice posting on a very different table styling to the type that Microsoft prefers. Under the banner ‘Clear Off the Table’ he suggests getting rid of gridlines and colors to simplify the look of tables. In his example, Joey takes a standard Microsoft auto formatted table […]
Another easy way to get images into Word and other Office documents. Last week we mentioned a faster way to get a scanned image into Word via the ‘scan to clipboard’ option available in some scanning software. There are similar options available for copying existing image files into Word, Excel or PowerPoint documents as well […]
Can you scan a picture or document directly into Word? Back in the day, you could click a button in Word to scan a document or image and drop it into a document. Can you still do that? Alas no. Microsoft dropped its imaging services within Windows and so Word lost the scan button. The […]
How to change the date format in Excel Online The default date format in Excel has always been a mystery to newcomers and its stranger in Excel Online (the new name for Excel Web Apps). To change the default date format for display and date entry for Excel Online, go to your Microsoft account: Go […]
The features in Excel for iPad. Here’s a look at the major features in Excel for iPad, looking over the five main tabs. The buttons will be familiar in most cases, though the underlying menus/galleries are different and limited compared to Excel for Windows or Mac. Shameless plug:Office for iPad: The Complete Guide is now available. […]
A close look at the new Formula Keyboard in Excel for iPad. One of the highlighted innovations in Excel for iPad is a dedicated Formula Keyboard which looks like this: It’s necessary because none of the supplied iPad virtual keyboards work well when you’re trying to enter Excel formula. Hunting and pecking around different symbol […]
A free way to show Microsoft Office help in other languages Office Screentips is a way to show tooltips for Word, Excel, PowerPoint, Outlook and other programs. This free addon from Microsoft doesn’t interfere with how the programs work or the wording of the tabs and ribbons. Only the tooltips that appear when you hover […]
Grab high-res pictures from movies to insert into Office documents or emails. We’ll show you how to capture or grab a proper, full size, high-resolution still image from a high quality video file then paste that picture into an Office document or Outlook email. Why do this? Sometimes a video turns out badly but has […]
How to find your pictures to insert into an Office document. Even a simple thing like adding a picture to an Office document or email has subtleties and annoyances. When you choose Insert | Picture in recent versions of Office, it opens, by default, in the Picture Library. That seems sensible, at least to Microsoft. […]
Can you call a Translation service to return results in Excel? After checking out the clever European word translator, we started wondering about loading translations into Excel. It should be simple to have some code to call a web server and return a translation of a word. Office 2010 and Office 2013 have a ‘mini […]
How the Clinton ‘Saints and Sinners’ spreadsheet might look. Excel has many uses and an upcoming book talks about one of them. Apparently Bill and Hillary Clinton used Excel to maintain a list of those they like and dislike. We could not help wonder what the Clinton worksheet would look like and why they’d use […]
Why and How to remove words from the main Microsoft Office dictionary. Sometimes the supplied Office dictionary has words you don’t want or you’d like a warning about. Here’s how to put the red squiggly line under words that would otherwise be OK according to Microsoft. Why? Why would you want to remove words from […]
Comparing caffeine levels in drinks from an Excel spreadsheet. The Guardian’s Datablog has another interesting analysis, this time of caffeine levels in different foods. Sounds boring until you read that reduced sugar colas have almost double the caffeine of a regular, sugared, cola. Or 1 cup of coffee can have as much caffeine as 2 […]
Examine US public health data in Excel. One of the great things about the recent Internet is the availability of previously hidden data. Large blocks of information have been collected, checked and published online. Combine that with a tool like Excel and you can do your own analysis! Here’s just one example of what’s available […]
An example of web scraping from BestBuy.com into Excel. We’ve already talked about getting prices from the Amazon.com web site into Excel with automatic updates. Now let’s look at how to do it for BestBuy.com prices.Excel has had a method of web scraping for many versions you can find it at Data | Get External […]
How to check Amazon product prices automatically in Excel. With Black Friday coming in the USA, it’s time to carefully check prices and look out for bargains. Ideally get those prices automatically and put them into Excel for comparison. To do that for Amazon means ‘scraping’ or trying to grab prices from a web page. […]
Updating Office 207 can literally go on forever! Many users are reporting a problem with the patches for Office 2007. The three troublemakers are: http://support.microsoft.com/kb/2760583/ (KB 2760583) http://support.microsoft.com/kb/2760411/ (KB 2760411) http://support.microsoft.com/kb/2760588/ (KB 2760588) No matter how many times you install them, manually or automatically. Given the widespread nature of the problem, across different versions of […]
How accurate is Excel compared to universal needs. On page 45 of the Sept/Oct 2013 Mental Floss magazine is a small item ‘Why do we care so much about the digits if Pi’ which includes this trivia: “The computers that keep the International Space Station humming round off pi at a crude 16 digits. When […]
You can combine cell formatting into a style, but not like in Word. Excel has Styles … truly it does. Word has a way to combine formatting attributes of a letter, word or paragraph into a single block with a name like ‘Heading 1’. Excel has a feature also called Styles but it’s a very […]
Evernote for Android now comes with the ability to edit Office documents. Our favorite notemaking system, Evernote, now has the ability to edit Office documents within their Android version. Yes – we very much prefer Evernote over Office Onenote. Evernote runs very well, syncs seamlessly and completely across all popular platforms and devices. Evernote has […]
Jon has found a clever way to show words with many numbers in Excel Back in July 2012 we showed you how to put singular or plural words against numbers in Excel – like 1 cookie or 3 cookies. At the end we noted how it got more complicated when you tried to deal with […]
There’s SUM and then there are all the useful SUM variations to choose from. Excel’s SUM() function is probably the first one we learn but there’s a lot more to it than simply clicking on the button to add up a list of numbers. In this article we’ll look at the options available in SUM […]
We found a worrying bug in Excel 2013 when inserting rows into an existing worksheet. While investigating the Excel ‘coding error’ in an economics paper we uncovered a worrying bug in Excel. It occurs in our testing of Excel 2013 in the cell ranges of formulas like SUM and AVERAGE at the bottom of columns […]
An Excel mistake caused a change in economic policy. A user mistake or Microsoft’s? A few weeks ago came the news that a much quoted academic paper was based on a faulty Excel worksheet. The paper titled “Growth in a Time of Debt.” from Reinhart & Rogoff was widely used to support the idea of […]
Other ways to make a new document based on an existing one … There’s more than one way to get a similar result as ‘New from Existing…’ that are worth keeping in mind. They apply across all recent versions of Word as well as Excel and Powerpoint too. You can just open the source document […]
The new Wikidata site should provide hours of Excel ‘fun’. The WikiMedia foundation, operators of Wikipedia among others, is developing a new site called Wikidata. It will be a home for public ‘structured data’ in other words, lists. There are plenty of lists already in Wikipedia. Wikidata will link with Wikipedia and help structure those […]
Microsoft Word, Excel and PowerPoint will soon be on mobile devices. One interesting snippet generally overlooked in the Office 365 Small Business announcements was the mention of Office Mobile Apps. There’s been plenty of talk about Microsoft Office coming to mobile devices, especially non-Microsoft devices but the Office 365 announcement was the first time Redmond […]
A little Excel quirk to mention using LEN() with large numbers. A little Excel quirk to mention. For as long as we can remember you could get the number of digits in a number by simply using =LEN() even though the input is a number, not text. But have a look what happens for the […]
Excel and really large numbers – they don’t play nice together. When we wrote about large prime numbers in Excel we forgot that Excel doesn’t do a good (some might say honest) job with large numbers. Sorry about that – mea culpa. Excel is fine with integers up to 15 digits but once you go […]
How to make an Excel chart that changes as you add more cells. In a recent article we looked at making a chart with a timeline for regular and irregular dates. That’s OK for fixed data but often you add or remove data and want the chart to automatically update. That’s much easier these days […]
Our famous guide to Office, saving money, install and new features. Now for Office 2013. Many of you figured it was coming and now it’s here … Office 2013: the real startup guide is available now. “Things you need to know that Microsoft won’t tell you: saving money, installing, configuring and using Office 2013” Only $9.95 […]
They’ve discovered a new Prime number but not for Excel. The GIMPS project has discovered a new prime number – that’s a number that can only be divided by itself and one. The new arrival is a bouncing bonny number 257,885,161-1 which is 17 million digits long. Prime numbers aren’t just mathematical curiosities, they are vital for […]
The secret to a timeline chart in Excel is the data. Emily writes asking “How can I make an Excel chart with dates on the X axis with a proper time scale – even if the dates aren’t regular? “ We could not understand Emily’s question until we asked for more details. Making a date […]
Insert special characters into Office documents quickly. One of the eternal questions to Office Watch is about adding extra characters to a keyboard. The most common requests are for adding currency symbols like Pound £ Euro € or Yen/Renminbi ¥ but can also include the @ symbol which can, even these days, be missing from […]
A clever addition to Excel 2013 will help split and join data automatically. Flash Fill is a new Excel 2013 feature that’s both useful and looks good in the demonstrations you’re bound to see. It’s not on any menu in the preview but works if you know what to do. In Excel there’s often need […]