• COVID-19
  • Insights
  • Who We Help
    •   Industrial Automation
    •   Manufacturing & Distribution
    •   A&E Professional Services
    •   International Businesses
      • ◦   Expanding Outside the U.S.
      • ◦   Expanding to the U.S.
  • Services
    •   COVID-19
      • ◦   Cash Flow Confidence Assessment
      • ◦   Maximize Your Loan Forgiveness
      • ◦   5 Key Focus Areas
      • ◦   COVID-19 Resource Center
    •   Client Accounting
      • ◦   Software Solutions
      • ◦   Accounting Support
      • ◦   Reporting
    •   Tax
      • ◦   R&D Tax Credit
      • ◦   Tax Credits & Incentives
      • ◦   Tax Structure
      • ◦   Federal Tax
      • ◦   State & Local Tax
      • ◦   Personal Tax
      • ◦   Other Tax Filings
    •   Advisory & Assurance
      • ◦   Assurance Levels
      • ◦   Reporting
      • ◦   Employee Benefit Plan Audits
      • ◦   Technical Accounting & Reporting
    •   Consulting
      • ◦   Data Analytics
      • ◦   Transaction Services
      • ◦   Business Planning
      • ◦   Succession & Exit Strategies
    •   International
      • ◦   International Tax
      • ◦   Foreign Direct Investment
      • ◦   Global Expansion
      • ◦   International Accounting
  • Events
  • Careers
    •   Why C&M
    •   Students
      • ◦   Campus Events
      • ◦   Internships
      • ◦   Reach Beyond Program
    •   Experienced Professionals
      • ◦   Team member profile videos
    •   Opportunities
    •   Employee Journals
    •   Office Tour
  • About Us
    •   How We Help
      • ◦   Service Approach
      • ◦   Affiliations
      • ◦   Communications & Technology
    •   Meet Our Team
    •   Testimonials
    •   Our Videos
    •   Our Story
  • Contact Us
  • Subscribe
CHANGE COUNTRY:
  • United States
  • 中国
  • Client Login
Clayton & McKervey Logo
  • COVID-19
  • Insights
  • Who We Help
  • Services
  • Events
  • Careers
  • About Us
  • Contact Us
  • Subscribe
    • Most Recent Insights
  1. Home
  2. Insights
  3. Microsoft Office – Best Practices

Microsoft Office – Best Practices

Posted by Clayton & McKervey on February 17, 2012

Clayton & McKervey Clayton & McKervey

Microsoft Excel is the business community’s largest and most widely used office productivity program. The program offers a wide array of functions including: formulas, sorts, graphs, data imports, and pivot tables. These functions are used to manually create simple reports, or automatically used to create complex reports. Equally important and worth getting-to-know is Microsoft’s Access database program. Access is used to create, manipulate, store, and extract large amounts of data.

At the December 8, 2011, roundtable, CFOs, controllers, and business owners gathered to discuss best practices, share success stories and uses of the programs, and simply ask questions related to using Microsoft Office’s productivity suite. Ideas and comments from the attendees are summarized below.

How do most people use Microsoft Excel?

More than anything, people export their financial system data to Excel to review important financial information including sales reports, inventory and work-in-process, budgets, time and expense analysis, cash flow, commission calculations, and the detail of any general ledger account. Others simply use the program as a way of maintaining data.

How do I know if I am using Excel efficiently?

Many of the attendees questioned if they were using Excel to the fullest extent possible. To validate this, if you were given a sales report Excel with these headers: Customer, Product, Ship to State, Quantity, Date Sold, Cost and Sales Person, how would you go about finding the answers to the following questions: “What are my total sales in December to Company ABC?” “What are my margins on Product 1 in the first half of the year versus the second?” “How much commission do I need to accrue for Salesperson A, based on November’s sales?” If you answer, “I’m not sure, it’ll probably take too much time, so I’ll ask someone else to do this,” “I’ll just jump in and start scrolling through the data creating and adding figures on my 10-key,” or “I’ll just call my IT person and have them create a one-time-only report,” chances are there is a more efficient way to use Excel’s “reporting” (e.g., sorting, summarizations, pivot tables, etc.) tools.

What examples can you provide of ways to use Excel efficiently?

Using the same example from the last question, pivot tables can be used to answer questions.  Even further, the data sets (i.e., the set of data selected and highlighted within Excel to be used in the pivot table) can easily be modified and adjusted.

Have you ever performed the exact same task, in similar fashion, numerous times, to modify data or recreate a template? One attendee gave an example of how they’ve adopted Excel macros to help with their budgeting/forecasting process. On a yearly basis, the old procedure was to roll-forward the prior year budget template. Across multiple Excel tabs and line items, this task involved not only the removing of prior year financial data and replacing it with a “$0”, but retaining the integrity of existing formulas. This requires a significant amount of valuable time. Through trial-and-error, and without any programming knowledge, the attendee was able to select the record-macro-feature, which automatically captured all key strokes and mouse clicks from the user across the numerous tabs and lines within a file. By creating this macro, they were able to access the programmed function during the next budgeting period, at which point, selecting the “play”-macro-feature would automatically replicate this task without user interaction and save significant time.

Other participants provided even more simplistic, yet highly efficient uses of Excel, such as creating a bank covenants template. In this example, the participant needed to calculate monthly covenant reports based upon their financial results to determine compliance.  Instead of manually calculating results, they created an Excel template that allowed them to enter categorized results (e.g., eligible receivables, finished goods, total debt, etc.) from their financial system into specific cells that were referenced in another part of the workbook which automatically tabulate a visual “Yes” / “No” compliance result for each of the formulated covenants.

When should I use Access vs. Excel to maintain my data?

One example detailed a business user that was trying to maintain a spread sheet of their company’s large fleet of vehicles. The spread sheet was to maintain vehicle information (e.g., make, model, color, options, VIN number), maintenance data (e.g., oil changes, tire rotations, transmission flush/fills), insurance / tab data (e.g., premiums, deductibles, tab expiration dates, costs), and user data (e.g., employee ID, employee name, position, date vehicle was provided to them, business mileage usage, personal mileage usage, accidents history, etc.).  As imagined, for one vehicle, the spreadsheet could easily be 50+ columns wide with a range of possible data inputs. To make things more complicated, it was necessary to maintain detailed records of vehicle history (i.e., not only “What vehicles does Employee 22 own now?” but, “What did Employee 22 own in 2008?”). In addition, multiple users were requested to enter the data into the report themselves – which could create a slue of problems, especially relating to information validity and integrity (e.g., “How do I assign vehicle 27 to two employees to share?”). This example was a clear instance of when Access could be used to create a relational database that would protect the integrity of the data and allow multiple users to input data simultaneously. More importantly, the database would allow management to easily query data to answer questions. For example, a user could use Access to determine answers to some of the following questions: “What are my average premiums paid for Vehicle X?” “What is the accident history for Employee 22?” “How many vehicles do I have approaching the 100K mark, and thus require replacement and cash out-flow?”

In short, Access should be considered when there are multiple users, multi data tables, data input/output controls (i.e., data management) are required, and user-limitations are necessary.

Where can I go to learn more about using Microsoft Excel and Access?

There are many tutorials and training programs available. Examples discussed during the roundtable included:

  • microsoft.com (someone mentioned there are great pivot table videos there)
  • New Horizon’s Training Course (for day-long classes at different experience levels)
  • CFOOnline.com (for business uses and tips)
  • Lynda.com (subscription-based tutorials for business uses and tips)
  • Powerpivot.com (a free powerful pivot table add-on for MS Excel 2010)
  • TheExcelAddict.com (a daily email blast for those wanting to learn more)
  • Youtube.com (thousands of “how to” videos)

Are there any other productivity tips or tricks people are using in other Microsoft programs?

Various additional topics were quickly discussed:

  • eGrabber.com – capture contact information from email signatures and websites
  • “=HLookUp” and “= VLookUp()” – Powerful excel functions used to compare and grab data between two different excel tabs, given a common identifier (e.g., Inventory ID, Check No/ID, etc.)
  • Word’s Mail Merge – Used to create letters, labels and other information by pulling information from a database, such as Excel
  • Excel’s Text-to-Columns – Used to separate data that was exported into a single cell
  • Sharepoint – Allowing document sharing and collaboration via the web

Our team is always ready to help.

Please contact us for more information.

Clayton & McKervey

Clayton & McKervey

Contact Clayton & McKervey

related news

How to Calculate R&D Tax Credits

As we’ve seen in the first two installments of this series, business owners often miss out on the R&D tax credit opportunity and the bottom-line infusion it can provide. Many…

Read full story

Doing Business in Mexico: What to Expect this Year

Without a doubt, this year will be interesting for Mexico. To start, it’s an election year and we all know what that means…a lot of uncertainty. As the global pandemic…

Read full story

What Expenses Qualify for R&D Tax Credits?

The R&D tax credit is one of the most overlooked opportunities to boost your bottom line. Many business owners fail to claim it under the mistaken belief that they’re not…

Read full story

Clayton & McKervey Launches The Sound of Automation Podcast

Media Contact: Denise Asker, dasker@claytonmckervey.com; 248.936.9488 Southfield, Mich.—February 17, 2021—Clayton & McKervey, a certified public accounting and business advisory firm helping growth-driven companies compete in the global marketplace, is excited…

Read full story

Misconceptions About the Research & Experimentation Tax Credit

As companies put more emphasis on Industry 4.0 and business processes become more automated and accessible, the opportunities for Research & Experimentation tax credits increase. The Research and Experimentation (R&E)…

Read full story

Categories

Jump directly to the topics that matter to you most.

  • A&E Professional Services
  • About Us
  • Advisory & Assurance
  • Business Owners
  • C&M Press Releases
  • Careers
  • China Consulting
  • Clayton & McKervey
  • Client Accounting Services
  • Consulting
  • COVID-19
  • Data Analytics
  • Estate Planning
  • Expanding Outside the U.S.
  • Expanding to the U.S.
  • From the President
  • Industrial Automation
  • International
  • Manufacturing & Distribution
  • Mexico Consulting
  • Podcasts
  • Private Client Services
  • Tax & Tax Credits
  • Transaction Services
  • Videos

Authors

Read news direct from our managers and stakeholders.

    • Ben Smith
    • Beth Butchart
    • Bryan Powrozek
    • Carlos Calderon
    • Casey Haggerty
    • Clayton & McKervey
    • Dave Van Damme
    • Denise Asker
    • Eric Lin
    • Jim Biehl
    • Julie Killian
    • Kevin Johns
    • Margaret Amsden
    • Miroslav Georgiev
    • Nina Wang
    • Rob Dutkiewicz
    • Ruben Ramirez
    • Sarah Russell
    • Sue Tuson
    • Tarah Ablett
    • Teresa Gordon
    • Tim Finerty
    • Tim Hilligoss
    • Wendy Reedy

Additional Resources

Additional news from Clayton & McKervey can be found below.

  • Subscribe to our email newsletter
  • View upcoming events
  • Contact us to let us know how we can help you
  • Main Content
  • Related Insights

Microsoft Office – Best Practices

Posted by Clayton & McKervey on February 17, 2012

Clayton & McKervey

Microsoft Excel is the business community’s largest and most widely used office productivity program. The program offers a wide array of functions including: formulas, sorts, graphs, data imports, and pivot tables. These functions are used to manually create simple reports, or automatically used to create complex reports. Equally important and worth getting-to-know is Microsoft’s Access database program. Access is used to create, manipulate, store, and extract large amounts of data.

At the December 8, 2011, roundtable, CFOs, controllers, and business owners gathered to discuss best practices, share success stories and uses of the programs, and simply ask questions related to using Microsoft Office’s productivity suite. Ideas and comments from the attendees are summarized below.

How do most people use Microsoft Excel?

More than anything, people export their financial system data to Excel to review important financial information including sales reports, inventory and work-in-process, budgets, time and expense analysis, cash flow, commission calculations, and the detail of any general ledger account. Others simply use the program as a way of maintaining data.

How do I know if I am using Excel efficiently?

Many of the attendees questioned if they were using Excel to the fullest extent possible. To validate this, if you were given a sales report Excel with these headers: Customer, Product, Ship to State, Quantity, Date Sold, Cost and Sales Person, how would you go about finding the answers to the following questions: “What are my total sales in December to Company ABC?” “What are my margins on Product 1 in the first half of the year versus the second?” “How much commission do I need to accrue for Salesperson A, based on November’s sales?” If you answer, “I’m not sure, it’ll probably take too much time, so I’ll ask someone else to do this,” “I’ll just jump in and start scrolling through the data creating and adding figures on my 10-key,” or “I’ll just call my IT person and have them create a one-time-only report,” chances are there is a more efficient way to use Excel’s “reporting” (e.g., sorting, summarizations, pivot tables, etc.) tools.

What examples can you provide of ways to use Excel efficiently?

Using the same example from the last question, pivot tables can be used to answer questions.  Even further, the data sets (i.e., the set of data selected and highlighted within Excel to be used in the pivot table) can easily be modified and adjusted.

Have you ever performed the exact same task, in similar fashion, numerous times, to modify data or recreate a template? One attendee gave an example of how they’ve adopted Excel macros to help with their budgeting/forecasting process. On a yearly basis, the old procedure was to roll-forward the prior year budget template. Across multiple Excel tabs and line items, this task involved not only the removing of prior year financial data and replacing it with a “$0”, but retaining the integrity of existing formulas. This requires a significant amount of valuable time. Through trial-and-error, and without any programming knowledge, the attendee was able to select the record-macro-feature, which automatically captured all key strokes and mouse clicks from the user across the numerous tabs and lines within a file. By creating this macro, they were able to access the programmed function during the next budgeting period, at which point, selecting the “play”-macro-feature would automatically replicate this task without user interaction and save significant time.

Other participants provided even more simplistic, yet highly efficient uses of Excel, such as creating a bank covenants template. In this example, the participant needed to calculate monthly covenant reports based upon their financial results to determine compliance.  Instead of manually calculating results, they created an Excel template that allowed them to enter categorized results (e.g., eligible receivables, finished goods, total debt, etc.) from their financial system into specific cells that were referenced in another part of the workbook which automatically tabulate a visual “Yes” / “No” compliance result for each of the formulated covenants.

When should I use Access vs. Excel to maintain my data?

One example detailed a business user that was trying to maintain a spread sheet of their company’s large fleet of vehicles. The spread sheet was to maintain vehicle information (e.g., make, model, color, options, VIN number), maintenance data (e.g., oil changes, tire rotations, transmission flush/fills), insurance / tab data (e.g., premiums, deductibles, tab expiration dates, costs), and user data (e.g., employee ID, employee name, position, date vehicle was provided to them, business mileage usage, personal mileage usage, accidents history, etc.).  As imagined, for one vehicle, the spreadsheet could easily be 50+ columns wide with a range of possible data inputs. To make things more complicated, it was necessary to maintain detailed records of vehicle history (i.e., not only “What vehicles does Employee 22 own now?” but, “What did Employee 22 own in 2008?”). In addition, multiple users were requested to enter the data into the report themselves – which could create a slue of problems, especially relating to information validity and integrity (e.g., “How do I assign vehicle 27 to two employees to share?”). This example was a clear instance of when Access could be used to create a relational database that would protect the integrity of the data and allow multiple users to input data simultaneously. More importantly, the database would allow management to easily query data to answer questions. For example, a user could use Access to determine answers to some of the following questions: “What are my average premiums paid for Vehicle X?” “What is the accident history for Employee 22?” “How many vehicles do I have approaching the 100K mark, and thus require replacement and cash out-flow?”

In short, Access should be considered when there are multiple users, multi data tables, data input/output controls (i.e., data management) are required, and user-limitations are necessary.

Where can I go to learn more about using Microsoft Excel and Access?

There are many tutorials and training programs available. Examples discussed during the roundtable included:

  • microsoft.com (someone mentioned there are great pivot table videos there)
  • New Horizon’s Training Course (for day-long classes at different experience levels)
  • CFOOnline.com (for business uses and tips)
  • Lynda.com (subscription-based tutorials for business uses and tips)
  • Powerpivot.com (a free powerful pivot table add-on for MS Excel 2010)
  • TheExcelAddict.com (a daily email blast for those wanting to learn more)
  • Youtube.com (thousands of “how to” videos)

Are there any other productivity tips or tricks people are using in other Microsoft programs?

Various additional topics were quickly discussed:

  • eGrabber.com – capture contact information from email signatures and websites
  • “=HLookUp” and “= VLookUp()” – Powerful excel functions used to compare and grab data between two different excel tabs, given a common identifier (e.g., Inventory ID, Check No/ID, etc.)
  • Word’s Mail Merge – Used to create letters, labels and other information by pulling information from a database, such as Excel
  • Excel’s Text-to-Columns – Used to separate data that was exported into a single cell
  • Sharepoint – Allowing document sharing and collaboration via the web

Our team is always ready to help.

Please contact us for more information.

Clayton & McKervey

Contact Clayton & McKervey

related news

How to Calculate R&D Tax Credits

As we’ve seen in the first two installments of this series, business owners often miss out on the R&D tax credit opportunity and the bottom-line infusion it can provide. Many…

Read full story

Doing Business in Mexico: What to Expect this Year

Without a doubt, this year will be interesting for Mexico. To start, it’s an election year and we all know what that means…a lot of uncertainty. As the global pandemic…

Read full story

What Expenses Qualify for R&D Tax Credits?

The R&D tax credit is one of the most overlooked opportunities to boost your bottom line. Many business owners fail to claim it under the mistaken belief that they’re not…

Read full story

Clayton & McKervey Launches The Sound of Automation Podcast

Media Contact: Denise Asker, dasker@claytonmckervey.com; 248.936.9488 Southfield, Mich.—February 17, 2021—Clayton & McKervey, a certified public accounting and business advisory firm helping growth-driven companies compete in the global marketplace, is excited…

Read full story

Misconceptions About the Research & Experimentation Tax Credit

As companies put more emphasis on Industry 4.0 and business processes become more automated and accessible, the opportunities for Research & Experimentation tax credits increase. The Research and Experimentation (R&E)…

Read full story

Categories

Jump directly to the topics that matter to you most.

  • A&E Professional Services
  • About Us
  • Advisory & Assurance
  • Business Owners
  • C&M Press Releases
  • Careers
  • China Consulting
  • Clayton & McKervey
  • Client Accounting Services
  • Consulting
  • COVID-19
  • Data Analytics
  • Estate Planning
  • Expanding Outside the U.S.
  • Expanding to the U.S.
  • From the President
  • Industrial Automation
  • International
  • Manufacturing & Distribution
  • Mexico Consulting
  • Podcasts
  • Private Client Services
  • Tax & Tax Credits
  • Transaction Services
  • Videos

Authors

Read news direct from our managers and stakeholders.

  • Ben Smith
  • Beth Butchart
  • Bryan Powrozek
  • Carlos Calderon
  • Casey Haggerty
  • Clayton & McKervey
  • Dave Van Damme
  • Denise Asker
  • Eric Lin
  • Jim Biehl
  • Julie Killian
  • Kevin Johns
  • Margaret Amsden
  • Miroslav Georgiev
  • Nina Wang
  • Rob Dutkiewicz
  • Ruben Ramirez
  • Sarah Russell
  • Sue Tuson
  • Tarah Ablett
  • Teresa Gordon
  • Tim Finerty
  • Tim Hilligoss
  • Wendy Reedy

Additional Resources

Additional news from Clayton & McKervey can be found below.

  • Subscribe to our email newsletter
  • View upcoming events
  • Contact us to let us know how we can help you

Website

  • COVID-19
  • Insights
  • Who We Help
  • Services
  • Events
  • Careers
  • About Us
  • Contact Us
  • Subscribe

Location

+1 248.208.8860
2000 Town Center
Suite 1800
Southfield, MI
48075 | USA

Connect

  • Events
  • Newsletter
  • Client Login

Social

  • LinkedIn
  • Facebook
  • Twitter
  • Glassdoor
  • YouTube
  • Instagram

Awards

DFP Top Work Places Best & Brightest
Prime Global

Tax | Accounting | Assurance | Consulting | Highly technical and accessible team of CPAs helping growth driven, closely held, middle market companies compete in the global marketplace. Michigan-based accountants and advisors focused on helping business owners in the United States and throughout Europe and China.

Privacy Policy Disclaimer

© 2021 Clayton & McKervey