Look Who it Was Random video

Excel - Eliminate Blank Cells in Crystal Reports with This Excel Tutorial! - Episode 580

655 views · Published 22 June 2009 · 3:45 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Cleaning Data Exported by Crystal Reports.

Welcome back to the MrExcel netcast. In today's episode, we will be addressing a question from Susan regarding a report created by Crystal Reports. If you have any questions for the netcast, please feel free to drop us a voicemail or an email at [email protected] and we will address it in a future podcast.

Susan's problem is that when she creates a report from Crystal, it takes three rows to create every single record. This results in data being spread out across multiple rows, with annoying blank fields in between. Susan wants to know if there is a way to get all the fields on one line and eliminate those blank rows.

To solve this issue, I will be creating two new headings and using a simple formula to consolidate the data from the multiple rows into one. However, I only want to apply this formula to the first row of each record. To do this, I will use the AutoFilter feature and select only the non-blank cells in column B, which is where the first row of each record is located.

Once the data is filtered, I can copy and paste the visible cells only, which will give me the first row of each record with all the data on one line. I will then use the Paste Special function to paste the values only, and then delete the extra rows. This may seem like a lot of extra steps, but it is a relatively simple solution using some advanced Excel tricks.

It's frustrating that Crystal Reports doesn't provide the data in a more user-friendly format, but with a little bit of Excel know-how, we can easily fix it. Thanks for tuning in to this episode of the MrExcel netcast. Don't forget to subscribe and hit the notification bell to stay updated on all our latest videos. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Susan writes in with an annoying problem. Crystal Reports is creating a report where every physical record is taking three rows in Excel. Episode 580 will walk through the steps necessary to get this into a nice sortable data set.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Table of Contents:
(00:00) Introduction
(00:30) Susan's Problem with Crystal Reports
(01:01) Creating Formulas to Solve the Problem
(01:24) Selecting and Filtering the Data
(02:00) Copying and Pasting the Data
(02:30) Deleting Unnecessary Rows
(03:06) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Copying and pasting values in Excel
Copying and pasting visible cells only in Excel
Creating a sortable data set in Excel
Creating formulas to reformat Crystal Reports data
Crystal Reports data formatting issue
Crystal Reports data manipulation in Excel
Deleting unwanted rows in Excel
Formatting Crystal Reports data in Excel
Removing blank rows in Crystal Reports data
Sorting data by column B in Excel
Sorting data by column in Excel
Using AutoFilter in Excel to select non-blank cells

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152469/

More from this channel