How to Enter Data for Analysis in Excel (2 Easy Ways)

In every business, it’s quite urgent to analyze the data to evaluate the condition of a business. It will help to determine the risks and take proper steps to reduce loss. In excel we can do that easily using some methods. Today in this article, we’ll show 2 easy methods to enter data in Excel for analysis with sharp methods and vivid illustrations.


Download Practice Workbook

You can download the free Excel workbook from here and practice independently.


2 Ways to Enter Data for Analysis in Excel

Let’s get introduced to the dataset that we’ll use to explore the methods. It represents some yearly sales and profits in different regions.

How to Enter Data in Excel for Analysis


1. Using Pivot Table to Enter Data for Analysis

A tabular format of data always helps to manage data easily and analysis properly. Excel Pivot Table feature offers many useful ways to enter data in Excel for analysis. We’ll analyze the profit according to individual regions. Let’s go forward to explore it.

Steps:

  • First, click on any data from the dataset.
  • Then click as follows: Insert > PivotTable.

Using Pivot Table to Enter Data in Excel for Analysis

  • After getting this dialog box, just choose the worksheet option, and it will choose the data range automatically. we chose New Worksheet.
  • Next, press OK.

Select Sheet for Pivot Table to Enter Data in Excel for Analysis

The PivotTable Fields area will appear on the right side of your worksheet.

  • To analyze the profit according to individual regions, drag the Region header in the Rows field and the Profit header in the Value

Now look, the pivot table is showing the sum of profit for each region. So, now we can easily analyze which region is giving us the highest profit and which is giving us less profit.

Read More: How to Analyze Large Data Sets in Excel (6 Effective Methods)


2. Using Data Analysis Add-in to Enter Data for Analysis

Besides Excel’s default features and commands, Excel also allows using official Add-ins and third-party Add-ins to enter data into Excel for analysis. Analysis ToolPak is that kind of official Add-in. Now we’ll learn how to organize data in Excel for analysis using the Add-in. Before using the Add-in, we’ll show you how we prepare Data Analysis Add-in in Excel. If you know and already activated then skip these steps.

Steps:

  • Click on the File tab beside the Home ribbon.

Using Data Analysis Add-in to Enter Data for Analysis

  • Later, select Options from the appeared menu.

Select Options to Use Data Analysis Add-in to Enter Data for Analysis

  • Next click: Add-ins > Excel Add-ins > Go.

  • After appearing in the Add-ins dialog box, mark Analysis ToolPak and press OK.

Read More: [Fixed:] Data Analysis Not Showing in Excel (2 Effective Solutions)


Descriptive Statistics

Firstly, we’ll learn how to use the Data Analysis ToolPak in Excel for Descriptive Statistics to get the summary of statistics.

Steps:

  • Click on the Data ribbon and select the Data Analysis command from the Analysis section.

Open Data Analysis Add-in to Enter Data for Analysis

Soon after, the Data Analysis dialog box will appear.

  • Select Descriptive Statistics from the tool list and press OK.

Choose Descriptive Statistics Tool in Data Analysis Add-in to Enter Data for Analysis

  • Here, we’ll make summary statistics report from the Profit column. So, select the range from the Profit column for the Input Range.
  • Then you will get different output range options, we chose New Worksheet Ply.
  • Finally, mark any statistical option from the lower section and press OK. We marked Summary Statistics.

After a while, you will get the report in a new sheet like the image below. It will contain some useful statistical factors like Mean, Median, Sum, Count, etc.

Read More: How to Convert Qualitative Data to Quantitative Data in Excel


Histogram

A histogram chart helps to show the distribution of values in a particular range. Here, we’ll learn how to use the Data Analysis Add-in in Excel for Histogram.

Steps:

Choose Histogram to Use Data Analysis Add-in to Enter Data for Analysis

  • Again select the input range from the Profit column and mark the Output option.
  • Finally, mark any chart option and press OK. We marked Chart Output.

Here’s the histogram output in a new sheet with a chart. It’s showing the frequency of values.


Regression

We can also perform a regression analysis in Excel using the Data Analysis ToolPak.

Steps:

Choose Regression Tool to Use Data Analysis Add-in to Enter Data for Analysis

  • Here, we selected the Profit column for Input Y Range and Sales for Input X Range. Because we want to predict sales based on the profits.
  • Lastly, select the output option and press OK. here also we set New Worksheet Ply.

A few moments later, you will get the regression summary output like the image below.

Read More: How to Analyze Sales Data in Excel (10 Easy Ways)


How to Enter Data for Sensitivity Analysis

Based on a given data sensitivity analysis extracts the other predicted data. To learn how to use data tables in Excel for sensitivity analysis, we modified the dataset that is showing a product’s sold unit, price per unit, and cost per unit. We’ll apply Sensitivity Analysis to get the other data for achieving the corresponding profit.

Steps:

  • First, use the following formula in Cell C7 to calculate the profit-
=(C5-C6)*C4

How to Enter Data for Sensitivity Analysis

  • Next, insert the other data in a new table like the image below for those you want profits.
  • And insert the calculated profit in the joining cell of the new data.

Insert Reference Cell to Enter Data for Sensitivity Analysis

  • Next, select the new data range and click as follows: Data > Forecast > What-If Analysis > Data Table.

  • In the Row input cell box, select Cell C4, and in the Column input cell box, select Cell C5.
  • Then press OK.

See, the table is now giving us the profits according to the new data of the table.

Read More: How to Analyze Text Data in Excel (5 Suitable Ways)


Conclusion

That’s all for the article. We hope the procedures described above will be good enough to enter data in Excel for analysis. Feel free to ask any questions in the comment section and give us your valuable feedback.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo