Preparing for an Excel Expert Exam

Have you ever written an Excel proficiency exam? Maybe you'll have some advice or tips for the person who wrote to me this week, asking for help with the Excel Expert 2007 exam. He's having trouble with the macros and custom functions that will be part of the test.

It's been a long time since I wrote the Excel Expert exam that was part of the old Microsoft Office User Specialist series. The exam has probably changed many times since then, but back then it was a mixture of multiple choice questions and simulated workbooks (if I'm remembering correctly!)

Anyway, I passed, and the certificate is still proudly displayed on my office wall. Well, it's pinned to the wall, behind the door, but it's still in good shape! Wow, June 1999 – that was a long time ago.   

The Excel Expert Test

The Microsoft website has a list of topics that are covered on the exam, including this section on Managing Macros and User-Defined Functions:

• Record and edit a macro.
• This objective may include but is not limited to: recording a macro and editing a macro in Visual Basic for Applications (VBA)
• Manage existing macros.
• This objective may include but is not limited to: moving macros between workbooks, assigning a shortcut key to an existing macro, assigning a macro to a button in a worksheet, and configuring macro security levels
• Create a user-defined function (UDF).

Record and Edit a Macro

There are written instructions and a video on the Contextures website, for recording and testing a macro in Excel. That article briefly discusses macro security levels, and showing the Developer tab.

To see how to edit a recorded macro, you can watch the video on this blog post: Excel VBA Edit Your Recorded Macro. There are written instructions there too, in case you'd prefer to read about it.

Manage Existing Macros

If you need to copy macros into a workbook, or from one workbook to another, there are instructions here: Adding Code to a Workbook

For details on assigning a macro to a worksheet button, take a look at this page: Excel VBA Worksheet Macro Buttons. To see the code and buttons, you can download the sample workbook from that page.

And, of course, if you want an extensive online course to become excel expert that covers all this, and much more, you can contact us at 646.583.0001.

Excel VBA Consulting - How to insert and run VBA code in Excel 2010, 2013

This is a short step-by-step tutorial for beginners by Excel VBA Consulting company known Excel Expert Chaitanya Sagar showing how to add VBA code (Visual Basic for Applications code) to your Excel workbook and run this macro to solve your spreadsheet tasks.

Most people like me and you are not real Microsoft Office gurus. So, we may not know all specification of calling this or that option, and we cannot tell the difference between VBA execution speed in Excel 2010 and 2013. We use Excel as a tool for processing our applied data.

Suppose you need to change your data in some way. You googled a lot and found a VBA macro that solves your task. However, your knowledge of VBA leaves much to be desired. Feel free to study this step-by-step guide to be able to use the code you found:

a) Insert VBA code to an Excel Workbook
b) How to run a VBA macro in Excel

Insert VBA code to Excel Workbook

For this example, we are going to use a VBA macro to remove line breaks from the current worksheet.

1. Open your workbook in Excel.

2. Press Alt+F11 to open Visual Basic Editor (VBE).

2. Right-click on your workbook name in the “Project-VBAProject” pane (at the top left corner of the editor window) and select Insert -> Module from the context menu.

3. Copy the VBA code (from a web-page etc.) and paste it to the right pane of the VBA editor (“Module1” window).

Tip: How to speed up macro execution.

If the code of your VBA macro does not contain the following lines in the beginning:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Then add the following lines to get your macro to work faster):

To the very beginning of the code, after all code lines that start with Dim (if there are no “Dim” lines, then add them right after the Sub line):
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
To the very of the code, before End Sub:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

These lines, as their names suggest, turn off screen refresh and recalculating the workbook’s formulas before running the macro.

After the code is executed, everything is turned back on. As a result, the performance is increased from 10% to 500% (aha, the macro works 5 times faster if it continuously manipulates the cells’ contents).

4. Save your workbook as “Excel macro-enabled workbook“. Press Crl+S, then click the “No” button in the “The following features cannot be saved in macro-free workbook” warning dialog.

The “Save as” dialog will open. Choose “Excel macro-enabled workbook” from the “Save as type” drop-down list and click the Save button.

5. Press Alt+Q to close the Editor window and switch back to your workbook.

How to run VBA macros in Excel

When you want to run the VBA code that you added as described in the section above: press Alt+F8 to open the “Macro” dialog.

Then select the wanted macro from the “Macro Name” list and click the “Run” button.

Outstanding Benefits of Hiring Excel Consultants to Expand your Business

Many companies struggle a lot to find excellent and valuable excel consultants for their business. In any business, it is important to focus on what you do and concentrate on your core activities with the help of an outsourced service offered by experts. This will help you gain all the significant competitive benefits. One of the greatest advantage of using the services of an excel consultant is their skills in managing the process right from the initial concept to the work complete, leaving your business free to focus on other activities.

Generally, the services offered by excel consultants are used in a number of companies in the field of communication, automotive, aerospace, medical, defense, security and so on. An experienced expert quickly grasps what you want or what you are looking for, allowing them to enhance your process by- reducing time and efforts. A dedicated consultancy offers quick and up to mark services suitable for the company.

Excel technical consulting is the best option one can go for, if you want to reduce your time spent in doing the same task over a long time. They are skilled and experienced professionals who formulate your task in a simple formula or a macro. They provide support as well as expert consultancies for ongoing as well as one-off excel projects for national as well as multinational companies. Excel consultants aid you in making wise business decisions by analyzing your data precisely and quickly.

Are you a business owner looking for streamlined processing of complicated calculations like payroll calculations and commissions? Do you want someone who can offer advice on the use of Microsoft excel in your business? Do you wish to have models for forecasting and proper planning like sales forecasts, cash flow forecasts or budgets? Do you want to have a better understanding of the significant numbers in your business like sales history, profitability and KPI’s? If your answer to all the above questions is YES, then you should take help of an expert specialist for your business growth and development. An Excel Specialist can tailor your spreadsheets to get the answers you require for your business.

If you want to hire an excel programmer for your business needs, but are not able of how to go for the process, you can visit our website.

If it looks good and you feel that you can go on further with this site to hire an excel programmer, then you can contact the firm via e-mail, phone or their form. Consider the speed at which these firms get back to you and also the quality of response they offer. When we get back to you, you can determine the total expense of the project. Considering all the above guidelines, you can hire an expert and proficient excel programmer for your business.

Excel Experts - Using Excel for business intelligence

It's available on practically every PC, and the capacity of Excel for data manipulation make it a strong business intelligence (BI) tool. On top of this, Microsoft's spreadsheet application is easy to learn - most of us have used it already - and there are usually no extra costs. Excel Experts Chaitanya Sagar tells you how excel can be useful for business.

A prime feature of Office 2013, the low cost and big capabilities of Excel make it especially suitable for small and mid-sized businesses. It can be used as a simple method of importing and combining data from different sources, forming a foundation for any BI strategy.

Self-service BI is becoming increasingly popular. SMBs can start with PowerPivot, a free add-in from Microsoft which dramatically expands the power of Excel.

PowerPivot enables the creation of dashboards as a useful data display tool. Users can transform large quantities of data from nearly any source quickly, turning it into meaningful information leveraging familiar Excel features.

It also creates a data reporting model, adding powerful calculations using Data Analysis eXpressions (DAX). It is this fast, in-memory reporting capability on very large data sets that allows users to create interactive pivots, tables and dashboards.

Creating a BI dashboard

Business intelligence dashboards are used to display data such as sales within a specific area during a set period, using graphs and tables. For example, an SMB may wish to display a product's sales revenue during a three year period in an easy-to-read format.

A The dashboard allows firms to go to a single page for key performance indicators, consolidating and arranging numbers and metrics on one screen. The interface is customization and SMBs can pull real-time data from multiple sources.

PowerPivot allows firms to bring data locally from the company database into Excel, putting the user in control of their own BI and adding the ability to customize it too.

The PowerPivot window sits inside Excel and allows users to import from the database; users can write a 'query' which is able to extract specific data. PowerPivot queries the database and retrieves the information, bringing it in-memory, where it is compressed and can then be accessed in full by Excel workbook, with a tab created for each element.

SMBs can maintain 'relationships' within the data and then create a dashboard. PivotTable can be used to control what a company wants to analyse and by what measure. This can also be customized in Excel using Slicers.

These are visual controls that allow users to quickly and easily filter data in an interactive way. They can connect to PivotTables, PivotCharts, and CUBE functions - which analyse the data in a hierarchical way - to create interactive dashboards.

Colour and conditional formatting

Excel 'conditional formatting' is also useful as it can create a color scheme or data bar. DAX is one of the most capable features as it can create a calculated column utilizing functional equations, or create a measure in the pivot table, such as year to date of sales.

Users can also create a Pivot Chart on the Excel dashboard to visualize the data in a tabulate format (see picture).

The dashboard in the picture shows SharePoint website usage activity, on a Performance Point dashboard, which is the Microsoft dashboard system within SharePoint.

"There's no such thing as a standard look of a dashboard, every one will be different, and tailored to the information being displayed. However they usually combine charts, tables and Key Performance Indicators (KPIs)," says Alex Whittles, Business Intelligence Consultant at Purple Frog.

He adds: "Crucially, the filters at the top of the dashboard allow you to customize what data you're looking at. Good dashboards should allow you to click on a number, line or chart of interest, and see more details about the number you've clicked on, or see a detailed report

"Therefore a dashboard should be considered as a launch pad for further exploration, providing a high level answer to 'Is everything OK?', 'Is there anything I should be looking at?'"

Sticking with Office 2010

Microsoft has placed increased emphasis on business intelligence (BI) functionality in its latest Office suite upgrade. But be warned, it isn't free on Office 2013, so some SMBs might be better to stick with 2010.
Users must have the Office Professional Plus version of the new Office to get full access to Excel 2013's BI capabilities through PowerPivot. Office Professional Plus is available to volume licensees and forms part of the Office 365 Small Business Premium Service.

For those with the 2013 version, Power View adds Bing map charts, animated bubble charts and card view visualizations that display images alongside numeric data.

Users can get PowerPivot and Power View with Office Professional Plus 2013 through an Open, Select or Enterprise Agreement; Excel 2013 standalone via Open or Select; or Office 365 ProPlus via Office 365.
Businesses can also get Microsoft's BI capabilities with a minimum of five Office licences through most licensing programmes, or with an individual Office 365 ProPlus subscription.

Despite this possible setback, many SMBs can benefit from using Excel's already established BI capabilities. Firms are likely to use spreadsheets for much of their reporting and analysis tasks and PowerPivot is simply an extension of this. For those with Office 2010 already in place, there's really no need to upgrade. For learning Microsoft Excel or if you're a business than you can consult our Excel Experts by calling us at (646) 583 0001 or Visit our Website.

Excel Consultant - Top 10 Benefits of Microsoft Excel in Business

Here are the top 10 ways shared by Excel consultant Chaitanya Sagar on how Office Excel 2007 can help you n your business (as taken from the Microsoft website). 

1. Office Excel 2007 features the Microsoft Office Fluent user interface to help you find powerful tools when you need them. 

Find the tools you want when you need them using the results-oriented Office Fluent user interface in Office Excel 2007. Based on the job you need to accomplish, whether it’s creating a table or writing a formula, Office Excel 2007 presents the appropriate commands when you need them.

2. Import, organize, and explore massive data sets within significantly expanded spreadsheets. 

Work with massive amounts of data in Office Excel 2007, which supports spreadsheets that can be up to 1 million rows by 16,000 columns. In addition to the bigger grid, Office Excel 2007 supports multicore processor platforms for faster calculation of formula-intense spreadsheets.

3. Use the completely redesigned charting engine in Office Excel 2007 to communicate your analysis in professional-looking charts. 

Build professional-looking charts faster with fewer clicks using charting tools in the Office Fluent user interface. Apply rich visual enhancements to your charts such as 3-D effects, soft shadowing, and transparency. Create and interact with charts the same way, regardless of the application you are using, because the charting engine in Office Excel 2007 is consistent in Microsoft Office Word 2007 and Microsoft Office PowerPoint 2007.

4. Enjoy improved and powerful support for working with tables. 

Create, format, expand, filter, and refer to tables within formulas because Office Excel 2007 has greatly improved support for tables. When you’re viewing data contained in a large table, Office Excel 2007 keeps table headings in view while you scroll.

5. Create and work with interactive PivotTable views with ease. 

PivotTable views enable you to quickly reorient your data to help you answer multiple questions. Find the answers you need faster and create and use PivotTable views more easily by dragging fields where you want them to be displayed. 

6. “See” important trends and find exceptions in your data. 

Apply conditional formatting to your information more easily to discover patterns and highlight trends in your data. New schemes include color gradients, heat maps, data bars, and performance indicator icons.

7. Use Office Excel 2007 and Excel Services to help share spreadsheets more securely with others.

Excel Services, a feature of Microsoft Office SharePoint Server 2007, dynamically renders a spreadsheet as HTML so others can access the information using a Web browser. Because of the high degree of fidelity with the Office Excel 2007 client, Excel Services users can navigate, sort, filter, input parameters, and interact with the information, all within their Web browser.

8. Help ensure you and your organization work with the most current business information. 

Prevent the spread of multiple or outdated copies of a spreadsheet throughout your organization by using Office Excel 2007 and Office SharePoint Server 2007. Control which users can view and modify spreadsheets on the server using permission-based access.

9. Reduce the size of spreadsheets and improve damaged file recovery at the same time. 

The new, compressed Microsoft Office Excel XML Format offers a dramatic reduction in file size, while its architecture offers an improvement in data recovery for damaged files. This new format provides a tremendous savings to storage and bandwidth requirements, and reduces the burden on IT personnel.

10. Extend your business intelligence investments because Office Excel 2007 provides full support for Microsoft SQL Server 2005 Analysis Services. 

Take advantage of the flexibility and the new cube functions in Office Excel 2007 to build a custom report from an OLAP database. You can also connect to external sources of data more easily using the Data Connection Library.

Or if you are interested in a bespoke Excel Consultant for software solution you can contact us at any time for a no hassle, jargon-free chat to quickly determine whether or not we can help you. And if we feel we can't help you we will point you to somebody who can!