Category: Applications

Excel Visual Basic (VBA) for Beginners - Use Excel VBA to Cleanse a Dataset

How to Select Cells by Value in Microsoft Excel

 

 

When you have a lot of data in a table and want only to select a group of cells with a certain value we can use Excel search for quick selection of those cells.

From the following table of data I want to select all the cells that have the value $ 1,000 and once selected place the cell text in bold

Select Cells by Value

Selecting by the Search Method

An alternative is to use the Excel search box to select the cells that meet the condition set. To do that we open the search bog, then we specify the value we’re looking for and finally click the Find All button.

Find All Selected Data by Value

Excel display all the cells that match the specified value, we select only the results for the worksheet cells are also selected Tip: To select all results can click on the first row of results and then press the Shift key and click the last row. Since you have selected all results, close the search window and observe the cells selected in the worksheet ready to apply the text in bold

Formatting Select Data

Using a Conditional Formatting Rule

If we make a selection of cells to make a format modification, then we can use an alternative method Once we have selected the range containing the information (b2:E16) I must press the Condittional Format button on the Home tab and select the Highlight cell rules option and then Equal To button.

Conditional Formatting to Select Cells by Value

In the dialog box I have to specify the quantity that I’m looking for and select the Custom Format option.

Custom Format

Then I’m going to select a Bold style for the fonts to format the cells that comply with this rule

Conditional Formatting Bold Style

Then click OK to close the dialog box and then click OK to apply the conditional formatting rule, we will have the cells of value equal to $1000 with a bold style format. Excel Total
 

 

How to Use Excel Comments

How to Use Excel Comments

 

 
 

Excel allows you to insert comments in any cell of your Worksheet in Microsoft Excel. Those Excel Comments are very useful to make a check, give some instructions or simply give a detailed explanation about the cell content.

Add a comment to a Cell

The first step is to select the cell that you will add the comment and then go to the Review tab and in the group Comments and press the New Comment button (In MAC only New). Clicking on that button, the box necessary to enter the comment is displayed.

Add a Microsoft Excel Comment

Enter your comment in the box and to finish click outside the box. If you need to enlarge the box only positions the mouse over any of the corners and drag the edge to have the desired size. Excel identifies a cell that has a comment through a small triangle in the upper right corner of the cell. To see the comment from a cell only you must position the mouse over that cell and Excel will display the comment.

Modify or Eliminate a Comment

When you select a cell containing a comment Edit and Delete Comment buttons are enabled.

Excel Comment Options

Clicking on the Edit Comment button will show the comment box allowing you to do the editing again. If instead you want to delete the comment is sufficient to press the Delete button. Now if you have an Excel worksheet that contains many comments, you can use the Previous and Next buttons to move between each. Or conversely, if you want to display all comments sheet at the same time you can click Show All Comments button. You can learn more about Microsoft Excel Comments and Review options here: Excel School
 

 

Excel Visual Basic (VBA) for Beginners - Use Excel VBA to Cleanse a Dataset

Excel Visual Basic (VBA) for Beginners to Cleanse a Dataset

 

 
This Excel video series helps Excel users just starting out with code in the Visual Basic program, or those who have never coded before, who are curious about it and just heard about it, about its powerful utilisation. In a lot of cases, Excel users find themselves engaged in tricky manual tasks in study subjets or work tasks, that take up time, create stress and lose of time. Data cleansing is one such task.

To explain that, if you have ever gone through a list of data and looked for mistakes (in dates or numbers, for example) then you have done a data cleansing task. With a little coding knowledge, that we are going to see in the following videos, it is possible to dramatically speed up this kind of task, reduce the stress and increase your productivity. You can learn more about Microsoft Excel go on here: Excel School

The 1st Part: Introduction

The 2nd Part: Beginning with VBA

The 3rd Part: Using Messaging Boxes

The 4th Part: Stopping the program

The 5th Part: Finishing and testing the program

 

 

Dependent Data Validation Lists in Microsoft Excel

Dependent Data Validation Lists in Microsoft Excel

This video will walk you through the process of setting up and using dependent data validation lists with Microsoft Excel. You could refer to this as cascading data validation. This is very useful to create templates of databases that you need to be completed by other users. The other users only have a limited option to fill each cell. You can learn more about Microsoft Excel productivity here: Excel School

 

What is Microsoft Excel Used For

What is Microsoft Excel Used For?

Microsoft Excel is one of the most used software by people in professional and personal level. It is a powerful tool for productivity that allows you to keep you data organised.

The most common uses are: 1. You can make simple spreadsheets and tables to keep tracking you business indicators …

Simple Spreadsheet for Business Tracking
2. Make big tables with complex budgets of incomes and expenses in business or in your home …

Complex Business Budget Excel Spreadsheet
3. Manage big bunch of data in organized databases …

Manage Databases in Microsoft Excel
4. Create and manage lists, of clients, products, employees, etc …

Manage lists in Microsoft Excel
5. Create one of the different types of Charts to analyze data in a simple way …

Create Microsoft Excel Charts

6. Make complex calculation with one of the more than 300 functions that Excel has …

Use Microsoft Excel Functions
7. Show graphs and visual objects with the Excel Smart Tags …

Use Excel Smart Tags
8. Analyze your data and show tables with filtered data with the Pivot Tables …

Create Microsoft Excel Pivot Tables
These are some of million of uses for Microsoft Excel, Learn more with Microsoft Excel MOOC

Advanced Formula Challenge #12: Results and Discussion

Last week I set readers the challenge which can be found here. Such was the number and variety of responses to this challenge that presenting a detailed breakdown of one such solution – as has been the case for all of the first eleven in this series of challenges – would, I feel, be somewhat inappropriate. For the majority of these challenges, it could be argued that there has been one solution which is indisputably “better” than the rest. Perhaps such an adjudication can also be made here, though to do so would certainly not More…

How to make a Supply and Demand Chart

How to Make a Supply and Demand Chart

 

 

One of the basics concepts in the economic class in the high school and in the university were the supply and demand law that is useful to know the price and the product quantity to satisfied a specific market

The supply and demand curve is usually plotted using the product price on the vertical axis and the quantity of the product on the horizontal axis. This time we will learn how to make a supply and demand curve in Excel.

SUPPLY CURVE

In simple words, the law of supply refers to producers and to maximize their profits. Therefore, the higher the price of a product, the greater the amount that the product want to provide. And conversely, the lower the price the lower the quantity supplied. The following table contains the data to graph a supply curve in Excel:

Supply Table

To create the corresponding graph is sufficient to select any cell in the table and go to the Chart tab and select the button Line, then Marked Line. Now you must pay attention because it is very likely to get a graph like this:

Supply Chart 1

To correct this graph we can click right on it and choose the Select data. Then we select the series called Quantity and click the Remove button:

Select Data for Supply Demand Chart

Then you have to put in the label Category (X) axis labels the range A3:A7 choosing the cells. As a final step we can change the name of the series Price by select the data source and set the cell A1 as the name of the series in the Name label. At the end we list changes a graph with the supply curve:

Initial Supply Chart

DEMAND CURVE

The law of demand states: Few people want expensive products. But if we have cheap products (almost given away), many people want them. See the following table for the data to graph a demand curve in Excel:

Demand Table

To include a line in our chart representing the demand curve must right click on the graph and select the Select data and add a new series whose values are in the range E3: E7.

Add a Serie for the Supply Chart

By accepting the changes we have ready our chart with a supply and demand curve in Excel.

Supply and Demand Chart in Excel