Hidden Secrets of Excel.
Hidden Secrets of Excel.
I worked as a Software Engineer, at three corporations, where my job was to automate Excel.
This one secret can save you gobs of time.
Many engineers, accountants and IT professionals live in Excel – it's the application they use most of the day. The problem is that they are doing some repetitive tasks, and don't know that they can automate them with Excel macros. This is because they don't think like a programmer.
When you think like a programmer, a lot of tasks can be automated in Excel. So, either ask your in-house programmer to help you break down your workflow, or follow the steps in this article.
Unlike most people, programmers think in loops.
Repeat the sentence above, until you're bored.
Thinking in loops, and nested loops, is not how most people complete their tasks. But it's the key to powerful Excel automation. For example:
I have a lot of clients that need to calculate payments from insurance, or other commission based sales. Every month the payment professionals need to look over reports from the sales departments. That can include external sales companies. All these commission files may be in different formats.
Normally someone will massage these files, so they can be imported into one, master, Excel sheet. But that's costly to do every month. Especially when humans make mistakes – no offense if you're a human reading this.
Using Excel macros, people often try to apply rules to format the sheets as they need. But this often stops short of the real goal.
Remove the need for any human interaction with the data.
Yes, you read that right, I am anti-human. At least when it comes to messing with data. Let the computer do what computers are good at – data processing.
To do that, a person just needs to write down the tasks they are doing every month with the files that always need to process. Then, notice where the loops are. Like copy columns A-F and Z into a new sheet … EVERY MONTH. Then do such-and-such like reformatting the phone numbers. Again … EVERY MONTH.
Where the loops show up, is a really good place to automate.
While Excel macros can automate some keystrokes, or common tasks, Excel allows these macros to run over-and-over on a set of data. So while recording a macro is useful, combining that macro with a looping structure is golden.
Excel has several looping functions that can run over every data row, copying the needed data to a new tab or new sheet. And while we're copying, we can even apply formatting, calculations, deletions, and even replacement. When you're faced with row upon row of data that needs to be manipulated, using loops, and macros create a one-two-punch. Saving you gobs of time.
Learning a few looping structures can supercharge your Excel power. The For-Loop runs a set number of times. Most often it's used to loop over a spreadsheet tab, once for each row like this:
For (the number of rows in this tab)
Do these things like copy, format, calculate, delete, and replace data
Next (do it all again until you've reached the end of the rows).
While and Do loops will repeat until a task is done.
The instructions on your shampoo bottle should actually read:
Set (the number of times you've applied shampoo) = 0
While (number of times you've applied shampoo) < 2;
Apply shampoo to hair;
Add one to (number of times you've applied shampoo)
Go back to the start of the loop, unless (number of times you've applied shampoo) is 2 or more.
Excel macros are like the middle stuff. The stuff you do once per row, but want to apply to all the rows of a spreadsheet. But they can be extended to run across all the rows, all the tabs in a file, or all the sheets in a folder.
Learning what loops can do will increase your Excel skills, or you may have programmers on staff that can enhance your Excel work with automation. Either way, get loopie.
When companies have a one-off software project, it's not what their IT department normally does. It's too small to spend the time, and effort to staff for. Yet your team sees the benefit of custom software.
Often the bidding sites look attractive – at least at first.
Thinking about hiring with Upwork, Freelancer.com or Fiverr.com? Search online for all the complaints clients are having. At these sites you might find some good programmers, but it can be hard. One client put it best:
"At the bid sites, I got hundreds of bids. Many from people who could not do the job," Craig Windom said. "Many freelancers were bidding $500 when I knew the job was more like $2,000. It was just a waste of time."
On trustpilot.com a buyer wrote this:
"Upwork is sketchy.... Be careful....," Cher Power said. "One freelancer took my deposit of $1,710 and did not provide any work. They basically stole my money. I complained to upwork and disputed with my bank. In the end, my upwork account was suspended. There is absolutely no protection."
Why do business with bid mills, when there is a better way.
We make you the hero by reducing the friction to get one-off projects done.
First, your project is discussed to learn the key deliverables.
Next, you get recommendations to advance your project.
That includes defining the project so that time, cost, and deliverable expectations are met.
You know what you're getting, and what you're paying for.
If you want to move forward, we eliminate the billing, and W9 reporting hassles while increasing security.
You just use a credit card or paypal account to start the project.
You're protected with refund protection, we never see your credit card details, and all IRS reporting is automated.
You'll have a weekly communication slot so that you're always informed about the project status.
Lastly, we've been in business since 1987, so you can expect us to be there if you need updates.
If you want to know more, email [email protected]