Ignore Blank Cells When Pasting

Ignore blank cells when pasting http://blog.contextures.com/

Last week, I was working on a client's file, and we wanted to get any new information from an update file, and paste it into the current record.

Both files were set up with the same columns, but the update file only had a few of the fields filled in. We could have used a macro to loop through the fields, grab any new information, and paste it to the current record. However, that would be a slow way to update the records, especially in a large file.

Fortunately, there is a built-in feature in Excel, that will copy a

Continue reading Ignore Blank Cells When Pasting

Excel Roundup 20141117

Weekly Excel Roundup http://blog.contextures.com/

In this Garage Series episode, Power BI and Excel go to Spain, to expose data patterns in past matches between Real Madrid and FC Barcelona.

"By combining historical El Clásico and player data, outside forces like temperature, humidity and moon phases, Jeremy tries to predict the outcomes and puts these predictions to the test among fans in Barcelona."

They didn't use "good hair" as one of the factors, but they predict 2-1 for Madrid, in the Oct. 25th match. (The final score was 3-1 for Madrid.)

You can watch the video below, or on YouTube: Power BI takes on El

Continue reading Excel Roundup 20141117

Create Custom Reports From Pivot Tables

custom reports from pivot tables http://blog.contextures.com/

If you're a pivot table fan, like I am, you know how quick and easy it is to summarize a massive amount of data, with just a few clicks. You can show sums, counts, averages, and other totals, without using any fancy formulas.

In the screen shot below, the pivot table is summarizing income and expenses, and there is a Slicer at the top left, for quick filtering.

Continue reading Create Custom Reports From Pivot Tables

Excel Roundup 20141110

Weekly Excel Roundup http://blog.contextures.com/

One Excel annoyance is that after you select a group of cells, you can't unselect one of them. For example, you might want to format specific cells, or create a named range, and you accidentally include a cell that shouldn't be in the group. You can't click on that cell to unselect it – you have to start over, and select more carefully the next time.

To solve that problem, Doug Glancy has created a free tool that you can download -- SelecTracker.

Next, we need a version that lets you unselect one cell within an large area. For example,

Continue reading Excel Roundup 20141110

Excel Numbers Do Not Sort or Add Correctly

Remove hidden characters in Excel http://blog.contextures.com/

Last week, I heard from someone who was having a problem sorting some numbers in Excel. He sent me a small sample file that showed a few of the dates and numbers that just wouldn't sort correctly.

My first guess was that the data had been copied from a website – that can cause some strange behaviour, when you paste it into Excel. A quick check with the COUNT and COUNTA functions showed that none of the values in cells C3:C6 were real numbers – they were text.

Continue reading Excel Numbers Do Not Sort or Add Correctly

Excel Roundup 20141103

Excel Roundup 20141103 http://blog.contextures.com/

Rick Grantham explains what a Monte Carlo Simulation is, and shows how to run one in Excel.

You can download the sample file from his blog, and watch the video below, to see the steps.

Continue reading Excel Roundup 20141103

New Workbook Based On Old One

xtremepivot table giveaway www.pivot-table.com

It's almost Halloween, and perhaps you're familiar with the horror story that I'm about to tell you. (And there is a treat at the end of the tale too.)

One day, not too long ago, Dr. Frankexcel needed to create new workbook. Instead of building the new workbook from scratch, Dr. F had a brilliant plan – open an old workbook, and use it as the base for the new file. Just open the old file, then quickly do a Save As, to create the new file, without damaging the existing one.

What could possibly go wrong? Bwaahhhaahhaa!

Continue reading New Workbook Based On Old One

Excel Roundup 20141027

If you'd like Microsoft to make Power Pivot available in all the Office 2013 and Office 365 packages, please sign the Friends of Power Pivot petition.

And if you're lucky enough to have the Power BI tools, Kasper de Jonge outlines how to get started with Power Pivot (Part 1). Watch the short video below, to see the steps.

Continue reading Excel Roundup 20141027

Excel Chart Compares High and Low Scores

Win loss high low chart http://blog.contextures.com/

Last weekend, I helped someone who needed a chart for a fantasy football league, to show the highest and lowest win/loss scores for each week. To help explain what it should look like, they posted a picture of a hand drawn chart, similar to the picture below.

By the way, I created the sketch in Excel, by using the Marker option, in the Artistic Effects. Who knew that Excel was so artistically talented?

Continue reading Excel Chart Compares High and Low Scores

Excel Roundup 20141020

In this video, sports scientist, John Lythe, shows how you can create 4 dynamic Excel charts in 10 minutes.

Or watch on YouTube: Make Four Dynamic Excel Charts in 10 Minutes

Continue reading Excel Roundup 20141020