Excel Tips & Tricks

Are you frustrated at the prospect of manually removing leading and trailing spaces from data? There is absolutely no need to worry, because by using the Trim function the wasteful and inefficient method of painstakingly removing leading and trailing spaces from data can be eliminated. Assuming you have imported data with leading and trailing spaces [...]

{ 0 comments }

If you are looking for an easy and yet useful function that can Sum data based on multiple conditions, then look no further than the Sumifs function. Assuming that you want to calculate the total sales figure for a given month and region the Sumifs function can be used to produce the desired results. In [...]

{ 2 comments }

Given that you have a list of names & surnames in a data range and your task is to extract the first letter of the names as the initial. By using the LEFT text function this supposedly complex task can be executed in one simple step and in no time at all.  The steps below [...]

{ 1 comment }

You have created budgets for various branches on different worksheets in your excel spreadsheet.  However you are wondering how you can automatically generate a consolidated budget for all the branches. Well, no need to worry because by using the consolidate option the task of summarizing data from various worksheets is simplified. Below we explain how [...]

{ 0 comments }

You have taken time to create a product sales PivotTable and placed the branch field in the report filter area, product name in the row area and product sales in the values areas. However you want to analyse sales per branch by quickly generating PivotTables for each branch to be placed on separate worksheets. You [...]

{ 0 comments }

Ever wanted to create inventory running balances and could not do so?  Well by using simple Excel sum function you can actually see the running balances as you enter new inventory over time. As the stores controller, knowing the correct stock on hand is vital. Follow the simple steps below as we explain how that [...]

{ 0 comments }

If you want to view parts of several worksheets on one worksheet, try using the Paste Picture Link Option. This will enable you to print multiple areas of a workbook onto one worksheet.  Dashboards summarizing parts of the workbook can also be easily created as explained below. Applies To: Excel 2003, 2007 and 2010 Select [...]

{ 0 comments }

By using the MAX function, the maximum value in a range can be calculated. However to find the cell with the highest value in a range three functions(Address, Match & Max) will have to be nested. The steps below explain how that can be done.  Applies To: Excel 2003, 2007 and 2010 The screen shot [...]

{ 1 comment }

Given that you imported data into excel with the First Name and Surnames in separate cells – The Concatenate function can be used to join the cells and have the First Name first, then the Surname. This will obviously automate the process of data entry meaning you can work smarter and effectively. Applies To: Excel [...]

{ 0 comments }