How to Use Excel – are you spending more time manipulating your data or thinking about the meaning of the results?
If the latter, then congratulations you are in order because you are already using Excel pretty efficiently.
What is Excel?
Excel is really the easiest way to keep track of large amounts of information. Even something as simple as a list of addresses and names is awkward to handle in a word-processing program or similar, yet trivially easy in Excel.
Excel also lets you sort, resort, and/or graph your information in a heck of a lot of ways—and this is even if you don’t need its calculation functionality. Therefore its use is very widespread in modern offices.
How to Learn Excel
The only way to learn Excel and retain everything you know, in my opinion, is to use it to do real work and problem-solving (building a lot of new reports from real data, finding answers to questions about data, etc).
That could mean taking a job where Excel skills are required and say “Yes, I can do that in Excel”, even if you don’t know how at the time.
Everything that I’ve learned how to do in Excel came from a positive attitude and a willingness to google everything and question more knowledgeable people around the office on how to do things.
You will rarely get the complete answer from just one source for your problem that needs to be solved in Excel. But the research and figuring out the problem from little to no knowledge will take you far in the learning process.
Of course the idea of “fake it till you make it” doesn’t work for a lot of people. So if you are not a fast learner and an avid Googler, you will probably want to start with a class aimed to get you your MOS certification in Excel.
Then take more advanced classes on edX or other MOOC websites to get more advanced introductions to manipulating data and getting insights in Excel.
How to Use Excel Proficiently
Let’s get to the story of Brad Yundt, Mechanical engineer, and Excel aficionado on how he used excel in a company he worked for 20 years.
Since you continue to ask the question about using Excel more efficiently, you now need to ask whether there is any repetition in the work that you do.
For example, do you need to handle the same type of workbooks on every job? Issue the same reports every month? Massage the same kind of data every week?
Do other people in your organization do the same things that you do?
If the answer to any of these questions is “Yes,” then you ought to be learning how to write macros to automate those tasks.
You may think you are too busy to take on the task of learning how to program in VBA. In actual fact, you squander company resources by continuing to do things manually.
You are making mistakes because human error can never be completely eliminated, no matter how good you are with the manual processes.
You are also wasting time because a macro can do in seconds what it would otherwise take you hours to accomplish (minus a few minutes if you have mastered keyboard shortcuts).
How can you find time to write these macros? Do the first couple of macros on your own time. From then on, when one of those tasks comes up—you will have free time to make more macros.
Over the ensuing months, the compounding of productivity improvements will start saving enough time that management notices, while also making better quality deliverables.
How to Break Repetitive Task into Small Pieces
Your problem then becomes figuring out how to break a repetitive task into small pieces, each of which can be tackled separately. If you can’t see how to step back and try to look at the forest rather than the trees.
Make a block diagram of a workflow to see opportunities.
I faced exactly this problem in 2006 when I took a job with a manufacturing company. As an engineer, my Excel-related tasks took about half of each day. These were:
- Make sure the equipment sold could meet the customer’s performance requirements
- Build a list of instruments needed for the project, then cross-check it against the P&ID schematic drawing
- Update the tag numbering of each instrument to use the customer’s preferred tag numbers. These new tag numbers would be used on all future documents relating to the project.
- Select the right size valves to control the system, then produce calculations that verified the sizing
- Produce a set of datasheets using an industry-standard template. These sheets show construction details of each instrument, along with process details like flows, temperatures, and pressures. Each datasheet needed to use the customer’s preferred engineering units and tag numbers.
- Submit all of these documents for customer review and approval. Update the documents according to customer red-line markups. The only acceptable final result was every detail in the document set being 100% correct.
The Excel Learning Process and Proficient Use From a Notable Excel Expert
Here his story…
I did things the hard way the first couple of times so I could learn where the opportunities lay for improvement. Marking up a previous job was the standard method.
That approach institutionalizes bad practices, and even after checking and double-checking before submittal, I made mistakes on 5 to 10% of the instruments.
This was personally very embarrassing because I had about 30 years of experience at this point.
The instrument list in step 2 was the first target for automation. My employer made about 15 different models, each of them having a standard bill of materials, but with numerous options.
I tackled this by putting a list of all instruments used by the company in the worksheet Parts Database, along with their part numbers and descriptions.
A separate worksheet for each model listed the parts normally used, both standard and optional. VLOOKUP formulas brought data over from Parts Database as all of these worksheets were in the same workbook.
To produce a custom instrument list, I just needed to find the worksheet for the model the customer ordered, “X” out the parts they weren’t getting, add in the handful of special parts required, AutoFilter and export the filtered sheet to a new workbook. Major improvements in effort, quality, and accuracy.
There was much gnashing of teeth the first time my customers wanted to use their own tag numbers.
Fortunately, they gave me a cross-reference list of the standard tag numbers in one column and their preferred tag numbers in another.
So I wrote a macro to check for possible conflicts, then perform a search and replace. This macro needed just seconds to run and was 100% accurate.
It took me two years to automate all six steps, and the macros chopped 90% of the time needed to produce workbooks for customer approval.
So my firm handled double the sales volume without needing more engineers, and my colleague and I used our now free time to take on tasks previously performed by a technical writer.
Document quality improved because we only needed to edit one place, and all future jobs got the benefit. Accuracy was also up, as most jobs now came back with no red-line comments at all.
Social Media: Facebook, Twitter, Wikipedia, LinkedIn, Pinterest