If you want to make sure you receive a notification about my next video about macros, just enter your name and e-mail address and click ‘submit.’ (This is different from any current ExcelTips subscription you may have.)

Name:

E-mail Address:

 

Share Your Comments!

I’d love for you to share your comments below. Particularly, I’d love for you to share the most boring, repetitive task you’ve had to do using Excel. I’ll interact with you here, and I may choose your task to talk about in my next video. (Scroll all the way to the bottom to leave a brand new comment, or you can reply to the comments of others.)

31 thoughts on “Video 1: Why You Need Excel Macros

  • January 8, 2018 at 9:00 am
    Permalink

    I like the quality of your video. One of the most”boring”; certainly the biggest time killer, I go through on a regular basis, is cleaning up the messy spreadsheets I get from coworkers and other people who send me their work. (To think I used to believe that everybody who creates a spreadsheet is an expert on Excel!) These are things like unmerging cells, consistent formatting of numerical fields, removing extra spaces in cells, splitting data into more usable fields (as in the case of a mail merge), etc.

    Reply
    • January 8, 2018 at 9:07 am
      Permalink

      I feel your pain, Stanley! If you are able to “systematize” what you go through to clean up the worksheets, then you can make a macro to go through that system for you. (That’s always the biggest task–coming up with the concrete steps you take each time.)

      -Allen

      Reply
  • January 8, 2018 at 10:28 am
    Permalink

    Do you go into more depth in writing macros in your videos? I only know how to make macros by using the record button and having excel record my actions to do the redundant formatting of reports. I don’t know how to edit the macros once they are done and that is frustrating!

    Reply
    • January 8, 2018 at 11:01 am
      Permalink

      Yes I do, Lisa. You’ll want to watch the next few videos, as I get into some of this. (I get into it a *lot* in the course itself.)

      -Allen

      Reply
  • January 9, 2018 at 12:24 am
    Permalink

    Thanks for the video. The most repetitive job I had to do in Excel is to
    * copy lines of delimited string (generally coming from text files) and paste into excel,
    * In data tab, use “convert text to columns” option
    * choose the radio button “delimited”, click next
    * click on “other” in delimiters group and choose either “*” or “~” as per the string copied.
    * apply a border to the whole set that got copied.
    * apply a different back color and make it bold, for the first row which normally is the header.
    * apply a different back color for the last row which is normally the total.

    Reply
    • January 9, 2018 at 6:32 am
      Permalink

      It is great that you are able to detail the steps you need to take. That’s the first step in accomplishing any task with macros–visualize and write down the steps.

      -Allen

      Reply
  • January 9, 2018 at 4:14 am
    Permalink

    Hi Allen,
    The most repetitive task I have to do is when I receive a list consisting of a date, followed by a variable number of items (typically between 3 and 10), then another date with another list of items and so on for about 15 such dates – you get the idea. My task is to add the most recent date in a cell alongside each of the items, then eliminate each of the rows with just dates in so I can subsequently manipulate the data in a meaningful way. Hope this makes sense.
    David

    Reply
    • January 9, 2018 at 6:33 am
      Permalink

      Yes, with a firm understanding of how you can manipulate data in macros, this could be handled quite easily!

      -Allen

      Reply
  • January 9, 2018 at 5:12 pm
    Permalink

    I want to understand and be able to create a macro that will allow a file to run a PowerPivor query and to load upon opening, save the information and closes the Workbook. I plan to use Windows Scheduler to run this file at 2 am so the following day I can run my Power BI on that sales info of the previous day.

    Please help.

    Reply
  • January 9, 2018 at 7:16 pm
    Permalink

    Hello Allen – I want to use Macros to help myself and a co-worker with a daily task. We both log-in to our bank website and download prior day banking information. The files download as Excel 2010 files and the first step is to enable editing. Then we change the page layout to meet our needs. This is where we change our use of the information. My co-worker prints it out with page breaks and distributes necessary information to our credit department. They return it with back-up as to how the credit card and ACH funds can be deposited.
    I save the daily banking information for every business day. In doing so, I adjust the format and then change the tab name and then copy it to the daily spreadsheet. I repeat this process for a total of four reports during the course of the day and it has become a bit monotonous. I have been doing this for just over two years and I want to learn Macros to make this more automated. I suspect I will need at least four Macros, one for each report since they each have different names. If I can learn this and then show the accounting team that I manage, I hope it can spark some forward thinking into other possibilities. I also hope to gain enough information from the free videos to accomplish this due to time constraints.

    Thanks!

    Reply
  • January 11, 2018 at 8:01 am
    Permalink

    I have a large spreadsheet containing patient information that have received medications however, some of them have received the same prescription within a month and only a few days part. My task was to determine who received the multiple prescription and how many days apart and display the number of days and the number of prescriptions for the date range. While I cannot share this spreadsheet due to HIPPA this is what I did. 1.) I sorted the spreadsheet by the patients unique identifier then the second level of the sort I did by date of the prescription. 2.) I inserted a column by the Rx Number in the blank column I put in this formula: IF(COUNTIF($L$2:L2,L2)>1, “Duplicate”,”Original”), it will put Original in the first RS number then duplicate in any that are duplicate RX Numbers, 3.) After I completed that, I removed all that were not duplicate, then did I did a count of the duplicate RX numbers using the subtotal on the Data tab .4.) I then inserted another column by the prescription date and did this formula: DATEDIF(H2,H3,”d”) it told me the number of days between the refills. All those that were <5 were highlighted for further review. I'm not sure if there would have been a easier way to do this but I had to do for each worksheet, they were broken out by the month. (I inherited this data to work with). If there could have been an easier way to do this I would really like to know I believe this is something I will be asked to do going forward. Thank you.

    Reply
  • January 13, 2018 at 8:23 pm
    Permalink

    Lesson 1 comment:
    Most boring and repetitive task: Every month I have to import numerous .txt files downloaded from another system and use the wizard to import, manually changing the data format for several columns from General to Text because they represent codes composed of digits with leading zeroes. Then some minor formatting edits such as adjust column sizes, freeze Panes to fix the title line, and perhaps format some columns as $s, usually with no decimals, numbers with commas, etc . Then the spreadsheet is ready for me to mail to my end-users.
    A slightly more complex example is when the next step is to create a pivot table to summarize the data, but with basically the same rows, columns, selections, and summaries every month.
    Thank you for the macro videos.

    Reply
  • May 7, 2018 at 8:26 am
    Permalink

    Will the video tutorials be downloadable so that I can use them over and over again? I have found that I don’t have the time I would like, even after I have signed up for a video tutorial like this, to stay with the curriculum schedule. As a result I often forget what I learned earlier and must go back multiple times to review and repeat.

    Thanks,
    Michael

    Reply
    • May 7, 2018 at 9:10 am
      Permalink

      Good question, Michael. For the course itself, I do provide a version of the course where you can download the videos. (The “base course” is streaming only, non-downloadable, to keep costs low and in recognition of the fact that not everyone needs the downloading capability.)

      -Allen

      Reply
  • May 7, 2018 at 8:58 am
    Permalink

    I have a list of vendors and payments made for 37 different sets of books.

    There is no uniform manner to the Vendor Code and Vendor Description across the 37 sets of books (some upper/lower case issues, some punctuation issues, etc.)

    I am hoping to get all Vendor Descriptions (“John W. Smith Sr.” on one set of books, “Smith, John W. Sr.” on another, “JOHN W SMITH SR” on another) in sync across all sets of books.

    I am looking for options.

    Reply
  • May 7, 2018 at 2:00 pm
    Permalink

    I use Excel to maintain Household Inventory using Microsoft Access data base.and I also use excel to maintain my company’s expenses and income (breakout). Lot of redundancy in both areas.

    Reply
  • May 8, 2018 at 3:33 am
    Permalink

    Great video I have passed in onto other colleagues

    Reply
  • May 8, 2018 at 10:09 am
    Permalink

    Thanks for this video and the ones to come. Very helpful.

    Reply
  • May 8, 2018 at 7:06 pm
    Permalink

    Thanks for a fantastic video! I can’t wait for the other three.
    I have a series of spreadsheets that perform various functions for my job control in a boutique consulting engineering practice. Several of them have common fields or use results from some of the other spreadsheets. I would really like a system where I can enter data once and have it automatically transfer across to the other sheets, thereby avoiding multiple data entry steps which is extremely time wasting. If you have anything that can help I would greatly appreciate it.

    Reply
  • May 8, 2018 at 10:32 pm
    Permalink

    Hi Allen
    I work for a training company and on a regular basis i need to generate registers but the current system we have does not “Format” the registers in an acceptable layout. Some of the biggest challenges is to include our company logo on the top of the register and a “Forward to” message at the bottom.
    Furthermore, the number of candidates on the register varies so I need to determine for how many lines to apply the macro to.
    Looking forward to forthcoming videos and hope to learn a lot.
    Kind regards
    Sarie Wessels

    Reply
  • May 10, 2018 at 11:22 am
    Permalink

    My most boring and repetitive task is entering the time employees come and leave work into an excel sheet, Another boring task is entering how much material was produced at the end of a shift into an excel sheet.

    Reply
  • October 1, 2018 at 8:24 am
    Permalink

    Would be great to build a macro that enables conditional formatting of cells into 4 specific subsets (and anything that falls outside these to be unformatted):
    1) Cells containing constants
    2) Cells containing formulas that only use other cells within the existing worksheet
    3) Cells containing formulas that use cells from other worksheets within the same excel file
    4) Cells containing formulas that use cells from external worksheets in other excel files
    The idea would be that the above 4 instances would, for example, have different text colour, but if the contents of the cell were to be replaced by a different instance, then the text colour would automatically change. For example, if I overtyped a cell containing a formula with a hardcoded value (ie constant), then the text colour would change from black to blue (or whatever I chose as the relevant colours for the two instances). I have found a way to do this with respect to instances 1) and 2), but I cannot find a way of distinguishing instances 3) and 4) from 2) (ie a formula is a formula, I cannot find macro code or anything else that would allow conditional formatting to distinguish between a formula that only contains references to cells in the existing worksheet for a formula that contains external cell references, be those from another worksheet in the same file or from a worksheet in a separate file).

    I have found a way of

    Reply
  • October 1, 2018 at 8:27 am
    Permalink

    cleaning up macros that were recorded, IE
    change explicit references to relative ones

    Reply
  • October 1, 2018 at 1:29 pm
    Permalink

    I need to take data from about 8 different feed (8 organisations) some come in csv others in html.
    I need to sort each of the feed and populate a workbook (1 work book for each organisation) I then need to consolidate some totals into an otherbook to update same

    Reply
  • October 2, 2018 at 12:12 am
    Permalink

    I need to change date format from US : 09/28/2018 to UK 28/09/2018 so it can be sorted. This can sometimes work with flash fill but it doesn’t always function.

    Reply
  • October 2, 2018 at 3:51 am
    Permalink

    Hi Allen,

    I’m a great supporter of your video’s which continue to help me in improving my VBA coding, and I remain interested in any new chapter you could cover, so please enroll me for your next videos.
    I was a bit sad when you presented the “standard” way of learning macros by showing a book form Wiley Press House (?), which was most probably John Walkenbach’s book(?), and you threw it away with a bit of disdain. I believe your video courses are the best and most promising way to enter the VB world, but an excellent reference book such as John Walkenbach’s one can possibly complete details of VBA. By the way, Allen, it was you who advised me about this book, and I never regretted it;

    Reply
    • October 2, 2018 at 6:44 am
      Permalink

      I would never treat John’s books with disdain, Reinhold; I think they are great! At over a thousand pages, though, they are not the stuff from which most people — especially beginners — can expect to learn how to use macros. They are a GREAT source of information after one has the basics down, however. (Like, for instance, AFTER someone has taken my courses!)

      -Allen

      Reply
  • October 2, 2018 at 9:12 am
    Permalink

    I have been tasked with learning how to write macros. I am still searching for ways to automate tasks on our spreadsheets to save time, frustration, and improve accuracy.
    so far your style is straightforward and easy to follow. I am in the high end window fabrication business.

    Reply
  • October 2, 2018 at 1:32 pm
    Permalink

    The most challenging effort is to take over an inherited spreadsheet that has macros that were originally created with VBS and then modified over the years by different people where changes were not made with VBS, but most often changed manually.

    Reply
  • October 3, 2018 at 7:14 am
    Permalink

    I capture attendance daily in a large factory and have to calculate wages based on the attendance figures for the month.

    Reply
  • October 3, 2018 at 10:03 pm
    Permalink

    i have to create a compliance report — basically running a report that is generated from an e-learning module and adding it to a compliance report — basically, turning two workbooks into one… in addition, as staff are hired, transfering or terminating, i need to update the reports… Time consuming and looking for a more efficient “leaner” way of completing/comparing reports. Thanks for sharing you videos. They’re informative and to the point!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *