Featured Posts

<< >>

The Art of Automating Reports in Excel (Part-2)

Assign code for individual calculation field. You will once more employ the similar 2 lines as explained above, but in this situation your ActiveCell. Value represents numeric function, like, SUM,

The Art of Automating Reports in Excel (Part-1)

One of the numerous attributes of MS Excel is its capability for automating reports. Users can easily generate high quality spreadsheets for simplifying others’ capability to feed information into its

Refine your Reporting standards With Excel 2013

If reporting is an inevitable task in your organization, it could never be more rapid, refined and remunerative than with Excel 2013. Excel provides an easy yet ultra smart interface

The Art of Automating Reports in Excel (Part-2)

  1. Assign code for individual calculation field. You will once more employ the similar 2 lines as explained above, but in this situation your ActiveCell. Value represents numeric function, like, SUM, instead of the InputBox function is employed to show an input function.

  2. Increase a line of code for saving your shared worksheet. The format will be like, “ActiveWorkbook.SaveAs Filename:=”Filename.xls”,” here “Filename”showcase the name of your worksheet. Take account of the quotes all over the place like “Filename.xls,” but not across the entire entry.

Automating Reports in Excel: In case If you are using Excel 2007 or later, you can use it by adding additional alphabet in the end of the word like, “.xlsx” for “.xls,” but in case if other users who will be using spreadsheet have Excel 2003 or former, they would not  be able to employ the worksheet in the absence of plug-in reader suite.

  1. Hit the Alt and Q keys at the same time. This will result in closing of the VS editor.

  2. Hit the Alt and F8 keys at the same time. This will help in opening the Macro dialog box.

  3. Hit it off the name of your process in the Macro list. If yours is the single process from the list, it will be designated in an automatic way.

  4. Hit the Alternatives tab. You will be encouraged to feed a keyboard based character for using it as a keyboard shortcut in addition to the Ctrl key. Select a significant letter which is not till now employed as a shortcut character, for example “e” for “entry.”

  5. Hit “OK” for closing the Macro alternative dialog box. You can now hand out your interactive worksheet to those who will make use out of it. After opening it, they can employ the shortcut key for enabling entry and following the stimulators you generated for filling in their information.

Method 2: Automating Reports in Excel

  1. Build your report into a PivotTable. PivotTables are constructed for summarizing information for helping you in comparing numbers and identifying rends. Your PivotTable should be associated to information somewhere else in your worksheet or imported from a file.

  2. Compose a VB script for opening and closing the report. Your characters must accomplish the tasks enumerated below. Each task will be defined properly monitored by the code specified in brackets for implementing it. When you compose the definite code, write it in a single block, substituting it by your own names for the sample names, and do not take account of the brackets that surround the complete illustration.

The Art of Automating Reports in Excel (Part-1)

From Microsoft Office resources

From Microsoft Office resources

One of the numerous attributes of MS Excel is its capability for automating reports. Users can easily generate high quality spreadsheets for simplifying others’ capability to feed information into its books, and users can also systematize the creation of its reports. Equally these characteristics need some familiarity and understanding of Visual Basics. The steps to accomplish both jobs are defined below for automating reports in Excel.

Method 1:  Generating an Interactive Spreadsheet – Automating Reports in Excel

  1. Select the layout of worksheet. Your worksheet should be arranged properly so that others employing it can find the areas they require swiftly for entering data. You can spread your worksheet in a horizontal or vertical way. Most of the users will discover a vertically laid layout stress-free to use, mainly if you are planning to print the worksheet.

  2. Generate typescript labels for the worksheet. You will need a label at the top most part of each column, in addition to a label in the cell to the left side of every cell in the column where you are planning to set the item fields.

  3. Now Press both the Alt and F11 keys at the same time. This opens the MS VS editor.

  4. By Double-clicking “Workbook name” in the “Project-VBA Project” pane from the upper left side. This will help in opening a code generating window in the key area of the editor.

  5. Choose “Method” from the Insert menu. This shows the Add Method dialog box.

  6. Fill a name for the process in the Name field. Offer the process a significant name, such as “SumExpenses” if your intuitive spreadsheet will be employed for generating report for some sort of expenses. After this click OK for closing the dialog box. Your method name cannot contain spaces, but you can employ an underscore ( _ ) instead of  a space. When the Add Process dialog box ends, you will get a line labeled “Public Sub” trailed by the name of your process. Underneath that line, you will find a space and the words “End Sub.”

  7. Give code for each input field in the worksheet. You will give 2 lines of code for every entry. The initial line of code get the form “Range(“cellname”).Select”, at this place “cellname” signify the cell in which the response field will exist.

This should be the cell directly to the right of a script label; if you have a script label in cell A2, you would put an input field in cell B2 (Range(“B2″).Select). Take account of the quotes all over the place of the cell name, but not for those which are around the whole code account.

The 2nd line of code get the form “ActiveCell.Value = InputBox(“InputPrompt”)”, where “InputPrompt” showcase script that will seem like they need  to inform the user about the type of information to enter in the input cell.

Refine your Reporting standards With Excel 2013

Excel 2013


Find out how Excel’s new features push your business performance to the next level
Image Courtesy of Gualberto107/freedigitalphotos.net

If reporting is an inevitable task in your organization, it could never be more rapid, refined and remunerative than with Excel 2013. Excel provides an easy yet ultra smart interface to generate resolute reports that can be used to make business operations more dynamic and proactive. There are scores of features in Excel that makes it a preferred choice of many organizations; here are the top most imperative ones that are now available with Excel 2013 to empower your business operations with enhanced efficacy:

Swift Data Analysis

Earlier, users needed some time to customize their reports with different colors, formats and alignments. The swift data analysis feature now makes it super fast. Various formatting and color settings options are available to you as soon as you select a range of data in a sheet. Quick preview options are also available to help you select and apply the best formatting that perfectly portrays your data.

Excel Survey Options

This is an enormously powered tool that is available with SkyDrive. For the parts of your Excel report where you may need to invite suggestions or input from the end users, Excel 2013 provides an easy way. Earlier, such tasks involved third party tools which were not very safe as it could lead to undesired data leakage. In Excel 2013, users can easily select the Survey options from the SkyDrive menu.

Instant Chart Recommendations

This feature saves a lot of time and effort that was previously needed to select and use the desired chart form. The chart recommendation option instantly reads the form of selected data and recommends the most suitable form of chart to be applied for best illustration of the data. Quick buttons are also readily available to efficiently customize the chart with different color, caption and label options.

The Power View

It helps to generate interactive Excel reports. You can amass multiple data sets and draw coherent relations between data. Power View can deploy online technologies like SilverLight and Bing mapping for professional reports and dashboards.

Enterprise Solutions

Excel Enterprise solution caters to big organizations that need to handle big data with more astuteness. It provides utmost control over the data along with easy storing and tracking features without any third party support.