Ideas That Excel

A perfect example of what can be accomplished with Excel was a monthly
“Work center Hours by Division” report I had to complete. I downloaded
data from our ERP system into Excel and then had to create totals by
division & work center. I also had to search out duplicate line items and
delete them, and many of the lines had very similar data so spotting
the duplicates was not easy.

At first it took me about 6 hours to
complete every month. As I improved by Excel skills & learned how to
write macros using VBA this report eventually was completed every
month in 6 seconds, using only 1 click of the mouse!

Do you have a long boring process that you want to be completed in only seconds?  Contact me to see if I can create a custom Excel solution for you!

Here is a trick that may solve the issue of an excel file that opens very slowly, or one that takes forever to save it.

Obviously if the file has a tremendous amount of data it will open more slowly and this l may not help much or at all.  This applies more towards an Excel spreadsheet that you cannot understand why if takes so long to open or to save.

First, make a backup copy of the file just in case something goes wrong, which may take some patience but it is better to be safe than sorry.

Go down one row past the last row that contains any data.

Click on the Row number on the Left and then press CTRL plus SHIFT and the DOWN arrow on the Keyboard.

This will select all the cells below that row.

With it still highlighted RIGHT CLICK your mouse so that all the empty columns are selected RIGHT CLICK on the mouse and select DELETE.  This will delete all the contents in those cells.

Next you should find the last column that contains any data and select the next column to the right.  Click on the column heading so the renter column is highlighted.

Then press CTRL and SHIFT and the RIGHT ARROW so that all the empty columns are selected.

Then RIGHT click on the mouse and select DELETE.

If the spreadsheet has data in several sheets you may have to do it to this on each tab of the spreadsheet to solve the issue.

Now SAVE the spreadsheet.   If the spreadsheet no longer takes forever to save then congratulations you have solved the issue!

Why does this happen?  Because Excel has A LOT of cells to keep track of, not only what is in each cell but the format of each cell, the result of the formula, etc.  So, to save memory Excel will take some shortcuts to save space & memory.    Sometimes as we work in a spreadsheet, we make a change that interrupts Excels internal shortcut.  So instead of Excel saying to itself “cells A662 to then end of the spreadsheet are all the same format”, if starts track it in much small ranges that cause excel to come to a crawl.

Again, this won’t solve the problem in every instance.  But I have found it works almost every time when a spreadsheet is staking forever to open or save that does not contain a large amount of data.

Need help?  Contact Me!

Some of the ways I can help improve your productivity & profits include:

Automation of Existing Reports – Do you have a report that you spend
hours and hours on every month? A report that takes your hours to
complete could take just seconds.

Make Data Exports from your ERP System Actually Mean Something – Many
ERP systems can export data directly to Excel or as a text file;
unfortunately many times they come out as a scrambled mess. In most
cases I can create a solution in Excel that can clean up the data and
make it into a usable export of data directly from your ERP system.

Create A Custom Piece of Software to help run your business – Using
Excel to it’s fullest potential we can create a custom spreadsheet
that acts as custom piece of software built to your specifications.
Before using up your IT budget buying another software program contact
me, I might be able to save your thousands of dollars by creating a
solution in Excel.

Excel Consulting Services – If you need help or additional training
with Excel I am happy to help you out. Please contact me for further
information.Ideas That Excel is here to solve your problems and make
you business more efficient through the use of Microsoft Excel. I can
help you if you need just basic help with Excel or if you need a full
fledged VBA program written with Excel that operates much like a
complete independent program.

Make Exports from your ERP System Useful for Analysis –   Most ERP
systems don’t have all the reports that you need but many do offer
exports of existing data directly into an Excel readable format.  The
problem is often these exports split one line of data up onto several
lines when opened in Excel.  Let me create a solution that can fix the data
with one mouse click!

Contact me for Excel solutions that will save you time and money.

Large or small projects feel free to contact me!

This free Microsoft Excel spreadsheet will calculate your car loan payment.

This is a great tool to have for any car shopper.

Never trust the dealer’s calculations!

Download the Car Loan Calculator   

The IdeasThatExcel blog will provide you with Tips & hints for Excel.  I will also post updates for projects that I have made available or information about upcoming projects.

Be sure to check in often.