Contextures

Contextures News

Learn Excel Macro Basics

May 9, 2017

Learn Excel macro basics, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.

Quickly Copy Data

If you need to copy specific data from one worksheet to another, an Advanced Filter can do the job quickly, with no macros required. Set up your criteria, such as minimum quantity, and product name. On the sheet where you want the extracted data, add column headings -- you can choose some or all of the columns from the source data. Then, use the Advanced Filter tool to copy the data, based on your criteria and headings.

There are written instructions and a few videos on my website, so if you haven't used Advanced Filters before, try them now, to see how powerful they are.

advanced filter move data

Excel Macros

Do your Excel files have macros in them, or are most of them macro-free? About 50% of mine have macros, and that saves time when doing repetitive tasks like multi-column filtering or sorting, or complex tasks like extracting data and building reports.

If you haven't started making your own macros yet, there are a couple of free resources this week, to help you get started.

  • Excel Macros for Beginners, by Allen Wyatt from the Excel Tips website. There are 6 free videos that give you a basic introduction to macros, and show how they can help you work more efficiently.
  • Getting Started with Macros: Join Jon Acampora (Excel Campus) for a free online webinar -- The 7 Steps to Getting Started with Macros & VBA. Sessions are available until Friday, May 12th, so pick the date and time that works best for you.

Both Jon and Allen have full Excel Macro courses too, but take a look at their free material, even if you're not ready to invest in a paid course. And if you are ready, their free resources will help you decide which instructor will be a better fit for your learning style.

Your Feedback

Thanks to Van, who sent his favourite EOMONTH formula. His company's weeks run from Saturday to Friday. Van's formula finds the Friday in each week, or the end of the month date, if it comes first.

  • =MIN(EOMONTH([@Date],0),[@Date] + 7 - WEEKDAY([@Date],16))

See last week's EOMONTH formula, and more Excel Date Functions.

end of month formula EOMONTH

Excel Articles

Here are a couple of recent Excel articles that you might find useful.

Excel Skills - This Forbes article is called Excel Skills for Economics Majors, but it has great tips for anyone who uses Excel. In another article, Eric Andrews describes what it means to master Excel -- do you agree with his list? (Level - All)

Excel Experiment -- One of the managers from Microsoft's Excel team had fun using Excel to send instructions to a Micro:Bit (small micro controller for education). Take a look, to see where you can go with Excel in the future. (Level - Advanced)

Also see: My Excel Products || Excel Events || Excel Humour || Previous Issues

Out for Lunch

We went out for lunch last week, to celebrate our anniversary, and were lucky to pick the only day that it wasn't pouring rain. After lunch, we strolled through the garden, enjoying the beautiful day, and burning off 4 or 5 calories from our meal. And then it was time to head back to the office, for more fun with Excel!

weekly photo

That's it for this week! If there are topics that you'd like to see covered in future emails, please let me know.

NOTE: If you have trouble with the images or links in this email, paste this URL into your web browser, to see the online version: http://www.contextures.com/newsletter/excelnews2017/20170509ctx.html

Debra
ddalgleish @ contextures.com

P.S. You can choose to get the full Contextures news package (news and occasional special announcements), or the basic news package (news only). Click either link to change your news option.

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: July 21, 2017 9:39 AM