Question: How do I make a specific cell mandatory to fill in, in an Excel workbook?  We have a form that employees fill in but require that important information like employee names to be mandatory.  Can this be done in Excel?  Answer: Yes, by using Visual Basic For Applications (VBA) Why:  To make a specific […]

Continue reading

Question: We have just upgraded from MS Excel 2007 to MS Excel 2010.  When applying conditional formatting in MS Excel 2007 we were unable to display negative data bars.  Can this be done in MS Excel 2010?  Answer: Yes, by using conditional formatting Why:  To create data bars for negative & positive values  Applies To […]

Continue reading

Question:   How can one merge MS Excel with MS word given that we have a list of clients stored in MS Excel? Are we able to automate the sending of statements to clients?  Answer:   Yes, by merging MS Excel with MS word Why:   To automate the sending of documents by merging MS Excel with MS […]

Continue reading

Question: Given that I have a column of dates in values in my report, how can one only show the name of the months and can this be done in Microsoft Excel? Answer: Yes, by using the text function in Excel Why:  The Text function coverts a value to text in a specific number format […]

Continue reading

Question: Can you help me to apply custom format for employee numbers to all excel workbooks on my computer?  I would like the employee numbers to have preceding zeros such as 001,002,003 etc.  Answer: Yes, with the Personal Macro workbook option  Why:  For the custom format to apply to all workbooks on the computer Applies […]

Continue reading

Question:  I find it tiresome switching between ribbons in Excel 2010 when I am creating reports. Is there a way of customizing a ribbon so that I can group all the frequently used functions together?  Answer: Yes, by creating a custom ribbon in Excel 2010. In fact, to make your reporting easier, Alchemex has created […]

Continue reading

Question: Excel is known for its excellent formulae & function capabilities. As such I extensively use Excel to calculate the variance between the target sales amount and the actual sales amount for our salesagents. However, sometimes the formulae returns errors/error messages such as #DIV/0! Is there a way of trapping error messages, so that a […]

Continue reading

Text Lengths in Excel Question:     I need to export the stock list to our database program for further analysis.  Last time I tried the routine, the names of the products were truncated because the wrong field size was used in the table. How can I return the number of characters in a text string? This […]

Continue reading

Converting Text Cases in Excel Question: I usually export my inventory data into Excel for further analysis. However the products names appear in upper case when I do this. Is there a way of converting the product names to lowercase and then capitalizing each word – how do I do this in Excel?  Answer: By […]

Continue reading

Question: As an effective tool for working with large volumes of data, I usually use PivotTables to summarize, organize and view the same data in many different ways quickly and easily. However the data is usually in one data source/range. Is it possible to create a PivotTable based on multiple data consolidation ranges? Answer: Yes, […]

Continue reading