Search Contextures Sites
Ignore blanks when pasting + more Excel tips
Excel Weekly News from Contextures November 25, 2014
In this week's Excel news, you'll see how to ignore blank cells when pasting over data, and much more. Thank you for reading the Excel news, and I hope that you are enjoying the articles. If you have suggestions for future topics, please let me know.
- Debra - firstname.lastname@example.org
- Ignore Blanks When Pasting
- Create a Calculated Field
- More Excel Tips
- Excel Announcements
- Video: Repeat Text with REPT Function
- Weekly Photo: Christmas Village
- Featured Excel Product
Did you know that you can copy a group of cells, where a few of the cells are blank, then paste the data somewhere else -- ignoring the blank cells? In the screen shot below, there is updated information in the green cells, where the city names and an order date have been filled in.
With Excel's Paste Special command, you can copy all the green cells, and paste over the orange cells, without losing the data in the circled cells. Excel will ignore the blanks in the copied cells, and just paste the cells that contain data.
Click here to see the details: Ignore Blanks When Pasting
One of the pivot table fears that people mentioned in our survey was creating a calculated field. It certainly can be confusing to add formulas to a pivot table, especially if you aren't sure if you need a calculated item or calculated field.
To get started, follow the steps in my latest pivot table blog post, and create a simple calculated field.
Click here to get the details, and watch a video too: Create a Calculated Field
Here are a few of the Excel articles that I read recently, that you might find useful:
- Instead of showing all your data in a chart, use Jon Peltier's technique to create variable endpoints for the chart data.
- Sumit Bansal builds a one variable data table, in the first part of his series on Data Analysis.
- If you're just getting started with programming, Philip Treacy explains how workbook and worksheet events work.
- Chandoo shares a quick tip for opening and saving files faster in Excel 2013.
- The Dashboard Spy ask if there is a difference between dashboards and scorecards.
- Charles Williams thinks that there are better alternatives to both VLOOKUP and INDEX/MATCH.
- Chris Webb shows how to combine data from multiple Excel files with Power Query, the easy way.
- Finally, for a humorous peek at what other people are saying about spreadsheets, read the latest collection of Excel tweets, on my Excel Theatre blog. And vote for your favourite!
Here are some upcoming events, courses, recently published books, and other new items, related to Excel:
- New Book: Data Analysis in Sport by Peter O'Donoghue and Lucy Holmes. "Introduces the fundamental principles of data analysis, explores the most important tools used in data analysis, and offers guidance on the presentation of results...Covers commercial packages for performance and data analysis, including Focus, Sportscode, Dartfish, Prozone, Excel, SPSS and Matlab."
Use the REPT function in Excel to repeat text a specified number of times in a cell. You can use the REPT function to create an in-cell bar or dot chart, track scores with a simple tally, or use it with VLOOKUP to find the last text item in a column
Watch this video to see how to use the REPT function.
Christmas is only a month away, so to get into the holiday spirit, we set up our little Christmas village on the weekend. Most of the houses have a light switch, and it's fun to turn the lights on when it starts to get dark in evening. I collect angel ornaments too, and they are standing up in the centre of the village. Now I just have to start shopping for a few presents!
This Friday, November 28th, is Black Friday in the USA, and to celebrate (even though I live in Canada), I will put one of my Excel products on sale, for 24 hours only. Be sure to check my website on Friday, to get the coupon code for the discount. The PivotPower Premium add-in will NOT go on sale, but one of my other products will be sold at a discount.
Also, if I hear about any other Excel related products that are on sale, I'll put a link on my website, so you can check those out too. And of course, there will be many sale items on Amazon.
See more recommended Excel products.
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.