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

Advertisements

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

Websites For The Week of January 29th

January 28, 2012 at 2:30 pm | Posted in Job Search, Mental Health, Positive, Weekly Readings | Leave a comment
Tags: , , , ,

 

 

 

 

 

Previous weekly website lists can be found here.

This week’s theme focuses on the job search and the winter blues. I know it’s been unseasonably warm here in Maryland, but even so we still all get those Winter blues.  Those of us job searching it can feel worse.

  • For just plain old resources for beating the winter blues check out this article.
  • There’s a step by step guide on Forbes magazine’s site.
  • More tips in a shorter form are here.
  • How to bust those blues in general in between filling out those applications.
  • Get some general tips for taking care of yourself if you have the job search blues, no matter the season are here
  • Since you’re more than a person looking for a job, if you have the winter blues or SAD (Seasonal Affective Disorder) there’s lots of info on WebMD

 

Finally, here’s a picture to meditate on to remind you that winter isn’t forever.

That’s it for this week. See what you like here. I’d love to hear from you about it. This week’s list is on a subject that I really think people can contribute to. Remember you can submit a website for the Sunday Readings on my How-To/Forum page.

Websites for the Week of August 21st

August 21, 2011 at 3:14 pm | Posted in Economy, Weekly Readings | Leave a comment
Tags: , , , ,

Previous weekly website lists can be found here.

This week is another theme week.  I’ve learned after almost six months of being unemployed that one of the largest budget areas is food.  I don’t know whether a lot of website have popped up because of the need or I just never had the need. This week’s list is became longer than earlier ones so I will probably be doing a Part Two next week. All these websites are now in my bookmarks.

Last week I mentioned a site for planning inexpensive meals.  This week I’d like to add to that list and expand to frugality in general.  Remember once you do get a job, these ideas will continue to help you save.

  • To improve your frugality skills .  Tips and tricks for living the good life on a budget.
  • To have a one-stop website for saving .  This site describes itself as, ‘cheap is good, but free is better’. Tips, tricks, ways to get free samples and printable coupons.
  • To get and share ideas about saving money .  There are two sections to the site that are helpful, the forum for sharing ideas and the blog for learning .
  • To  make saving money as painless as possible.  This site provides help on everything from saving during the holidays to baby care to swapping.
  • To focus on  finance, family,and career.  The site claims to make frugal cool and covers things like 0% credit card transfer to frugal ways to teach your kids to love school.

That’s it for this week. See what you like here.  I’d love to hear from you about it.  This week’s list is on a subject that I really think people can contribute to.  Remember you can submit a website for the Sunday Readings on my How-To/Forum page.

Work Scenario #6 – Cash

August 18, 2011 at 9:38 am | Posted in Business Ops, Work Scenerios | Leave a comment
Tags: , , ,
Disclaimer: So that some work scenarios make more sense, I’d like to describe the organization I worked with. A mobile mental health team served severe and persistent mentally ill people in a county in Maryland. Some descriptions may seem vague or cryptic; it is to preserve the identity of clients, organization, and co-workers. Some documents are redacted via pixilation. My specific duties will always remain intact.

As I mentioned in my check scenario nothing needs more attention to detail and protocol than handling money, this time the money involves internal spending, petty cash.  As in my last scenario, this responsibility was purely mine, therefore I could create what worked for both me and the organization under very little supervision.

Our petty cash was not just for office use, but also to be used as loans to our clients.I was responsible for the fund’s disbursements and subsequent replenishment once it reaches near-depletion.   In addition, I had a second ledger to keep track of loan repayments.

I quickly developed and implemented the following process:
When petty cash was paid out I there were two options for tracking:

  1. Create a receipt, included the company code for the reason the money was paid.  Since this was from a booklet, copies were created when written.
  2. Used a store or Internet receipt that was given to me. Copies of these were done on the office equipment.

Two to three times a month I made the reimbursement request.  The original receipts had to be presented to the Accounting department.  Information from those payouts were entered into a spreadsheet I decided that the most helpful data for validation would be, Internal code, payee, date, amount, and any notes needed for clarification.  Loans were designated in red.  These procedures had to be observed for fund disbursements, which include the documentation required for the approving authorities.

The spreadsheet was printed out and turned into the accounting department along with the receipts.
When ready, since I was the designated custodian responsible for disbursing the fund and safeguarding its physical security, petty cash checks were made out to me.  I cashed the check.  I never requested bills over twenty-dollars.  More often than not our payouts from petty cash were ten dollars or less.  Then the process started all over again.

When training temps and new administrative assists I would include my process and sent the spreadsheets to them to keep our petty cash protocols consistent.

 

Enhanced by Zemanta

Work Scenario #5 – Entitlement Checks

August 1, 2011 at 2:54 pm | Posted in Position Description, Work Scenerios | Leave a comment
Tags: , , , ,
Disclaimer: So that some work scenarios make more sense, I’d like to describe the organization I worked with. A mobile mental health team served severe and persistent mentally ill people in a county in Maryland. Some descriptions may seem vague or cryptic; it is to preserve the identity of clients, organization, and co-workers. Some documents are redacted via pixilation. My specific duties will always remain intact.

Nothing needs more attention to detail and protocol than handling money.  This and the next work scenario will be addressing those responsibilities.  Our organization was often the representative-payee for clients receiving Social Security checks for disability.  The government has several laws and rules about this distribution.  I attended a one-day seminar given by the regional Social Security Office before I starting the duties as described below.

I was given the responsibility of the distribution for the clients in our office.  Approximately 30 of our clients needed us to take care of their money.  Although how monies were allocated were decided by their primary contact, I had to know the client’s needs just as well so I could be an asset to a co-worker that may be struggling with this decision.  From start to successful completion was the following three-step process.

Step One:

For me, creating a spreadsheet and importing or entering data was always a good start and it wasn’t any different with this task.  My spreadsheet tracked the funds for a year, with a worksheet for each month.  The most relevant information about a client were included, names, amounts of checks, what each cut would be, and a reconciliation of funds for the end of the month.  My data came from the office manager, that received the physical checks, and my co-workers requests.  My office manager, who trained me five years ago, already had a plan that worked well on getting me the information I needed.  It was up to me to make it all work in our office.

By exchanging details via forms I created, letters from the Social Security Administration, and the previous month’s data we could all do our jobs more satisfactorily.  By knowing this information, I could send out a team-wide email listing the money expected for the month for each client, any loans from us that had to be paid back, and any notes that would help on the decisions to spend money.  When my co-workers read the email, they knew to fill out the request forms for me.  Those requests were sent to me to, reconciled against the money available, returned for corrections as needed, finally, requests were sent to the office manager.  The deadline to get all this done was around the second Monday of the month.  The process then was out of my hands until the checks were sent to me at the beginning of the following month.  Then the second part of the plan of action began.

Step Two:

Checks got to our office a day or two before the distribution date.  Once I had them, checks were copied, attached to my copies of the requests after making sure that the checks were cut correctly, and alphabetized.  If there were any problems in how a check was written, I voided them, put in a correction request, and returned them to the accounting department.  I removed all checks for payments for rent, our pharmacy, and any other bills I had to mail out for the client.  Rent checks were addressed and mailed that same day.  This entire process was time-consuming; in any given month, there could be as many 120 checks.  It was always one of my better-planned days of the month.

Step Three:

The product of this preparation was an ability to give out the checks to clients in a smooth and seamless way.  ‘Check Day’ was always very busy and we all still had our daily responsibilities to regard.  Clients could come into the office to pick up their checks or they could be delivered.  For the ones that came to the office I had a sign out sheet, answered any questions, and made small talk, that’s the easiest part of the job.

Once I put this plan into place ‘check day’ ran smoothly and any problems that arose could be easily handled.  Information on hand and a good rapport with the Social Security offices in the county helped me do my job.

Enhanced by Zemanta
Next Page »


Entries and comments feeds.