Sometimes you need to insert two values into the same cell. Like having the profit variation in absolute terms and in percentages in a single cell. Different format for two values in a single cell The solution to this problem is the TEXT(value,format) formula. Imagine that you need to show the variance over two years […]

Isn’t it nicer to see negative percent values in red color instead of all black, differentiated only by the brackets or by the (-) sign?! The format code pattern to show negative percent in red color Select the cells you want to format and click on Format cells… button. On the Number tab write in […]

Whenever you need to count the elements inside data series the excel countif formula is available to help you reach your target easy and fast. The Countif formula has the following structure: =COUNTIF(Range,Condition) Note that the Range can be a large table not only one column. Examples of Excel Countif Formula Let’s say we need […]

The easy way to count the characters in a text, without counting the spaces too, is to use the LEN excel formula on a string text that has the spaces removed. Removing spaces before counting characters Removing spaces inside a text is done by using the SUBSTITUTE formula with the following expression: =SUBSTITUTE(“text where you […]

Optimizing the conclusions presented by an Excel worksheet data processing algorithm usually needs a solution for information sorting in order for it to be displayed top-down by importance factor to the subject.  Additional formulas needed You need to insert the formulas to calculate automatically the position of each item in the list based on it’s […]

Comboboxes are user friendly data input forms that can make the difference between a quality looking form and a archaic data input screen. Defining combobox in Excel For this step you must go to the Excel Developer menu, make sure you have this menu since it doesn’t come activated in Excel Options. Select the Insert […]

The solution for this problem is simple. The words are separated by spaces and these spaces need to be counted in order to find out how many words there are inside the text. Worksheet formula to count words in a text If we eliminate the spaces with the Substitute formula and count how many eliminations […]

Reading from SQL tables usually means that there is a need for supplementary data processing and manipulation before conclusions and information being provided to the decision making person. Excel is the best application to satisfy this need and some very useful and ergonomic reports can be developed in order to provide fresh and accurate information. […]