MS Excel

Changing the width of an Excel column to accommodate all text in a single line is something I do frequently while working with MS Excel worksheets. After a while it gets tiresome to select Format > AutoFit Column Width from the ribbon all the time. So here is a wonderful Windows short cut that will…

Let’s say you have a reference START DATE like “February 2, 2005″. You would like to calculate the exact WORKDAY 100 days BEFORE or AFTER that date. How would you do it? What’s more, how would you do it if the “weekend” definition varied, like “Sunday only” or “Saturday and Sunday”, or even just “Monday”?…

Imagine you’re running a manufacturing plant, producing hats, shirts, gloves, and other similar items. You know the following: The number of each item you are now manufacturing. The number of labor units you need to produce each item. The number of raw material units you need to produce each item. The unit price, unit cost,…

To calculate optimal product mix with MS Excel “Solver” to maximize profits is easier than you thought. Here is the situation: Imagine you’re running a manufacturing plant, producing hats, shirts, gloves, and other similar items. You know the following: The number of each item you are now manufacturing. The number of labor units you need…

© Ugur Akinci Let’s imagine you’re working with the following data set: Let’s say you want to find only those transactions in which SHOES were sold for LESS THAN $20. Follow these steps: (1) Enter a couple of empty rows to the top of your table. (2) Copy your HEADER row and paste it to…

To change the SUM of a Range Dynamically is easier than you thought in MS Excel. Let’s say you have a data table like this: PROBLEM: We want to create a SUM for the GROSS column that would adjust itself dynamically when new sales records are added to the table. How would you do that?…

© Ugur Akinci Let’s say you have a list of hundreds of names typed with all kinds of capitalization errors: PROBLEM: How can you correct the capitalization errors in every name and make sure each first and last names begins with a CAPITAL letter, followed by lower-case letters? SOLUTION: It’s very easy. Just select a…

Time for a simple MS Excel macro. Imagine you have a MS Excel document that is updated regularly or frequently. So you think it’d make sense to display the last date on which the document was modified so that your readers would know how fresh and updated the document is. If you enter the formula…

PROBLEM: Your MS Excel worksheet (see below) contains hundreds of identical rows and you’d like to delete them without spending hours to search and identify them. SOLUTION: Follow these steps… 1) Select all the rows in your worksheet, including the column headers (if any). 2) Click and select the DATA tab on the ribbon. 3) In…

Merging MS Excel cells and centering the contents is so easy: you just select the cells and click the Merge & Center button on the Home tab of the ribbon. But what if you don;t want the content to center? What if you’d like to left- or right-justify the text within the merged cells? Then…