Excel Tips & Tricks

Question:  In order to effectively monitor the performance of the stock items, I need to have a list of the best performing products in quantity sold. This helps in planning and making sure that enough stock is available at all times. I specifically need to display a list of the top three items. How can [...]

{ 0 comments }

Question: One of the problems I face as the payroll administrator is that when entering the salaries for staff members, I sometimes enter figures outside the respective salary bands. Our employees are categorized into various grades which then determine the salary bracket. Is there a way of adding some validation so that when the wrong [...]

{ 0 comments }

Question:   When working with large spreadsheets, it gets a bit frustrating when you go to read the data in a distant row or column and the headings at the top of the page or down the side of the sheet disappear. Given that I have a huge list of data to analyze in excel, is [...]

{ 0 comments }

Question:  I am in charge of finance for a large company with various branches throughout the country. The branch accountants send their budgets quarterly to the Head Office.  I then present a consolidated budget to the company board.  Currently I have to recreate the consolidated budget for all the branches. This obviously takes time and [...]

{ 0 comments }

Question:   One of my tasks as the Sales Manager is to calculate the percentage increase or decrease in sales at the end of the financial year in comparison to prior year sales figures. The formula is quite complex and usually the data captures make mistakes when called upon to enter the formula. Is it possible [...]

{ 0 comments }

Question: I usually export the stock movement data from our Accounting Package into Excel. However I struggle to calculate the total for the negative values (stock out) and positive values (stock in) separately .The data is placed in one column when exported to Excel. Is there a way of overcoming the allocation of positives and [...]

{ 0 comments }

Question: As the organizer of weekly sales meetings, I am required to prepare an electronic attendance register to the Sales Director. I currently use MS Excel for recording the attendance. I usually enter “A” for staff who attended the meeting and an “X” for those who were absent. Is there a better way of tracking [...]

{ 0 comments }

Question: I have a workbook that I use for budgeting and need to paste the same data into many of the worksheets. Is there a quicker way of doing this than the usual copy & paste? Answer: Yes, with the FILL ACROSS WORKSHEETS option. Why:  To fill data in many worksheets simultaneously Process (Excel 2003, [...]

{ 0 comments }

Question: Can I evaluate multiple conditions in a formula?  Answer: Yes, with the Nested If Statement  Why:    In order to construct more elaborate tests of the data Process (Excel 2003, 2007 and 2010): The If statement can be used to conduct conditional tests on values and formulas. However if more elaborate testing of data will [...]

{ 0 comments }