Udacity - Excel Fundamentals 1. Macro names Keep macro names short but descriptive , especially if you record a lot of macros, so you can easily identify them in the Macro Dialog Box. The system also provides a field for Description, though not everyone uses it. Macro names must begin with a letter and cannot contain spaces, symbols, or punctuation marks.
After the first letter, you can use more letters, numbers, or the underscore character, but the maximum length is 80 characters. In the Record Macro dialog box, enter a macro name and Shortcut Key if applicable.
The dialog box disappears, and the Record Macro button changes to a Stop Recording button. Enter your keystrokes, formulas, and so forth, then click the Stop Recording button and run your macro. Always begin at Home For Absolute reference macros, always begin in the Home position cell A1 —with your cursor and your data. If you saved your macro in the Personal Macro Workbook recommended , you can reuse this macro on other worksheets with similar data.
The best free software for your PC ] Example: Imagine that every month you receive dozens of spreadsheets from all your branch offices that you must first combine, then organize, and calculate to produce one monthly report.
You can write a macro to perform all of these functions, including opening and combining all the worksheets into one combined spreadsheet. Create a new workbook in Excel e. Your cursor is automatically located in the Home position A1.
Do not move it to accommodate titles, field headers, or anything else. It must be positioned in A1. The data begins in A1 and ends in G Continue this process until the data in all workbooks are copied into your April Report spreadsheet. Do not copy the formulas or the formats. Just copy the data and run the macro. After the data is calculated, you can Insert Rows to add column headers, spreadsheet titles, and other information.
Then you can format the column widths, change fonts, add attributes, and place borders or boxes if you'd like. You can also create an additional macro to perform formatting tasks. Using the mouse to navigate is more complicated and not as reliable.
When it comes to macros, use the mouse only for selecting or clicking menu options. Keep macros small and specific Keep your macros small and specific to the tasks at hand.
Also, if you combine all the tasks into one long macro and it fails, it takes forever to locate the point of failure. Step through an Excel macro with F8. In the April Report example, you wouldn't combine the calculation macro with the format macro. You'd use one macro to combine all the branch workbooks into one final spreadsheet, a second macro to calculate the data, a third macro to format the spreadsheet, a fourth macro to create charts, and perhaps a fifth macro to print.
If a macro fails, you can troubleshoot it by stepping through it one line at a time. Shrink the Code window down and place it on the bottom right side of the screen so you can see your spreadsheet behind it. Press F8 each time you want the macro to proceed to the next step.
The macro stops when it reaches the error. You can then repair the error or record a new macro. When you purchase something after clicking links in our articles, we may earn a small commission.
Read our affiliate link policy for more details.
You can learn to use it by taking an online course. If you don't know how to use MS Excel well, you should definitely learn. Once you know how to use Excel, you will have a handy tool that will boost your productivity in many ways for years to come. Top analysts and other professionals usually know how to use Excel. Would you love to be able to work through spreadsheets like magic?
I cannot say enough how freeing it is to be able to have an intuitive idea of what you want to do in Excel, and be able to execute that idea through a mastery of the tool. Microsoft Excel is the easiest and best way to do many tasks. Being comfortable using Excel is a great skill to have. You have the power to organise and manipulate almost any kind of digital information. Once you have mastered Excel, you'll never look back. You will be more productive and employable.
With Excel, you can: Do quick calculations and create charts Organise personal information, such as accounts and usernames Create budgets and record expenses accounting Create plans and timetables Read information supplied to you as data Sort downloaded information, such as bank transactions Build databases and do analysis in a work setting.
How to Learn Excel Learning and becoming better at using Excel can be done in multiple ways. You can look up how to do things using the help menu or by finding an online tutorial. Training courses: MS Excel is something you can become good at but never truly master. It evolves, along with the potential uses for it. The best Excel users make learning an everyday part of working with spreadsheets.
Free Online Training Courses Free Excel training courses typically use video presentations, which are organised into modules.
You can learn about a specific topic by watching a video and trying to repeat the actions in Excel. Online Microsoft Excel Courses For an engaging and satisfying learning experience, there are some affordable online courses. They beat free courses because the providers have invested more in learning materials.
Some courses also include interactive lessons. Paying for a course produces better learning outcomes for two main reasons. Paid courses are superior in general, with more highly developed content. You're also much more likely to complete the course because you're financially invested. That investment encourages you to keep learning, finish the course, and get the valuable skills payoff.
Excel University Training Certificate finance and accounting Excel University offers self-paced training that's geared towards finance and accounting activities. Designed by a CPA, the programs show how to leverage Excel to automate financial tasks and produce accurate, well-presented results. The courses feature lecture videos, homework activities, and instructor-moderated forums where you can ask questions.
The training combines self-paced study with an interactive, social online experience. Go there Excel with Business Excel with Business offers a huge range of courses under monthly and annual membership options. The beauty of joining Excel with Business is that you can try some of their free or cheap training courses for a month at a low price.
Then keep going only if the style of learning is working for you. Excel with Business are all about providing practical, effective training. They show you how to do truly useful things instead of trying to teach every little capability MS Excel offers.