If you create a chart from a worksheet with data that is hidden in rows and columns or that has empty cells, by default the hidden data will not be displayed in the chart and the empty cells will be displayed as gaps in the chart. To override this you can use the Hidden and […]

Continue reading

Icon Sets in Conditional Formatting create a visual effect in your data so that you can compare the value of a cell with other cells. For example, if you are an accountant and want to easily categorize debtors based on how much they owe your company you can use Conditional formatting – Icon Sets. In […]

Continue reading

A data form provides a convenient means to enter or display information in a range or list without scrolling horizontally. A data form can make data entry easier than typing across the columns when you have a wide range with more columns than will fit on the screen at one time. A data form simply […]

Continue reading

This month’s Financial Function tip is on the NETWORKDAYS.INTL function which is very similar to the NETWORKDAYS function that we covered last month. Both functions are used to calculate the number of working days between two dates. The difference is that NETWORKDAYS limits weekend days to Saturday and Sunday, NETWORKDAYS.INTL allows you to choose any two days of […]

Continue reading

By using the DSUM function, you can specify criteria and conditions regarding which cells should be added together. An alternative to using DSUM is using SUMIF, but SUMIF is not suitable for complex criteria. The list below shows monthly and daily product sales. In cell G5 we have calculated a running total using the DSUM function, […]

Continue reading

You may have imported or entered account codes into a Microsoft Excel spreadsheet and want to identify invalid account codes. Let’s say the account codes are supposed to have five characters but because of data entry errors some accounts codes do not meet the required criteria; they either exceed or are less than five characters. […]

Continue reading

Have you ever had your data in an excel workbook accidently or deliberately tampered with? You could be working on a project and would like to restrict access to data on some shared workbooks. In the following tip we explain how the entire worksheet/PivotTable can be protected with the user only allowed access to the […]

Continue reading

Are you looking for a simple yet effective way of monitoring the performance of your sales staff? Perhaps you are the Sales Manager and would like to analyse the performance of your staff. The COUNTIFS function can be a great formula to use in order to ascertain if the Sales Representatives have met their targets. […]

Continue reading

Microsoft Excel uses actual numbers to store specific dates inside of Excel. By subtracting these two numbers, you can then tell how many days there are between a given start date and a given end date. However this doesn’t necessarily take into account days like Saturdays and Sundays which may not be trading days for […]

Continue reading

Scenario: You don’t always have control over the format and type of data that you import from an external data source, such as a database, text file or a Web page. Before you can analyse the data, you first need to clean it up. Below we will discuss the REPLACE function; which replaces part of […]

Continue reading