Home » Blog » Sage Intelligence Tips and Tricks

How to insert icons into a spreadsheet July 5, 2018

This tip may seem basic, but with Microsoft continually adding value to its products—we just had to mention it. It’s lead by a repository of every icon imaginable (and maybe even a few that are unimaginable) that you may find useful, especially if you’re someone who likes your Excel workbooks… Read more

Learn how to use Title Variables in your Sage Intelligence reports June 26, 2018

Having a meaningful title for a report can help make its intention clear.  For example, being able to add a date to a report name allows you to tell at a glance what information the report is referring to, and having this work dynamically is an obvious convenience.  Sage Intelligence… Read more

Save time by using shortcut keys in Sage Intelligence June 26, 2018

Shortcut keys are common in many software applications and can save you time by making common functions easy to perform.  Sage Intelligence is no different, and several shortcuts are provided in both the Report Manager and Connector.  They are as follows: CTRL + R In the Report Manager, selecting a… Read more

Discover a more detailed analysis of dates using timelines in Excel June 20, 2018

Have you ever felt the need to effortlessly switch the date ranges within your Pivot Table in Excel? Have you ever wanted to view the data for just one specific time-frame without having to apply a logical filter all the time? When is the last time you were able to… Read more

Learn how to select your data conveniently using keyboard shortcuts June 13, 2018

In a previous tip, we showed you how you can navigate around a data list quickly using keyboard shortcuts.  To view the tip, take a look here.  By combining the same shortcuts with the Shift key, you can easily make selections in your data for editing purposes. Assume that you… Read more

How to keep your Report Manager tidy by hiding reports you don’t use June 12, 2018

You may have reports that you don’t run out often, if at all.  A common example is the sub-reports of a union report, and you only ever run out the union report.  Having these additional reports in your Report Manager may be necessary but can clutter up your folders.  Fortunately,… Read more

How to calculate the average of the top 5 values June 5, 2018

Let’s say you have a workbook containing sales over a two year period. You would now like to know what the average is for your top 5 sales values, as well as the value for your largest sale. In our example, which you are welcome to download here, we are… Read more

How to reduce the size of a workbook using a single formula May 30, 2018

Sometimes, large Microsoft Excel workbooks can be slow and inefficient—which could cause frustration.  To reduce the size of a workbook, consider replacing several formulas with a single array formula. Array formulas can perform multiple calculations and they also ensure consistency, which can lead to greater accuracy.  Furthermore, array formulas provide… Read more

How to create a Hierarchy SmartArt Graphic in Excel May 23, 2018

Microsoft Excel offers you the ability to create and design a Hierarchy SmartArt Graphic.  This is useful in showing a graphical representation of an organogram, organisation chart or any hierarchy structure. This example, explained below, will help you create your own hierarchy. Our example scenario is of a Report Store… Read more

Learn how to edit standard reports in Sage Intelligence for your Sage Business Cloud solution May 22, 2018

Sage Intelligence provides you with several standard reports that are displayed under the Standard Reports page as soon as you open the application.  They provide you with necessary, ready-to-use value right off the bat. To ensure that you always have an original instance of these reports, they cannot be edited… Read more

Save your entire report to PDF using macros and Sage Intelligence May 22, 2018

Being able to save your reports to PDF provides a convenient way to view and share them. Sage Intelligence provides two common ways that you can do this. These include; distributing a report as a PDF using the Distribution Options in Excel once a report has run out, and using… Read more

Using the VLOOKUP formula with the approximate match in Excel May 10, 2018

Have you ever used the VLOOKUP formula with the approximate match in Excel? By using this formula, you’re able to extract the correct trade discount percentage based on the quantity of a specific item purchased. In this tip, we demonstrate how to set this up. If TRUE or 1 is specified… Read more

How to create more accurate forecasts with Excel May 9, 2018

Do you need to future-proof your business? The Excel Forecast sheet may be the right tool for you! The Forecast sheet allows you to take your historical business data (sales, cash flow, etc.) and create more accurate forecasts for the future. You are welcome to download the workbook to practice!… Read more

Inserting 3D models into an Excel spreadsheet May 2, 2018

This is a tip that you’re going to really enjoy if you love imagery and visualisations in your spreadsheet! It’s not the most useful tip in the world, but for most people, especially the creative types—it will put a smile on your face because it’s so much fun to play… Read more

Learn how to navigate through your data quickly with keyboard shortcuts in Excel April 25, 2018

When working with large data lists in Excel, you may need to know the extent of the data, i.e. the number of rows and columns.  If consistency of the data is important too, then you may need to identify whether there’s any empty rows, columns or cells. An obvious way… Read more

Learn how to quickly save your Excel management pack as a PDF April 24, 2018

Do you have an Excel management pack with multiple worksheets and would like to save it as a PDF for convenient viewing and sharing? In this tip, I’m going to show you how easy this is to do. You have three options for this, but before continuing, there are a… Read more

Learn how to flag missing accounts in your Sage Intelligence Financial Report Designer report layouts April 24, 2018

Depending on your Sage business solution, you may or may not have the Missing Accounts feature in your Financial Report Designer report.  Even if you have this functionality, you are still not automatically alerted that account level detail may be missing from your layout.  In this tip, I’m going to… Read more

Learn how to extract more than one column of data with a single VLOOKUP formula in Excel April 18, 2018

It’s been well over 10 years since I started training Microsoft Excel to corporate clients. During training sessions, delegates would usually ask questions on how they can use Excel to help them become more effective in their work. Last week, a financial manager asked me how she could easily look up more… Read more

How to use a slicer instead of a drop-down list in Excel April 11, 2018

Selecting data from a drop-down list is a convenient and accurate way of entering data into a range. It helps in ensuring that the correct data is entered quickly in Microsoft Excel. However, a slicer provides a quicker, better and easier way of selecting data from a list. In this tip,… Read more

Learn how to hide rows and columns using Named Ranges April 10, 2018

Do you have a workbook that could do with some neatening up?  Being able to hide rows and columns in a report that contains data you don’t need to view is a great way to do this.  An example of a report like this is a financial statement, which has… Read more

How to calculate the average useful life of assets April 4, 2018

To complete an analytical review of the depreciation of your company’s assets, you can calculate the average useful life of these assets. The average useful life, which is a measure of conditional central tendency, can be calculated using the AVERAGEIF function. Central tendency is the middle value or a typical… Read more

How to effectively use custom number formatting to display your + or – values March 29, 2018

In order to effectively analyze data, custom number formatting can be applied to your Microsoft Excel spreadsheet. Custom number formatting is used to easily identify values based on a set criteria. In a large Excel spreadsheet, you can easily highlight all negative or positive values by using custom number formatting. In this… Read more

Using the Balance Type functionality in Sage Intelligence for Accounting March 28, 2018

Would you like to separate your debit and credit balances while using the Excel Design Pane?  If so, then the Balance Type function is for you.  An example is if you want to create a Trial Balance with debit balances reflected in a debit column and credit balances reflected in… Read more

Creating an organized folder structure in Sage Intelligence March 27, 2018

With a disorganized reporting structure, finding anything specific can be like finding a needle in a haystack.  Stop wasting time searching for reports by creating an organized folder structure with Sage Intelligence.  The most effective folders structures are easy to use, so everyone can intuitively find the reports they’re looking… Read more

Additional Sage Intelligence reports to boost your business March 27, 2018

How would you like constant access to a library of additional reports?  The Sage Intelligence Report Utility allows you to select the reports you like and automatically imports them into your Report Manager.  All you need to do is download and install the Report Utility, select the reports you want,… Read more

3 Easy steps to hide zero values in Excel March 22, 2018

To have neat looking reports in Microsoft Excel, you may decide that you want to hide zero values. In this tip, we explain how to hide zero values for selected cells by using the custom number format. The hidden zero values appear in the formula bar when a cell is… Read more

How to compare and merge a shared workbook in Excel March 14, 2018

Sometimes when working in Excel and collaborating with your colleagues on a single document, you may wonder to yourself; how can I compare and merge a shared workbook? The answer is simple! You can do this by using the Compare and Merge Workbooks command (this command will have to be… Read more

Understanding date formats in Sage Intelligence March 12, 2018

If you’re a user of the Sage Intelligence Connector Module, you’ll know that the Connector maintains the links between your Sage Intelligence reports and your Sage Business Management Solution data sources.  Different database systems store and recognise dates in different formats, and Sage Intelligence attempts to hide this detail from… Read more

3 Easy steps to help you complete a cash flow template March 8, 2018

In order to correctly show the movement and management of cash within a business, accountants use a cash flow statement. This statement will display a business’s money coming in from ‘cash receipts’ and where it has paid money out, ‘cash paid’. The cash flow statement is a standard financial statement… Read more

Learn how to forecast with scenarios using what-if analysis tools in Excel February 28, 2018

The Scenario Manager is a feature in what-if analysis and you can use this to enhance your forecasting in Microsoft® Excel®. A scenario is a set of values that Excel saves and can automatically substitute in specific cells on a worksheet. You can create and save different groups of values… Read more

Learn how to use the Quick Create Report feature in Sage Intelligence February 27, 2018

If you want to create a report quickly or you’re not sure how to design one from scratch, the Quick Create Report feature in Sage Intelligence is very easy to use.  By simply selecting a set of pre-defined columns and rows, you’re able to set up a financial report in… Read more

Learn how to add additional fields to a report February 27, 2018

Did you know that whether you are using a standard or a customised Sage Intelligence report, you’re free to add additional fields to it in the Sage Intelligence Report Manager?  There are a couple of reasons why you might want to do this.  For example, perhaps the report doesn’t have… Read more

Quickly split data into two or more columns in Excel February 21, 2018

If you ever need to split data from one column in your Microsoft Excel worksheet into two or more columns, you can use the LEFT, MID and RIGHT Text functions. The LEFT function returns the first character or characters in a text string, based on the number of characters you specify. The… Read more

Using conditional formatting with custom Excel formulas February 14, 2018

Conditional formatting is an effective Microsoft Excel feature that allows you to highlight important information, for example; the ability to find duplicate values within your spreadsheet. You can create your own rule by applying conditional formatting to individual cells or a range of cells. When you have selected the data… Read more

Troubleshooting your Sage Intelligence errors made easy February 13, 2018

No matter what time of day it is, it’s never a good time for Sage Intelligence errors.  What can you do to resolve these errors on your own?  And, what can you do next?  Sage Intelligence has made it possible to triage these issues, and if you can’t figure it… Read more

How to calculate Sales Tax/ VAT using PowerPivot Measures in Excel February 7, 2018

In a previous tip, we showed you how to set up a PivotTable using PowerPivot. You’re now going to learn how you can easily calculate Sales Tax/ VAT using the Measures feature in the PowerPivot tab. If you’re the business’s accountant, you may want to analyze the Sales Tax/ VAT that will… Read more

How to determine whether you are over or under budget using custom formats January 25, 2018

Monitoring budgets can be quite stressful and may often involve the use of formulas. Instead of complicated formulas, you can use custom formats within Microsoft Excel to easily determine if you are over or under budget. All it takes are a few steps, which we explain below, and you can… Read more

Learn how to share your report between different users and companies January 23, 2018

Did you know that in Sage Intelligence you can design a report in one company and view that same report in a different company?  That’s right, just as long as those companies carry the same account structure, you can share your report. This report will go through the following process:… Read more

What’s the deal with Sage Intelligence and data sorting? January 23, 2018

Have you ever run out a report only to find the need to change the sorting of a specific field once the output is in Excel?  This can be quite taxing, especially if you run multiple reports with a lot of data on a regular basis, and if you need… Read more

Here’s how to run a report out to PDF January 9, 2018

In our last tip, we showed you how to save a copy of your Excel workbook to file when running a report.  If you’d like to catch up on it, then take a look here. Did you know that in addition to saving your reports in an Excel format, you… Read more

Learn how to save a report to file using Sage Intelligence December 19, 2017

Apart from just running out a report, Sage Intelligence allows you to automatically save the generated workbook to a file during run-time.  This is useful, for example, if you run a monthly sales report that needs to be saved to a central location for others to view.  In this tip,… Read more

How to create a hyperlink to a specific location in a workbook November 29, 2017

For quick access to related information in another file, workbook or on a web page, you can insert a hyperlink in a Microsoft® Excel® worksheet cell. You can also insert hyperlinks in specific chart elements or images. If you struggle to navigate to various worksheets in a large workbook, then… Read more

Two ways to create calculated columns and rows in your Sage Intelligence reports November 21, 2017

To create insightful and highly customizable reports, Sage Intelligence provides you with the ability to add calculated columns and rows to your reports.  These can be used to calculate, for example, variances, totals and other indicators at account and account category level, or based on your columns.  The below provides… Read more

Three ways to use a Consolidation Connection in Sage Intelligence November 21, 2017

In our last tip, I showed you how to create a Consolidation Connection in the Connector module, which lets you combine data from different companies into a single report to obtain a holistic view of your organization.  If you would like to review the tip, you can find it here.… Read more

How to view or compare two or more worksheets at the same time in Excel November 15, 2017

You can quickly view or compare two or more worksheets in the same workbook, or in different workbooks, by viewing them side by side. You can also arrange multiple worksheets to view them all at the same time. In this tip, I show you how. Applies To: Microsoft® Excel® for… Read more

Collaborating on a Shared Workbook November 8, 2017

Multiple users can effectively work on a spreadsheet by sharing it, and then collaborating on it. Edits can be made simultaneously by team members, thus having a continuously updated version of the spreadsheet available all the time. In this way, version control is enforced, and duplicate work is eliminated. Applies… Read more

Learn how to create a Consolidation Connection November 6, 2017

Sage Intelligence allows you to report on data from different companies in a single report to obtain a holistic view of your organization and allow you to carry out more detailed comparative analysis.  The primary way to do this is through a Consolidation Report that uses a Consolidation Connection.  Once… Read more

Discover how to compare two versions of a workbook in Excel October 25, 2017

Sometimes when working in Excel, you may want to compare two versions of a workbook to see what was changed. It could be an issue such as totals that are manually entered instead of formula calculated that you want to check. You can use Microsoft Spreadsheet Compare to run a… Read more

Learn how to switch output modes when running a report in Sage Intelligence October 24, 2017

Did you know that apart from being able to run reports out to Excel, Sage Intelligence allows you to switch output modes which can be useful when designing and debugging reports?  Two additional options are provided, output to Screen and output to SQL. Output to Screen does away with rendering… Read more

Using Microsoft Excel as a Data Source in Sage Intelligence October 11, 2017

You may be aware that Sage Intelligence allows you to connect to any ODBC compliance data source, meaning that you can access and report on information from just about anywhere in your organisation.  Included is the ability to use Microsoft Excel as a data source, which is useful in cases… Read more

How to work with numbers containing more than 15 digits in Excel September 29, 2017

Numbers containing more than 15 digits in Excel are not often used, however some users might use them when recording credit card numbers, account numbers, stock codes, etc. Applies To: Microsoft® Excel® for Windows 2013 and 2016. Excel can’t handle more than 15 digits per cell, and so when these… Read more

Making use of Wildcards in your Reporting Trees September 29, 2017

Reporting Trees is a feature of the Financial Report Designer, and allows you to model organisational hierarchies based on the structure of your business.  You can then filter your layouts by the different units in your trees to view your data in ways that make sense to you. Sage Intelligence… Read more

How to work with numbers containing more than 15 digits in Excel September 21, 2017

Numbers containing more than 15 digits in Excel are not often used, however some users might use them when recording credit card numbers, account numbers, stock codes, etc. Applies To: Microsoft® Excel® for Windows 2013 and 2016. Excel can’t handle more than 15 digits per cell, and so when these… Read more

How to fix wrapped data labels in a pie chart September 1, 2017

When creating a pie chart and adding data labels to the slices, the wording can sometimes split into two lines—giving you wrapped data labels that aren’t visually appealing. In the below example, Administration has been split into two words, each on a separate line. The labels are wrapped and the… Read more

How to view statistics on the status bar in Excel August 25, 2017

This tip helps you discover how to quickly display statistics on the status bar by simply highlighting data. You are welcome to download the workbook to practice. Applies To: Microsoft® Excel® for Windows 2010, 2013, and 2016. Excel allows for up to 6 statistic options to be viewed on the… Read more

Run multiple versions of the same report, with different parameters, all at once August 23, 2017

There may be times when you want to run out the same report but with different parameters.  For example, you may have a financial report that you want to run individually for three separate fiscal years.  Typically, you would need to run out the report three times, once for each… Read more

How to print and display formulas in an Excel workbook August 18, 2017

Instead of displaying values, you can actually display formulas as a way of reviewing the spreadsheet. In this tip, we explain how that can be done. You are welcome to download the workbook to practice. Applies To: Microsoft® Excel® for Windows 2010, 2013, and 2016. To show all formulas in a… Read more

How to generate a barcode in Excel August 11, 2017

This tip shows you how to generate a barcode in Excel that will enable you to track stock, sales, purchases or fixed assets. Applies To: Microsoft® Excel® for Windows 2010, 2013, and 2016. Excel has no built-in functionality to generate a barcode.  However, this is easily achievable by simply downloading and… Read more

Join Sally as she helps you forge ahead, making use of the Sage Intelligence Connector module August 9, 2017

Sage Intelligence gives you a lot of value out-the-box, through the ready-to-use reports provided in the Report Manager and the free reports that you can access via the Report Utility.  If these aren’t exactly what you’re looking for then you also have the ability to tweak them to suit your… Read more

How to Remove PivotTable Fields from Pivot Charts August 4, 2017

When creating Pivot Charts from PivotTables, the PivotTable fields will be displayed on the chart. In this tip, you’ll learn how to quickly hide them so that your report looks neater. Applies To: Microsoft® Excel® for Windows 2010, 2013, and 2016. You are welcome to download the workbook to practice this… Read more

How to remove spaces and characters in a cell July 28, 2017

If you have ever had problems sorting, filtering, or manipulating data in a Microsoft® Excel® workbook, and realized that it was caused by stubborn leading spaces in the cells, this tip will show you how to remove spaces quickly the next time. If you’re wondering what causes the spaces, there… Read more

8 tips to use when working with reports in the Excel Report Designer for Sage Intelligence July 26, 2017

Sage Intelligence Reporting Cloud provides convenience to you as it lets you work in whichever environment you’re most comfortable in. You can either choose to design reports in your browser through the Online Report Designer or in Excel—through the Excel Report Designer. When making use of the Excel Report Designer,… Read more

How to Subtotal Dates by Month and Year in Excel July 21, 2017

If your data has a column of dates, you may want to subtotal dates by month and year. However, when using the Subtotal command, the desired result is not being achieved as it creates a subtotal for each day. You are welcome to download the workbook to practice. Applies To:… Read more

Avoiding the Ref# error: How to convert formulas to values in Excel July 14, 2017

Sometimes when working with spreadsheets in Excel, you may want to distribute a workbook with static values. If your worksheet has formulas that are linked to other worksheets, you may end up with the dreaded Ref# error when distributing. That is why it is important to convert the formulas to… Read more

Learn how to share your Reporting Trees July 12, 2017

If you have used the Financial Report Designer to create reports, you may have taken advantage of Reporting Trees as a way of filtering them. If you then intend on sharing these reports with other counterparts and want them to be able to filter by the same tree structures, then,… Read more

How to restore missing labels in Excel July 7, 2017

Manipulating data with missing labels can pose a challenge. Copying and pasting data can be time consuming and tiresome. That is why, in this tip, we show you how to quickly restore data labels using a simple formula. If you find value in this tip, why not sign up for our… Read more

View specific aspects of several different worksheets on one worksheet June 30, 2017

One worksheet can be used to view specific aspects of several different worksheets. This can be done using the Linked Picture option. There are a few options when consolidating information from multiple worksheets, and in this tip, we’ll deal with the most popular. The option for this tip enables you… Read more

How to reverse the sign of an account in your Sage Intelligence Financial Report Designer June 28, 2017

In the Sage Intelligence Financial Report Designer, account balances are returned in their “natural format” from your Sage accounting or business management solution.  Therefore, credit accounts (with a credit balance) are shown as negative values and debit accounts (with a debit balance) are shown as positives values.  However, in financial… Read more

How to avoid the VALUE error when using the SUMIF function between workbooks June 23, 2017

When using the SUMIF function between workbooks, you may get a VALUE error if the source workbook is not open. This behavior occurs when the formula that contains the SUMIF, COUNTIF, or COUNTBLANK function refers to cells in a closed workbook. To work around this, use a combination of the… Read more

How to delete data but keep your formulas on a worksheet in Excel June 16, 2017

Have you ever found that at the beginning of the financial year you need to make a copy of the previous year’s workbook? If so, you’d need to delete all the old year’s data, but you would really like to keep your formulas in the worksheet. By using the Go… Read more

Learn different ways to add and edit Sage Intelligence formulas in the Financial Report Designer June 14, 2017

Once you know how to create your own customized reports, using the Sage Intelligence Report Designer and Microsoft® Excel®, you have the power to analyze any aspect of your financials!  At the core of this is knowing how to add and edit Sage Intelligence formulas in a report.  In this… Read more

How to quickly convert negative values to positive values June 9, 2017

Sometimes converting negative values to positive values can be time-consuming. This tip allows you to quickly convert a range of negative values into positive values, without having to edit each cell. Applies To: Microsoft® Excel® for Windows 2010,2013,2016. 1.In a blank cell, enter the value -1. 2. Select the cell… Read more

How to round a number off to the nearest multiple in Excel June 2, 2017

If you need to round a number off to a specified multiple, for example; rounding a selling price to the nearest 5cents or rounding a time to the nearest 15 minutes—then MROUND is the function to use. Below we explain how that can be done. Applies To: Microsoft® Excel® for Windows… Read more

Create your own Operational Report in Sage Intelligence Reporting Cloud May 24, 2017

Do you want to create your own operational report and analyze it according to your specific reporting needs from Sage One International? Sage Intelligence Reporting & Microsoft Excel make this easy and quick to do. Applies to: Sage One International, Microsoft Excel 2013 & 2016 How do I do this?… Read more

How to Print a List of Named Ranges in Excel May 19, 2017

It is very easy to create a large list of named ranges but to manage those ranges can be confusing, especially if you receive the workbook from somebody else. Having a list of the named ranges in your workbook could be helpful. To get a list, follow these steps: Applies… Read more

How to calculate Sales Tax/ Vat using PowerPivot Measures in Excel May 12, 2017

In a previous tip, we showed you how to set up a PivotTable using PowerPivot. This week, you’re going to learn how you can easily calculate Sales Tax/ Vat using the Measures feature in the PowerPivot tab. If you’re the business’s accountant, you may want to analyze the Sales Tax/ Vat that… Read more

Learn how to quickly and easily make changes to the design of your Sage Intelligence Reports May 10, 2017

Do you want to be able to conveniently update your Sage Intelligence reports? Some of your reports may appear outdated or may require a change in branding based on new company policies. You may also want to add new functionality based on later versions of Excel. This is quick and… Read more

How to use keyboard shortcuts in Microsoft Excel to increase productivity May 5, 2017

Using keyboard shortcuts in Microsoft Excel helps you increase your efficiency and productivity, and helps you to use Microsoft Excel like a pro. In this Tip, we take a look at how we can use Excel shortcuts to save us time and increase our business efficiency. We will learn how… Read more

How to use the IFS Function in Microsoft® Excel® April 28, 2017

The IFS function gives you an efficient alternative to the tedious task of creating a nested IF function. The IFS function checks conditions sequentially, and as soon as a condition is found to be true, the function stops and returns the specified value. You can take advantage of this logic… Read more

Creating a reference sheet in your Sage Intelligence reports April 26, 2017

Accountants, bookkeepers and financial analysts alike will tell you one of the most sought after capabilities of a well-tuned accounting package is time efficiency. No one wants to spend unnecessary time having to enter monotonous data. This is where a well-specified reference sheet in your Sage Intelligence reports can be… Read more

How to Create a 3D Map in Excel 2016 April 21, 2017

With a 3D Map, you can plot geographical data and view it on a 3D globe or custom map. If your data is time-stamped, you can even use a 3D Map to get a geographical tour of how your data changes over time. To create a 3D Map, your data… Read more

The Sage Intelligence Learning Portal is here! Here’s how to use it… April 12, 2017

At Sage Intelligence, we recently released our brand new Learning Portal.  It consolidates all of our learning resources into one, easy-to-use online library and is categorized to meet your level of knowledge and guide you to mastering your product.  In this tip, I’m going to show you how to access… Read more

3 PivotTable Improvements in Microsoft Excel 2016 April 7, 2017

The PivotTable is one of the easiest tools used to transform huge amounts of data into meaningful reports. Excel didn’t disappoint with the improvements that have been made to PivotTables in their 2016 version. These include the following; Field List search, Automatic Time Grouping, and Multi-select Slicers. In this tip,… Read more

Understanding your Cash Flow March 29, 2017

Understanding cash flow is a vital component of any business.  According to Eric T. Wagner, contributor for Forbes, “At surface level, the primary reason businesses fail is that they simply run out of cash.” Knowing how much money is coming into and going out of your business means you can… Read more

How to prevent objects from distorting in Excel March 17, 2017

When an object has been placed in the same row as your data and you delete some of your rows, you may find that your object will either be compressed or deleted completely.  This can easily be avoided by changing a setting in your object properties, thus preventing your object… Read more

Learn how to grant or restrict access to reports in the Sage Intelligence Report Viewer March 15, 2017

Did you know that you can grant or restrict access to reports in the Sage Intelligence Report Viewer? Sage Intelligence makes this easy to do.  Perhaps you’re busy developing a new report and you don’t want users to be able to run it from the Viewer until you’re happy with… Read more

How to get a distinct count in a Pivot Table March 10, 2017

Do you need to know how many customers you have invoiced this month?  When creating a Pivot Table and adding your customers to both the row labels and again in the value area, each transaction is totaled for each customer.  This does not give a true reflection of how many… Read more

3 Easy steps to create a forecast report in Microsoft® Excel® 2016 February 24, 2017

Have you ever needed to create a forecast report in Microsoft® Excel®? How did you calculate the projections? Whichever methods you used, Microsoft has made this much simpler with a new feature called Forecast Sheet. All you need to do is provide the input, and with a single click of the… Read more

Conveniently remove blank item rows in your PivotTable reports February 22, 2017

When creating reports, it’s usually preferred that rows with irrelevant data are excluded—as this results in a tidier, more readable report.  When it comes to PivotTable reports, this can include rows with blank items.  Below is an example of a report with them shown. To get rid of them, we… Read more

How to automatically open a frequently used workbook February 17, 2017

If you start each day needing to use a frequently used workbook, e.g. daily sales report, to-do list, calendar, etc, then this tip is for you.  You can automatically open these workbooks, by simply adding the workbooks or a shortcut in the XLStart folder.  This is a specific folder that Microsoft®… Read more

How to extract the formula text in Excel February 10, 2017

Auditing and troubleshooting formulas in Microsoft® Excel® can be time consuming and difficult. That is why the FORMULATEXT function is helpful because it simplifies formulae error checking and correction. The formula text can easily be extracted without having to copy and paste. The FORMULATEXT function syntax has one required input—a… Read more

Easily start up other apps when running Sage Intelligence reports February 8, 2017

When working with reports, you may make use of other tools to help analyze the data in them. For example; a calculator or notepad.  Did you know that Sage Intelligence allows you to automatically open other programs when a report is run?  Through the CmdExec add-in this is possible. In… Read more

How to hide zero values using Macros in Microsoft® Excel® February 3, 2017

Do your financial and operational reports include rows with zero values? Do you feel that your reports would be neater and easier to read if you could hide these rows?  In this tip, we will show you how to hide zero values using Macros. Applies To: Microsoft ® Excel® 2010,… Read more

How to print different headers and footers for odd and even pages January 27, 2017

If you’ve created a Microsoft® Excel® report that needs to be printed double-sided (e.g. booklet form), you may want to have your headers or footers printed on different sides of the paper—left hand side for odd pages, and right hand side for even pages. To achieve this effect, follow this… Read more

Learn how to report on more than just your financials in the Financial Report Designer January 25, 2017

The Sage Intelligence Financial Report Designer report provides a comprehensive tool for your financial reporting needs.  However, there may be times when you’d like to include other data in it to add to the information you can report on in a single report. Although the Report Designer has a defined… Read more

Beef up your Financial Health Check report with your own financial ratio January 11, 2017

In our last tip, you were introduced to the new financial ratio reports provided through the Report Utility, which you can use to perform a health check of your business.  If you didn’t catch it, or would like to brush up on it, you can find it here. Although the… Read more

3 Easy steps to hide zero values in Excel December 16, 2016

In order to have neat looking reports in Microsoft® Excel®, you may choose to hide zero values. In this tip, we explain how to hide zero values for selected cells by using the custom number format. The hidden zero values appear in the formula bar when a cell is selected.… Read more

How to enhance your chart data series using a pictograph December 9, 2016

Charts are used for displaying data graphically, and they can be enhanced by using a pictograph. Pictographs use any picture you choose to represent as your data series in the charts. Icons can also be used as pictograph to represent data values in order to decrease the size of the… Read more

Performing a health check of your business using Financial Ratio reports December 7, 2016

At Sage Intelligence, we believe in providing you with the right information to help you improve the running of your business, and give you back your time.  One of the ways we do this is through the free Financial Ratio reports that you can access through our Report Utility. Over… Read more

Easily move a row or column in Excel December 2, 2016

When wanting to move a row or column in Excel from one section of a worksheet to another (e.g. from row 13 to row 10), it’s normal practice to use the Cut and Insert Cut Cells command. However, an easier and quicker way to do this is the following: Select… Read more

How to prevent slicers from ruining filtered charts in Excel November 25, 2016

          Charts are used for creating a graphical presentation of the data.  A common practice when creating charts is to create them off of a filtered Pivot Table, usually a Top 5 or a Top 10 Pivot Table.  Slicers can then be added to interactively filter… Read more

Adding a report template to a new report in Sage Intelligence Reporting November 23, 2016

In our last tip, you discovered that with an existing report, you’re able to make and save changes to a layout as a result of the Excel template that is associated with it.  This means that any modifications you make persist each time the report is run, with the latest… Read more

How to quickly rearrange your worksheets in alphabetical order November 18, 2016

Are you looking for a shortcut to quickly rearrange your worksheets in alphabetical order?  Instead of dragging and manually arranging your worksheets, you can use VB for Microsoft® Excel® to quickly rearrange them into alphabetical order. Excel currently has no built-in function to rearrange your worksheets. Therefore, if you have… Read more

How to enhance your Excel dashboards with Power View reports November 11, 2016

Dashboards in Microsoft® Excel® are used for visually presenting important business information. Dashboards can display current status of metrics and key performance indicators. In this tip, we look at how to enhance dashboards with a Power View report by analyzing sales data. Power View reports in Microsoft® Excel® pull your… Read more

Saving changes to an existing report layout in Sage Intelligence Reporting November 9, 2016

Sage Intelligence Reporting allows you to create reports of your own, as well as customize your existing ones, all in the familiar environment of Microsoft® Excel®.  This is based on the Excel template that gets attached to each report.  Once it’s linked back, after any changes have been made, the… Read more

An easy way to count cells that are not empty in Excel November 4, 2016

Let’s say you want to count cells that contain the number of employees in a data range, and would like to ignore any blank cells. The way to go about doing this is to use the COUNTA function. The COUNTA function is a way to count cells containing any type… Read more

How to easily double-check your Excel formula October 28, 2016

When creating an Excel formula, sometimes the result you get is not quite the one you expected, and if a complex formula is being used, it can be time consuming to find where the error is occurring. If you’re ever writing a long formula and it doesn’t give you the… Read more

Get your filters to stick in PivotTable reports October 26, 2016

Have you ever tried to make use of filters in your PivotTable reports, only to find that they don’t stick when you save your template back to the report in the Report Manager?  When your workbook is generated, maybe you’d prefer blank items to be hidden, or you would like… Read more

How to use a slicer instead of a drop-down list in Excel October 21, 2016

Selecting data from a drop-down list is a convenient and accurate way of entering data into a range. It helps in ensuring that the correct data is entered quickly in Microsoft® Excel®. However, a slicer provides a quicker, better and easier way of selecting data from a list. In this… Read more

How to create a Tree Map chart in Excel 2016 October 14, 2016

The Tree Map chart is a new chart type available in Microsoft® Excel® 2016. It’s used to proportionally display hierarchical levels of data in the form of different sized rectangle blocks. A sales manager can use the Tree Map chart to monitor the performance of the sales consultants. This chart… Read more

Enable the recipients of your distributed reports to interact with PivotTables like you do October 12, 2016

Have you ever distributed reports containing PivotTables and found that your recipients aren’t able to interact with them, only having a snapshot of the data? Wouldn’t it be great if they could have some of the same functionality you do, like the ability to filter, drill down, or add other… Read more

How to convert dates into the correct format in Excel October 7, 2016

When extracting data from a database, dates don’t always show up in the format you want them to. That’s why it’s helpful to know how to use the Text to Columns option to convert dates into a format that can be used to easily analyze or work with data. As… Read more

How to create quarters in Excel September 30, 2016

Currently, there’s no function that will convert dates into quarters in Microsoft® Excel®.  However, using the CHOOSE function, we can easily convert dates into quarters in Excel. The CHOOSE function returns a value from a list of items based on a position number. For example, if we had a function… Read more

Quickly split data into two or more columns in Excel September 23, 2016

If you ever need to split data from one column in your Microsoft® Excel® worksheet into two or more columns, you can use the LEFT, MID and RIGHT Text functions. The LEFT function returns the first character or characters in a text string, based on the number of characters you… Read more

More efficient report running: Part 2 – Using system variables with scheduled reports September 21, 2016

In this tip, we continue from where we left off with the previous tip, and will demonstrate how you can use system variables when scheduling reports to run automatically. This will reinforce how they are applied during the scheduling process. If you didn’t catch the previous tip, you can find… Read more

How to change the default font size for Excel comments September 15, 2016

Adding a comment to a cell in Microsoft® Excel® is a quicker way of providing feedback. Instead of typing out an email to provide feedback, you can simply enter important information straight into the spreadsheet. However, you may want to change the default font size for Excel comments because it’s… Read more

How to quickly rearrange columns in a data table in Excel September 9, 2016

As with most commands in Microsoft® Excel®, there’s an easy way and a long way to rearrange columns in a data table. The long way would be to highlight a column, cut it, and then paste it where you want to move it to. In this tip, I’m going to… Read more

More efficient report running: Part 1 – System Variable date Parameters September 7, 2016

Have you ever wondered how you can set up your reports to pull data from the same day you run them, without having to manually change the Parameter values each time? Sage Intelligence System Variables make this possible and allows for more efficient report running. This works especially well when… Read more

Get a hold of your Sales Flash Report and the AVERAGEIF Function August 23, 2016

Would you like to keep track of your most important sales indicators in no time at all?  If so, why not check out the free Sales Flash Report provided through the Sage Intelligence Report Utility.  This report gives you a quick view of your Sales over a particular day or… Read more

Learn how to categorize debtors balances in Excel using icon sets August 18, 2016

As an accountant, you can easily categorize debtors balances by using the conditional formatting – icon sets option. Icon sets creates a visual effect in your data and helps you to see how the value of a cell compares to other cells. In the example below we categorize the debtors… Read more

5 Easy steps to enhance your dashboards in Excel using the camera tool August 11, 2016

The camera tool enables you to take a picture of a range of cells and display it in a preferred way, thus allowing you to enhance your dashboards. The picture will automatically update when the data in the original cells change. In this example, the column Sparkline chart will be rotated to… Read more

Learn how to preserve formulas in older versions of Sage Intelligence Reporting August 11, 2016

In our previous two tips we focused on Distribution in Sage Intelligence Reporting.  In the first tip we showed you how Intelligence Reporting gives you the option to send reports either with embedded formulas or without. When doing so, you also need to take into consideration whether your recipients have Intelligence Reporting installed… Read more

5 Easy steps using the combination chart in Excel to help you monitor budgets July 20, 2016

There are many different charts in Microsoft® Excel®, but only one that allows you to highlight different types of information by combining two or more chart types into one. The combination chart can be used to see how far away a value is from the target or if it is… Read more

Learn how easy it is to reduce the size of an Excel workbook using a single array formula July 14, 2016

We often find ourselves working with Microsoft® Excel® workbooks that contain large amounts of data, causing them to process instructions slowly. To reduce the size of an Excel workbook, consider replacing several formulas with a single array formula. Array formulas can perform multiple calculations and ensure consistency which can lead… Read more

3 Easy ways to distribute your Sage Intelligence reports with or without embedded formulas July 13, 2016

In Sage Intelligence Reporting you have the ability to distribute reports using 3 different methods. 1. You can send a report via email 2. You can send a report to a FTP location 3. You can publish your file to a drive In older versions of Sage Intelligence Reporting, only… Read more

4 Easy steps using the Advanced Filter to extract unique values in Excel July 5, 2016

Have you ever found yourself in a situation where you have thousands of duplicate values in a column? Perhaps you were importing client data into Microsoft® Excel® from an accounting system or simply combining data in Excel, but often end up having duplicates values in a given column. If you… Read more

2 Easy steps using the TRANSPOSE function in Excel to re-arrange data June 30, 2016

Have you ever needed to change the orientation of a range of cells whilst maintaining links to the source data and end up having to manually recreate the spreadsheet? Here’s a quick and easy way using the Microsoft® Excel® TRANSPOSE function. The TRANSPOSE function returns a vertical range of cells… Read more

Learn how to create a performance profile for all your Sage Intelligence reports June 29, 2016

Do you have reports that take a long time to run out? Not sure whether it’s normal, due to a large amount of data, or if there’s a problem? Sage Intelligence Reporting has a built-in feature for creating a performance profile when reports are generated. Allowing you to determine whether… Read more

5 Easy steps to display positive or negative values in Excel using custom number formatting June 23, 2016

In order to effectively analyze data, custom number formatting can be applied to your Microsoft® Excel® spreadsheet. Custom number formatting is used to easily identify values based on a set criteria. In a large Excel spreadsheet, you can easily highlight all negative or positive values by using custom number formatting.… Read more

4 Easy steps to auditing complex formulas in Excel June 16, 2016

When creating formulas in Microsoft® Excel®, the expected result is not always returned, and if a complex formula is being used, it can be time consuming to find where the error is occurring. Luckily there is an easy way to audit complex formulas in Excel. In this tip we explain how… Read more

Learn how to license Sage Intelligence in a Citrix or Terminal Services environment June 15, 2016

Sage Intelligence Reporting licensing can be tricky, especially given the different license types and the possible network configurations. In this tip we’re going to look at the case of Citrix and or Terminal Server environments and consider the different options available. This includes Terminal Server, Citrix XenApp / XenDesktop, load… Read more

Learn how easy it is to create a financial dashboard in Excel June 10, 2016

If you are looking for better insight into your businesses financial performance, then creating an interactive dashboard might be the answer. By using a dashboard, you can quickly and easily recognise trends, do comparisons and review performance indicators that will lead to informed decision making. This week we’ll take you… Read more

Learn a better way to visualize your data using the Bing Map app in Excel June 2, 2016

The Bing Map app is a flashy Microsoft® Excel® Add-In app that makes it easy to plot locations and visualize your data in Excel. The Bing Map would be best to use with data where you have to display values for regions or countries. In today’s exercise we will use… Read more

Easy steps to opening an Excel workbook on a specific worksheet May 27, 2016

Assuming you have one frequently used Microsoft® Excel® worksheet in a big workbook, wouldn’t you prefer to land on that specific worksheet when opening the workbook? By creating the simple macro given below, one can land on a specific Excel worksheet and even a specific cell when opening a workbook. Follow these… Read more

Three ways to achieve the same result in Sage Intelligence Reporting May 25, 2016

Sage Intelligence provides a lot of flexibility when it comes to creating and using reports. Often, the same task can be accomplished in different ways and you can decide which one works best for you. In this tip, I’m going to show you three different ways to accomplish the same… Read more

Learn how to quickly remove hyperlinks from an Excel worksheet May 19, 2016

Are you looking for an easy and effective method of removing hyperlinks from your worksheet? Sometimes when you copy data from the internet or other sources, you often run the risk of bringing across embedded hyperlinks. Manually removing all the hyperlinks from your worksheets can be a tedious and time consuming… Read more

Protect parts of your Excel worksheet to prevent unwanted changes May 12, 2016

Are there parts of your Microsoft® Excel® worksheet that you wish you could protect? For instance you want to protect certain cells that contain certain formulas and important data, then Worksheet Protection is the answer. Worksheet Protection prevents someone from accidentally or deliberately changing, moving, or deleting important data from… Read more

Learn how to deal with different fiscal years in Sage Intelligence Reporting May 11, 2016

By using the consolidated Financial Report Designer in Sage Intelligence Reporting, you can compare the financial information from different companies all in the same workbook. There may be times when the companies you are looking at have different fiscal years, for example, one may run from January to December and… Read more

An easy way to generate PivotTables on separate worksheets in Excel May 6, 2016

You have taken time to create a product sales PivotTable and now you would like to analyze the data. You want to analyze the sales per branch by quickly generating PivotTables for each branch to be placed on separate worksheets. In this week’s tip, we take you through a few easy… Read more

Easily organize data in Excel using multiple data consolidations for PivotTables April 27, 2016

The PivotTable is an effective tool for working with large volumes of data, it allows you to  summarize, organize and view the same data in many different ways quickly and easily. By using the PivotTable multiple consolidation ranges option you can create a PivotTable that consolidates data from more than… Read more

5 Easy steps to change the periods in your cash flow report to months April 27, 2016

When working with a report there might be times you’d like to see the information displayed in a different format. A common question people ask is, how they can see month names instead of period numbers in their column headings. With Microsoft® Excel® this is easy to do. In this… Read more

Get creative in Excel using sparklines to show data trends April 21, 2016

New in Microsoft® Excel® 2010, and later versions, a sparkline is a tiny chart in a worksheet cell that provides a visual representation of data. Use sparklines to show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight maximum and minimum values.… Read more

An easy way to create a dual chart in Excel, trending with a secondary axis April 14, 2016

Charts by default display a primary vertical axis when created. This works if there is only one unit of measure in the data. However, should there be different units of measure in your data, a secondary axis will be required, thus allowing you to create a dual chart in your… Read more

Design modern layouts that also work in older versions of Sage Intelligence April 13, 2016

Wouldn’t it be great if you could create layouts using the latest features of the Financial Report Designer, including the Layout Generator and Task Pane that also work in older versions of Sage Intelligence? The great news is that you can design modern layouts and still have it work in older… Read more

Learn how to create a thermometer chart in Excel to show your performance against a set goal April 7, 2016

Did you know that you can create a thermometer chart in Microsoft® Excel®? You can use the thermometer chart to compare your performance against a set goal. For example, revenue vs target or budget vs actual, can be plotted using a thermometer chart. The performance value and its corresponding target… Read more

How to create an Excel combination chart from scratch March 31, 2016

After you have created your Microsoft® Excel® spreadsheet, you can visually represent the worksheet data by creating a chart.  Charts often make your data clearer and easier to understand. If you find that the data is not in sequence or in a group of cells, you may need to create… Read more

Use the DSUM function to add data according to set criteria in Excel March 24, 2016

When analyzing your data, you may want to add certain parts of it up based on set criteria. You are able to do this using the DSUM Function in Microsoft® Excel®. This function enables you to drill down into your data and get specific results, leading to better decision making… Read more

Learn how to easily combine reporting periods in Sage Intelligence Reporting formulas March 23, 2016

One of the advantages of Microsoft® Excel® that it allows you to present and easily total your data across multiple time frames. There’s now also an easy way to combine reporting periods in Sage Intelligence Reporting formulas. When reporting on different periods using the Sage Intelligence Report Designer, this is usually done… Read more

Use the TRIM function in Excel to easily remove spaces between data March 19, 2016

Are you constantly battling with the task of having to manually remove leading spaces between data? Now you can easily remove spaces from your data, except for single spaces between words, using the TRIM function from Microsoft® Excel®. Assuming you have imported data with irregular spacing into Excel an worksheet, in this tip… Read more

Use the MID and FIND functions in Excel to easily link a worksheet to a cell March 19, 2016

Some businesses use Microsoft® Excel® to prepare and present their business reports. Report headings often contain worksheet names, and to make your reports more efficient, you can link these worksheet names to a specific cell. So, instead of retyping the worksheet names in your report headings, you can easily apply the… Read more

5 easy steps to stop PivotTables from returning deleted data in Excel March 19, 2016

In some cases you might find yourself deleting data, and when applying a PivotTable that deleted data may resurface and cause a number of inaccuracies in your PivotTable. In this week’s tip we use 5 easy steps to ensure that when you apply PivotTables to your data, it will automatically… Read more

Use the Sampling Analysis tool in Excel to easily select random numbers. March 19, 2016

This week’s tip is especially handy when running competitions for your business, so please share it with your promotions and marketing teams. The Sampling Analysis Tool is great for when you need to randomly select a number from a given range of values. An example of this is the following;… Read more

Learn how to forecast with scenarios using what-if analysis tools in Excel March 19, 2016

The Scenario Manager is a feature in what-if analysis and you can use this to enhance your forecasting in Microsoft® Excel®. A scenario is a set of values that Excel saves and can automatically substitute in specific cells on a worksheet. You can create and save different groups of values… Read more

Understanding the additional options of the ZeroingII Add-in when using Sage Intelligence March 19, 2016

The ZeroingII add-in is used to hide rows in a report where the values in a particular column meet certain criteria (normally they equal to zero).  It’s added to a report using the Run Add-in field under the report’s properties in the Report Manager and is performed when the report… Read more