Blog Posts

How Quick Are You in Excel?

Written by: | Posted on: | Category:

If this blog describes you or your business, please click our Contact page and let my company help out.

As you may know, Custom Visuals automates as much as we possibly can as part of our core business. We have a couple thousand unattended tasks running on our servers (co-located and in-house) on behalf of our clients throughout the day (not including the tasks the operating system runs on its own behalf). Some of these are the types of tasks I’m referring to in this blog. The coma-inducing ones that are important, but suck the life out of you or your employees.

If part of your day involves performing the same 10, 20 or 50 steps on Excel spreadsheets, I want to change that into something more productive for you. One of the common processes we manage for clients involves an employee that receives a CSV file that needs to be turned into a formatted Excel file for use by colleagues or clients. Let’s consider an external file with 10,000 rows and 20 columns, and an internal file with 200 rows and 2 columns as our example set. These changes might involve some of the following steps along the way:

Executive summary: dozens of common Excel steps you wouldn’t wish on your worst competitor, but may be requring employee(s) to perform. Skip past the numbered items for an alternative.

  1. Download a CSV file from external website.
  2. Locate a CSV file on internal share.
  3. Open the external CSV file in Excel.
  4. Create a new tab in the existing Excel file.
  5. Open the internal CSV file in the 2nd tab.
  6. Change back to the first tab.
  7. Insert 3 new columns starting at the 4th column.
  8. Type in column names for the three new columns.
  9. Type in a VLookup in the first new column using fields from the 2nd tab.
  10. Copy and paste the VLookup to the remaining rows in the column.
  11. Copy/paste the Vlookup column as values.
  12. Delete the 2nd tab.
  13. Place a red border around every cell in the column that did not have a VLookup value.
  14. Type in a Left function in the next column to extract a subset of another column.
  15. Copy and paste the Left function to the remaining rows in the column.
  16. Enter a Date function in the next column to provide a formatted date from two other columns.
  17. Copy and paste the Date function to the remaining rows in the column.
  18. Set one column to a specific Date format.
  19. Set one column to a specific Numeric format.
  20. Set a second column to a different Numeric format.
  21. Set a Total value for three columns.
  22. Highlight all missing values in a set of columns with a yellow background.
  23. Set the column widths of all columns to the maximum to show all contents.
  24. Set any columns more than 60 characters wide to 60 characters.
  25. Freeze the top row.
  26. Filter the top row.
  27. Create a graph of 3 non-contiguous columns.
  28. Set X and Y axes to specified values.
  29. Save/Close the file in Excel format.
  30. Copy the file to an internal share.
  31. Send an email to two email groups indicating the process is complete.

How long would that take you to accomplish after you got really good at it? 5 minutes? And if you had 5 files with varying sizes and steps to perform each day, how often would mistakes occur? At what point would your mind wander off to its happy place?

Would you prefer to have it done in 10 seconds, instead? Without tying up Excel, an employee or a workstation at all? We have programs processing 200,000+ rows and 50+ columns (50x more data than above) in just a couple minutes. And these programs are performing more steps, validating more data and sending summary emails with clear pass, warning and failure messages.

Of course, not all of our programs are like this. Some are simpler, some are much more complex. Some happen in a fraction of a second, others run for several hours. The point is we do a lot of it. In fact, we’ve done enough of it to become experts at processing data of all kinds.

If this piques your interest, hit our Contact page and find out if we can help.

© 2003-2021      Custom Visuals, LLC      Privacy Policy      Sitemap