Quick ways to clean up data downloaded into Excel from accounting programme?

hi Folks
Often when data is downloaded from Quickbooks/Sage into Excel it's usually got lots of merged cells, extra columns, extra rows etc...any suggestions on how to quickly clean them out to get the data in clean Excel list format i.e. no blank rows, no blank columns. Thanks
LVL 1
agwalshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The most obvious way is to record a macro doing the work:
go to cell A1
shift ctrl csrdown
shift ctrl csrright
cell format: ...
JohnBusiness Consultant (Owner)Commented:
When I use QuickBooks, I try to make sure the report does not include superfluous data and then that limits the extra rows and columns. It generally is not a problem.

Try the Transaction Summary Report which is more geared to a data dump.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
agwalshAuthor Commented:
@Qlemo - thanks for that...any idea where I'd get the code to do that...and I'll pass it on.
@John Hurst - will suggest that to the person...
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
"Record a macro" - start the macro recorder, and perform the necessary actions. This creates VBA code (with a lot of overhead, but working).
agwalshAuthor Commented:
Both good suggestions but John Hurst's answer a better fit for my specific question.
JohnBusiness Consultant (Owner)Commented:
@agwalsh  - Thank you for the update and I was happy to help;
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.