Favorite Tools #1 – Macros

March 2, 2012 at 3:00 pm | Posted in Tips and Tricks, Tools | Leave a comment
Tags: , ,

Nothing helps an Admin like streamlining regular repetitive or tedious tasks.  Macros in Word and Excel are necessary tools to cut your time in half using automation.
The most effective macros are well planned,  You may want to start by writing down the sequence of steps and commands you want the macro to perform.  You may even want to ‘practice’ before actually recording it to be sure your keystrokes, clicks, and commands produce the desired results.
Recording macros is easier than coding VBA from scratch and it works just as well and has the added benefit of saving time. I look at it as a way to create my own tools.  Here are some that I’ve used that worked for me.
I am assuming here that you are familiar with the Macro menu interface for recording and using them.  If not, check out the resources at the end of the article.

1. It seems inevitable that when you’re entering data into a spreadsheet that the rows and columns don’t fir the data correctly.  This macro quickly corrects that problem and gives the spreadsheet a cleaner look.
Start out with a spreadsheet that has rows and columns are too narrow or too wide.  If you don’t have one already, just purposely make one.  Here’s an example of what I used

  •   Select the cornerstone on the worksheet to select all cells (or Ctrl+A twice). Be sure that all cells remain selected.
  •   Double-click between two column headers. Be sure your cursor is positioned between the two letters and appears as a horizontal double arrow.
  •  Finally, double-click between two row headers. Be sure your cursor is positioned between the two numbers and appears as a vertical double arrow.
  • I like to finish up my Excel macros with Ctrl+Home this places your cursor in cell A1. This step is optional, but it’s always good to know where your cursor is going to end up when your macro completes. Then stop recording.

2. Another common task/problem I have in Excel is getting the spreadsheet to print out just the way I want it, usually to have it fit on one page and centered.  So I used the following macro to help me out.

  • Click on File in the Menu Bar, click on Page Setup.
  • Click on the Page tab at the top of the Page Setup window and select the orientation (portrait or landscape)
  • Click on the Margins tab at the top of the Page Setup window and set the margins (I have mine set to horizontal and center page)
  • Optional: Click on the Header/Footer tab at the top of the Page Setup window and select a header and a footer from the drop down lists
  • Click on the Sheet tab at the top of the Page Setup window. If you would like the top row of the spreadsheet to repeat on every printed page, click once in the “Rows to repeat at top” box and type $1:$1 If you prefer to have the grid lines showing, click once to place a check in the grid lines box
  • Click on OK.

Again, if you choose, click Ctrl+Home to move the cursor to A1, then stop recording.

3.  As the person who writes the most business correspondence, the name and address of the company was something written often. This macro inserts that information at the cursor position. It’s the best example of the simplest macro, typing and formatting four or five lines of text with a keystroke or a punch of a button.

  • Open a blank document using the normal template.
  • Set any formatting options you want such as font color or highlighting.
  • Type out the address as you want to appear in all documents
  • Now stop recording

Those are just a couple of examples of what I use.  I’m sure that every reader has a task that needs automation, just write down the steps, record a macro, and save time!

Now for the definitions of the day

tool
[tool]
noun
1. device for doing work
2. means to an end

sim·pli·fy
[ símpla fi ]
transitive verb
1. make something easier
2. reduce expressions to simpler terms

ef·fi·cient
[ i físh’nt ]
adjective
1. well-organized
2. able to function without waste

Other Resources

  1. Basics of recording  macros in Excel or Word
  2. Video from the ‘For Dummies” series
  3. Microsoft’s Macro Info Page

Let’s Be SMART About This

February 3, 2012 at 3:39 pm | Posted in Business Ops, Tips and Tricks | Leave a comment
Tags: , ,

I’m sure that many assistants know about the mnemonics to help them improve their productivity and set up projects.  Here is one of my favorites.  These tips can fit many different types of objectives.  It’s a step-by-process to successfully complete any goal by detailed planning.

S.M.A.R.T.

Specific: This is where the definition of the goal/problem solving process begins.  Specific goals are more easily accomplished.  This step uses the five W’s as the best way to focus and keep the goal action oriented, basically what would you like to see happen.  I think my favorite ‘W’ is How because when the benefits of the solution are considered I can focus even more on the other ‘W’s and the next steps.

  1.    Who is involved?
  2.    What could some obstacles be?
  3.    When should it be completed?
  4.    Why is this an important goal?
  5.    How will I get it done/benefit from the solution?

Measureable:  Establish the criteria for measuring the progress of your goal.  This is needed to set a schedule for due dates and to stay on track.  Being a list maker, this step keeps me motivated.  If there is a list of tasks or meetings associated with my project, deleting it from Outlook or crossing it off a hardcopy list gives me that feeling accomplishment and the feeling that I’m on the right track because objectives have been met.

Attainable:  This step is the decisive test for me.  The skills, tools, resources, and even financing are considered here.  Even if you have a good problem solving idea or a goal that you feel is important, it may not be doable now.  Here you can set yourself either up for success, or failure, so be honest about your needs.

Results-Oriented [some people use Relevant or Realistic here]:  This step keeps things moving towards success.  Each ‘sub-goal’ has to have the same importance when it comes to results. For example, if the third part of your solutions isn’t completed or doesn’t have the results you expected the goal would break down from there on.  There won’t be any substantial progress without it.

Timeframe:  Of course you’re going to have to set up a start and end date.  Don’t forget to track key milestone dates as you keep working.  This is a great way to remind you of the urgency of your desired accomplishment.

pro·duc·tive
[pro-duck-tiv]

adjective

1.    producing something abundantly and efficiently
2.    producing satisfactory or useful results

smart
[smaart]

adjective
1.    showing intelligence and mental alertness
2.    shrewd and calculating in business and other dealings

plan
[plan]
1.    a method of doing something that is worked out in advance
2.    something that somebody intends or has arranged to do


Entries and comments feeds.