How to Track Changes in Excel

Tracking changes in Excel is a critical feature you need for managing and reviewing edits when working with a team

You can use this functionality to keep a track record of all the changes made and who made what changes in the workbook. You can highlight edits, accept or reject changes, and maintain a history.

In this tutorial, we will see how you can track changes in Excel whether you’re auditing financial data or overseeing a team project using easy methods

Download our sample workbook to practice along the guide in real-time.

Table of Contents

Things to remember

Before we get into how you can track changes in Excel, let’s first go over the dos and don’ts

1. Not all changes made in Excel are tracked. Edits you make to cells are tracked and stored in the change history. But other edits like highlighting, aligning, formatting, formula recalculations, changing column/row width, height and hiding/unhiding are not stored
2. You cannot undo changes made in Excel. Its change history keeps a record of the changes made to the Excel file only, you can manually override those but not edit them.
3. You can only track changes in shared workbooks so when you turn off tracking changes in Excel, the workbook brings shared. However, some major Excel features are disabled when you use a shared workbook, sorting, filtering and formatting being some of them
4. By default, the change history is retained for 30 days by default. But the next time you open it, it will show the change history for 30 days plus the days that have passed in between. But once you close it, any changes older than 30 days from today will be gone. The good news is you can set the time for how long you want the change history to be retained.
5. You cannot track changes in a workbook containing tables or XML maps. If your workbook contains any of the two, remove them and then try using the track changes feature.

How to track changes in Excel

Now that we’ve seen all the things we need to know before tracking changes in Excel, let’s see how to get this thing started.

Enable Track Changes Feature

The Track changes feature of Excel is deprecated in newer versions, for Excel 2016, 2019 and for Microsoft 365. This is why you will only see the Protect group with Comment buttons on the Review tab.

But worry not, we have a solution to fix that too. Before we see hoe to use track changes, we will see how to enable it in Excel using the Customize ribbons options.

To enable it, follow the below steps.

Step 1) Open your workbook.

Step 2) Go to the File option and select Options at the bottom left corner.

Step 3) The Excel Options dialog box will appear on the screen.

Step 4) In the dialog box, select Customize Ribbon from the Categories.

Step 5) Under Choose Commands from, select All Commands.

Step 6) Under Main tab, scroll down and select Review tab.

Step 7) Select New Group from the bottom.

Step 8) It will appear under the Review tab.

Step 9) Right click it to rename it.

Step 10) The Rename dialog box will appear.

Step 11) In the dialog box, enter the name, Track changes.

Step 12) Press Ok.

Step 13) Go to the Commands list and select the following four commands and drag them to the Track changes group.

• Track Changes (Legacy)
• Compare and Merge Workbooks (Legacy)
• Share Workbook (Legacy)
• Protect Sharing (Legacy)

Step 14) Once done, press the Ok button and close the Excel options window.

Now, in the Excel Review tab you will have a new group with four different buttons.

Step 15) Click on Share Workbook to enable the Accept/Reject Changes options.

Step 16) Select the Use the old shared workbooks feature instead of the new co-authoring experience option.

Step 17) Check who has access to the workbook now and add or remove a user.

Step 18) Press Ok.

You should now be able to access all features of the Track changes section. Now, follow the below steps to turn on track changing in Excel.

Turn On Track Changes

By default, the tracking changes feature is turned off in Excel. To turn it on,

Step 1) Go to the Review tab and select the Track Changes button from the Changes group.

Step 2) From the drop-down menu that appears, select Highlight Changes

Step 3) The Highlight Changes dialogue box will appear on the screen.

Step 4) Check the Track changes while editing. This also shares your workbook. option.

Step 5) Under the Highlight which changes box, select the time and people whose changes you want to see in the Excel sheet – we chose all times and everyone.

Step 6) Check the Highlight changes on the screen option

Step 7) Press Ok.

Excel will now enable the Track Changes feature in your workbook and will highlight any changes that are made to your workbook from this moment onwards.

Accept or Reject Changes

Excel gives the creator of the workbook the option to accept or reject the changes made by the teammates in the workbook. But for that to work, you need to turn on this feature.

To do that,

Step 1) Go to the Review tab and select the Track Changes button from the Changes group.

Step 2) From the drop-down menu that appears, select Accept/Reject Changes.

Step 3) The Select Changes to Accept or Reject dialog box will appear on the screen

Step 4) Under Which changes, select when you want to accept or reject changes – we chose not yet reviewed.

Step 5) In the Who box, select who you want to accept or reject from – we chose Everyone.

Step 6) Leave the Where box empty as it will be in the workbook only.

Step 7) Press Ok.

Excel will show you the changes made in the workbook one by one, you can choose to accept or reject one by one or do it all altogether.

If there are no changes to accept or reject, Excel will show a prompt.

Easy, no?

Highlight Changes on Screen

The highlight changes on the screen are turned on when you turn on the Excel track changes feature in your workbook.

Excel highlights the row numbers and column letters in an almost maroon colour. The cells are highlighted in different colours depending on the person who edited the cells.

It colours the cell borders and a triangle at the top left corner of the cell. You can hover over this triangle to learn more about the person and time of change

Isn’t this fun? Try it now!

Set Timer for Duration of Keeping Change History

The default settings are to keep the change history for 30 days and delete any previous changes. If you want to keep the changes for a longer period, here’s how you can do it

Step 1) Go to the Review tab and select the Share Workbook button from the Changes section.

Step 2) The Share Workbook dialog box will appear on the screen.

Step 3) Select the Advanced tab from the dialog box.

Step 4) Under Track Changes, select the number of days you want to keep the change history.

Step 5) Press Ok.

And it’s done! The change history will not retain the changes made in the last 60 days until you delete or change the settings

Open Change History in a New Sheet

If you prefer seeing the changes made in your workbook on a new sheet instead of highlighting it in the data set, you can use the new sheet history listing feature of Excel

It creates a new Excel spreadsheet with a detailed log of all the changes made, the time and the names of people who made the changes.

Pro tip

Make sure your workbook is shared before you open the change history in a new sheet.

If not, go to the Review tab, select the Share workbook button from the Changes section and select Allow changes by more than one user at the same time option.

To do that,

Step 1) Go to the Review tab and select the Track Changes button from the Changes group.

Step 2) From the drop-down menu that appears, select Highlight Changes.

Step 3) The Highlight Changes dialog box will appear on the screen.

Step 4) If the track changes feature is turned on, only check the List changes on a new sheet option.

Step 5) Press Ok.

Tada! It’s done!

This will create a new worksheet named History Sheet with logs of all the changes made. It shows everything about the changes, when it was made, who made it, the change of data and more.

The table created has an Action Column which is filled with Won or Lost. Track changes feature sets Won, when changes made to the same cell by different users, are kept. If the changes are overridden then it fills the cell with Lost

Turn Off Track Changes

If you don’t want to track changes in Excel any longer, you can easily turn this feature off. The process is the same as turning it on.

To disable track changes in Excel,

Step 1) Go to the Review tab and select the Track Changes button from the Changes group.

Step 2) From the drop-down menu that appears, select Highlight Changes.

Step 3) The Highlight Changes dialog box will appear on the screen

Step 4) Uncheck the Track changes while editing. This also shares your workbook. option.

Done! The track changes feature has now been turned off. Wasn’t that easy?

Conclusion

In this guide, we saw how to track changes in Microsoft Excel. This included learning how to turn track changes on and off and highlighting changes made on screen.

We also saw how you can determine the duration you want to save change history for. The track changes feature also allows you to accept or reject the changes made by your coworkers in the workbook

You can also choose to view the change history in the same sheet or a different one and even track the last changed cell.

To learn more about Excel, check out the articles below.

How to Color Cells in Excel (Many Examples Explained)

How to Highlight Every Other Row in Excel: Step-by-Step (2024)

How to Duplicate a Sheet in Excel (3 Methods)

We hope you enjoy reading this article as much as we did crafting it!

Does dragging an Excel formula not work? Here’s why!

If I wake up tomorrow learning that the dragging formulas feature of Excel will no longer work – I’d prefer sleeping back assuming it was only a nightmare.

Half of the power of Excel is derived from its dynamics to drag the formula around the sheet in all four directions (above, down, right, or left. And they automatically adjust based on the changed cell references.

And it is just amazing. However, sometimes, when you try to drag a formula, you might not be able to capture the Fill Handle (it just doesn’t appear). Duh!

Or even if it does, it doesn’t automatically the formulas the way it should (scratching my head).

Why is that? Why does dragging an Excel formula not work in such times? The reasons (together with their solutions) come in the tutorial that follows.

Grab your free practice workbook for this tutorial here and continue reading till the end.

Table of Contents

Why dragging an Excel formula won’t work

There can be two potential reasons why your Excel sheet no longer allows you to drag a formula across rows and columns.

Here are these

Fill handle and Cell drag and drop disabled

I have two lists of numbers in Excel.

Now if I want to sum these up, normally, I’d write up the following formula in a first cell adjacent to one of these columns.

Once I have set up this formula, I will hover my cursor around the right bottom of this cell to find a small plus icon (the fill handle). As soon as I find it, I will capture it and drag it down until my adjacent list ends to have the formula for the remaining cells done.

But what is this? As I hover my cursor over the bottom right corner of this cell, I don’t find the fill handle

If this is the situation faced you, this is because, for some reason, the Fill handle and cell drag and drop for your Excel book have been disabled.

Here’s how you can enable it again.

Step 1) Go to the File tab.

Step 2) Go to Options.

Step 3) In the Options window, from the pane on the left, select Advanced.

Step 4) Search for the check box for “Enable fill handle and cell drag and drop”.

Step 5) Check this option.

Step 6) Press Okay.

Come back to your Excel sheet to find the fill handle back in place as you hover your cursor around the cell’s right bottom

Step 7) Try dragging the formula down now.

And you’ll see the formula is successfully dragged down (plus the sum for the next rows is rightly automatically calculated.

P.S: Formula dragging is too big of a blessing for Excel users

Calculation set to Manual

Another reason why dragging a formula in Excel might turn out to be an absolute failure is the inappropriate Calculation setting.

For example, when I drag the formula, to sum up the number in Cell A2 and B2 down the list, the formula is dragged but, this is what happens.

The formula is dragged down but, the numbers are the same. The formula didn’t recalculate for the remaining cells

This is because Excel didn’t automatically calculate the formula for the remaining rows as you dragged it down.

However, if you go to the Formulas tab > Calculation group > Click on the Calculate Sheet button now.

You’ll see the numbers for the dragged-down formulas will be updated. The only problem was that Excel hadn’t recalculated the sheet for the new formulas

Technically speaking, this is not a problem with the fill handle but the calculation setting of Excel. However, if you do not resolve it, it will make the formula dragging down the exercise entirely ineffective.

To help this problem:

Step 1) Go to the Formulas tab > Calculation group > Calculation options > Set the calculation mode to Automatic.

Now when you drag and drop formulas in Excel, the dragging option will work absolutely fine.

With the calculation mode set to automatic, Excel recalculates each formula every time you make any change to the sheet

Conclusion

So here we are. We have seen both, the potential reasons why dragging an Excel formula might not work.

If you had a similar issue in Microsoft Excel, I hope reading this tutorial helped you resolve it. To look into similar other Excel tutorials that explain how you can resolve commonly faced issues of Excel, check out the following Spreadsheeto Excel tips and tutorials.

• F4 shortcut not working in Excel? How to fix it!
• How to Fix “Formula Omits Adjacent Cells” Excel Error
• Excel Won’t Scroll? ALL 5 Fixes: Step-by-Step

How to Make a Flow Chart in Excel (Step-by-Step)

Flow charts are an excellent way to visually represent workflows, processes, and complex walkthroughs to reach a decision.

You can design flow charts in Excel using its wide array of tools and shapes. It allows detailing and customizing options to create professional-looking flow charts.

If you have a business process or a project timeline that you want to map out as a flow chart in Microsoft Excel, this tutorial is for you. In the sections that follow, I am going to show you how to create a flowchart in Excel, ensuring you can effectively communicate your ideas.

Table of Contents

What is a Flow Chart?

A flow chart is flowy (haha, just kidding).

It is a visual representation of a process, a decision tree, or a workflow. In the easiest words, a flow chart is a set of shapes (rectangle, oval, diamond or you name it) connected with arrows.

Each shape depicts a different activity, flow of control, or an activity. And believe me, we all know flow charts since secondary school. Let me quickly remind you what one looks like.

Flow charts are a great go-to tool for academics. Not only there, but you’ll also see it being used in fields like engineering, software development, and business (and where not) to simplify and clarify complex processes, enhance communication, and facilitate problem-solving and decision-making.

Components of Flow Chart

A flow chart primarily has the following components:

• Start/End (Terminator):

Represented by an oval shape, it indicates the beginning and the end of the process.

• Process:

A rectangle that shows a step in the flow chart process where an action is performed.

• Decision:

A diamond shape that depicts a point in the process where a decision must be made (mostly Yes or No) before moving to different paths within the flowchart based on the decision.

• Input/Output:

A parallelogram that shows where data is inputted into or outputted from the process.

• Flow Lines/Arrows:

Arrows or flow lines are the joining lines within a flow chart that direct the flow from one step to another, showing the sequence of actions.

• Connector:

Connectors are the small circles or sometimes labeled arrows that connect different parts of long and complex flow charts.

That’s all about the basic flow chart introduction. It’s now time that we try creating one by ourselves to see how it comes out.

Creating a flow chart in Excel using shapes

The basic and more appropriate (the manual way) of creating flow charts in Excel is by using the shapes library of Excel.

Excel has a huge library of shapes from which you can choose any shape you like (starting from basic circles to a little complex ones like a wave or a scroll etc.

You can draw these shapes and then connect them using arrows to create a flow chart of your own. Don’t know how to make a flowchart? Let me walk you through the end-to-end process of creating flow charts in Excel using shapes in this section.

We are making a flow chart for a product assembly workflow in a factory. Starting from receiving the raw material to shipping the product.

The most important step for me in creating a flow chart in Excel is to remove the gridlines from the Excel sheet.

This gives you a clean canvas to create just as you like.

Step 1) Go to the View tab > Show group > Uncheck the option for Gridlines.

Step 2) Go to the Insert tab > Shapes.

Step 3) Scroll down to the Flowchart shapes section.

Step 4) Choose the shape that you want to use in your flow chart.

I am beginning with inserting an oval.

Step 5) Click and drag on the worksheet to draw an oval as the start shape.

Step 6) Double-click on the oval to write within it. Label it as “Start”.

Step 7) Go to Inserting shapes again > select a Rectangle shape.

Step 8) Draw it towards the right of the Start shape. Label it “Receive Raw Materials”.

Step 9) Draw another rectangle next to the previous one and label it “Inspect Quality”.

Until here we are covering steps within the process hence we are using rectangles. Make sure to keep the size of shapes similar.

Pro Tip!

If your flow chart involves making multiple shapes where the size of each shape must be the same, you can either hold down the Ctrl key and click each shape you want to align or resize.

This way all shapes will be sized simultaneously the same.

Or you can create one shape, size it as you like, and then copy-paste it instead of creating new shapes.

Lastly, once the shapes are created, select all the shapes > go to the Shape Format tab on the Ribbon > Size group > Set uniform height and width.

All the shapes will be sized to the same numbers.

Step 10) Draw a diamond next and label it as “Quality Acceptable?”.

Using a diamond since this is a decision point.

Step 11) Add two more rectangles slightly above and below the diamond to label as “Reject and Return” and “Assemble Product”.

Step 12) Draw another rectangle next to “Assemble Product” and label it as “Test Product”.

Step 13) Add more steps to the process by adding rectangles and label them as “Package Product” and “Ship Product”.

The structure of the flow chart is now ready. All we need to do is add arrows to connect together all the shapes.

Step 14) Go to the Insert tab > Shapes > select the Arrow shape.

Step 15) Draw arrows connecting one shape to another indicating the flow of the process.

Here’s how it looks with all the arrows added.

Step 16) At each decision point, add text boxes near the connecting arrows by going to the Insert tab > Text box.

Step 17) Write “Yes” and “No” in each textbox to make sense out of the flow chart

And you have the flow chart ready.

You can change the colors of the boxes by selecting each shape and exploring options from the Format Shape tab.

Similarly, you can play around the fonts and styles of the text inside each shape.

In short, with Excel, there are no limits to how you can design your flow chart. Bring out the artist inside you.

Pro Tip!

To lock the whole chart in place so that shapes don’t fall out as you move any one of multiple of them, press down the control key and select all the shapes, go to the Shape Format tab > Arrange group > Group button.

All the shapes will be grouped together and as you move any one of them, the whole flow chart will move (as if knitted together).

Creating a flow chart in Excel using Smart Art

Creating the same chart as above can be simpler and quicker if you use Smart Art.

Let me show you how.

Step 1) Go to the Insert tab > Illustrations group > Smart Art.

Step 2) Choose the smart art graphic that suits your needs by exploring the different types in the left pane.

We are going with a style from the hierarchy as we have decision points and multiple options in our chart.

This is how it will initially look.

Step 3) Once inserted, you can add more shapes to it by right-clicking on any shape, choosing to add the shape before or after, below or above.

Step 4) Similarly, right-click on any shape > click on Change Shape to change the shapes within the flow chart.

Step 5) Keep adding shapes and changing them as and where you like.

SmartArt automatically connects shapes with arrows, but you can adjust connections by selecting a shape and using the tools provided in the SmartArt Tools tabs.

Step 6) Add text in each shape as desired.

Step 7) You can use the small circles on the borders of the flow chart to drag and resize the whole chart. Or you can resize individual shapes as you want.

Step 8) Once made, you can then go to the Smart Art Design tab and choose different designs and color palettes for the chart as you like.

Here’s how my final chart looks.

And, if you do not want the shapes to be connected as a flow chart after it is made, select the chart, and go to the SmartArt Design tab > Reset > Convert to Shapes button.

The chart will be converted into individual shapes (like in the above section). So, moving one shape will not affect the rest.

Using SmartArt, you can create detailed and visually appealing flow charts in Excel in no time.

Conclusion

Excel is an excellent tool when it comes to creating flow charts to visualize processes and workflows.

Just as we’ve seen in the above tutorial, there are many shapes and connecting tools in Excel that you can use to design clear and professional flow charts.

The best part about flow charts is that they enhance understanding and decision-making. Especially with all the flexibility that Excel offers for easy customization of flowcharts, you have the whole sky to create charts just how you like.

• If learning about creating flow charts in Excel is something you enjoy, must read the following Excel tutorials by Spreadsheeto.
• How to Make a Pivot Chart in Excel (Step-by-Step)
• How to Create a Waterfall Chart in Excel (Step-by-Step)
• List of All Excel Charts & How to Use Them (2024 Tutorial)

How to Calculate and do an ANOVA Test in Excel

The ANOVA test of Microsoft Excel is a great statistical tool that helps you find significant differences between two or more data groups.

Never mind if you’re hearing the word ANOVA for the first time – the word might be alien but the concept is something we need and apply in our daily lives

To run an ANOVA test in Excel, you’d need the Data Analysis Toolpak facility which allows you to perform complex statistical analyses like a breeze. To see how to do that, grab your free practice workbook for this guide now, and come along, I will walk you through the steps to calculate and do an ANOVA test in Excel.

Table of Contents

What is ANOVA

ANOVA stands for Analysis of Variance. This statistical test of Excel is used to check if the means of three or more independent data groups are statistically and significantly different, or not.

It helps to determine if the differences between the means of these groups represent actual differences (between the means of population) or if they have occurred by chance

ANOVA test is the appropriate choice to make when you want to compare the variance between three or more groups. For example, if you’re going to compare the sample data of student heights from three different schools.

There are two kinds of ANOVA tests that you can perform in Excel.

• One-way ANOVA (or One-variable ANOVA test):

The One-way ANOVA test is used when you have one independent variable within multiple groups, and you want to see if this differs from the independent variable.

For example, if we compare the test scores of students from three different Schools (Schools X, Y, and Z), the school is and independent variable, and the test scores are the dependent variable (dependent on how good each school is).

Performing a one-way ANOVA test on the sample or scores of these students will help you know if the performance of each school is significantly different from the other (analysis of variance in scores)

• Two-way ANOVA (or ANOVA: two-factor test):

The Two-way ANOVA test is to be used when you have two independent variables, and you want to test how these variables interact and cause differences to a dependent variable.

Talking of the same scores and school example above. If you want to see how three different schools (first independent variable) and their faculty (second independent variable) affect the test scores of students, you can run the 2-way ANOVA analysis

One-way ANOVA test in Excel

Performing a one-way or one-variable Anova test in Excel is quite straightforward. Let me show it to you through an example here.

Here I have data on test scores from three different schools, say School X, School Y, and School Z

To run an analysis of variance to see if the mean of test scores from these three schools is significantly different, follow the steps below.

ANOVA test is a part of the Data Analysis Toolkit of Excel that won’t be there on your ribbon by default. To add it to the Excel ribbon:

Step 1) Go to the File tab.

Step 2) Go to Excel Options.

Step 3) From the pane on the left, click on Add-ins.

Step 4) From the bottom of the window, select Excel Add-ins and click on Go.

Step 5) Check the option for Analysis ToolPak and click Ok.

The Analysis group will be added to the Data tab

Step 6) Go to the Data tab > Analysis Group > Data Analysis.

Step 7) Select Anova Single Factor and click on Okay.

Step 8) As the input range, select the data on which the ANOVA test is to be run (including the headers).

Step 9) Our data is grouped as three different columns so I am checking ‘Columns’ as Grouped By.

Step 10) Check labels in the first row as our data has labels/headers in the first row.

Step 11) The Alpha Value is set to 0.05 (we’ll let it be that for now).

Step 12) For the output range, define the cell range where you want the results of the ANOVA test populated.

Step 13) All details are done, now click on Okay.

Excel will run the one-way ANOVA test for you and return the results as follows.

Running a one-way Anova test is this simple in Excel.

The results might seem like too much to digest now. But keep your calm, we will break them down to understand what it means in the next section

Interpreting the results of the One-way ANOVA test in Excel

Here are the ANOVA test results for the one-way ANOVA test that we ran for the test scores of three different schools above.

What do they tell you about the dataset? Let’s see that here

The Summary Table

• Count: This simply counts the number of data points (test scores) in each group (school).
• Sum: The sum tells the total of all test scores in each group.
• Average: The average (mean) is calculated by dividing the sum of the test scores by the count of students.
• Variance: Variance tells how much the test scores of each school deviate from the mean. The formula for variance is the sum of squared differences between each square and the group mean.

Stats from the Summary table are easy to decode. More like some basic statistical figures for your dataset put together.

ANOVA Table

• Source of Variation: The variation in data can take two types. It can be between the different data groups and within the same data group.
• Between Groups: Between groups stats represent the variation in means of different groups due to the independent variable (as discussed above).
• SS (Sum of Squares): Before I explain to you what it represents, let me show you how is it calculated

1. Find the mean for each group which is already calculated in the Summary table as 87.3, 78, and 91.3.
2. Calculate the overall mean for all the three groups. It is 85.53.
3. Now calculate the sum of squares for each group by using the following formula:

This will give you 31.21, 567.51, and 332.54 for School A, School B, and School C, respectively.

Sum all these numbers up to get the sum of squares as 931.267.

The sum of squares is the total variation between the group means.

• Df (Degrees of Freedom): It is simply the number of groups less 1. We are analyzing three independent groups so the df is 2 (3 less 1).

The degree of freedom tells the number of independent comparisons that you can make between the groups.

This represents the number of independent comparisons you can make between the groups

• MS (Mean Square): Just like the Sum of Squares is the total variation between all three groups, the Mean Square represents the average variation between the three groups.

To calculate it, you divide the SS (Sum of Squares that is 931.467) by the df (Degrees of Freedom that is 2).

• F (F-statistic):

Let me show you the formula for the F-statistic and half the story behind it will automatically begin to make sense.

The F-statistic is a measure that compares the variance between the group means and the within the groups. The higher this number is, the more these means are significantly different.

• P-value:

The P-value comes from the F-distribution table based on the F-statistic and the df

The P-value is indicative of the probability of the null hypothesis (that the means of all the groups is the same) being true. A P-value equal to or less than 0.05 rejects the null hypothesis. This might mean that at least one of the groups’ mean is significantly different.

• F crit (F critical value):

F-crit is the critical value from the F-distribution table for a given level of significance (that we have and is usually set as 5%) and the degrees of freedom

The same calculations (based on the same sense) continue within groups.

Bottom Line for our ANOVA test

For the ANOVA test we’ve just run above, we have the following key stats.

• F-value: 71.35
• P-value: 1.66961E-11 (which is approximately 0.0000000000167)
• F critical value (F crit): 3.354131

Since the P-value is way smaller than 0.05 (it’s not even 0.01), we can safely reject the null hypothesis to be true (that all the group means are the same).

Talking of the F-value, 71.35 is significantly greater than 3.354 which further tells that the group means are significantly different from each other

This ANOVA results show that the mean test scores for students from all three schools, X, Y, and Z are very different. The independent variable (schools) makes a great difference to the dependent variable (test scores).

That is how the ANOVA table helps hypothesis testing and summarizes and pictures the total variability in the given datasets. It shows variability between different groups and within groups.

Conclusion

Following this step-by-step guide, you can run one-way ANOVA tests in Excel like a pro. ANOVA tests help you determine if the differences in the means of three or more independent groups are significant, or not

Running an ANOVA test in Excel might not be as big of a challenge as is to interpret the results of this test. Rightly understanding the results of the ANOA test such as the P-value and F-statistic in its right sense will help you make informed decisions based on the results of your data.

We have covered all of this knowledge in the guide above. Hope you enjoyed reading this guide and if you did, do not forget to check out other similar Excel tutorials by Spreadsheeto here.

• How to Add Line of Best Fit in Excel (Easy Method)
• How to Find Coefficient of Variation in Excel
• How to Perform Linear Interpolation in Excel (Easy)

How to do Sensitivity Analysis in Excel (Easily)

Excel is a great analytical tool and if you haven’t yet used it for sensitivity analysis, you’re missing out on a great feature of Microsoft Excel.

The sensitivity analysis tool of Excel allows you to see how variation in one or more input variables may impact the output of the model.

You can systematically list down a range of input variables and try them all to see how sensitive the output to this input variation is. Helps you identify the critical factors so you can make informed decisions.

Projecting it to the world of finance, sensitivity analysis has a broad scope of application. In this guide, I will take you through two methods to do sensitivity analysis in Excel like a breeze.

Grab your free practice workbook for this guide now and tag along with me.

Table of Contents

What is Sensitivity Analysis

Whenever you’re budgeting or forecasting data under a model, you are taking a handful (or more) of assumptions and relying on their outcome.

Since these are just your expectations that might go unrealistically well or horribly wrong – you can only hope and project but never be sure about them. That is where and why you perform a sensitivity analysis (also called stress testing).

We stress some of the critical assumptions of our data model to see how worse the results can get with different scales of changes to our assumptions. This is to see the results your financial model would yield under different circumstances.

Sensitivity analysis is all about analyzing how sensitive are results of your data model are to different variable inputs.

We are going to cover two comprehensive examples of performing sensitivity analysis in Excel just now. Let’s jump down straight to understand it better.

One variable sensitivity analysis in Excel

To understand how single variable sensitivity analysis is performed in Excel, let me show you this example. I have $10,000 that I want to invest for 5 years.

Different instruments and banks offer different interest rates. I want to see what the final lump sum amount that I will draw from the instrument after 5 years (principal and interest included).

Here’s how we do it.

For an exemplary interest rate of 6%, the future value of my investment would grow to $13,382.

Now to see how different would be the future value of my investment under different interest rates, I am running a sensitivity analysis through the following steps.

Step 1) Make a two-column table (we call it a one variable data table).

Step 2) In the first column, populate the different interest rates for which you want to calculate the future value.

Step 3) In the first row of this table, write the exemplary interest rate i.e., 6%.

Step 4) In the column next to it, calculate the future value of your investment using 6% as the interest rate and reference all arguments from the original data table.

Pro Tip!

The FV function has the following arguments:

• Rate: This is the interest rate per period. Setting it to 6% from the original data table that contains the investment and tenure details to be used too.
• Nper: The total number of payment periods/interest compounding periods. Set it to 5 years which is the tenure of investment.
• Pmt: Omit this argument as there are no periodical payments.
• [pv]: As the present value for this investment, refer to the initial investment of $10,000 with a minus sign. The minus sign will make it look like an outflow so the future value of investment will be a positive number (an inflow).

Step 5) This will calculate the future value for this investment at 6% in the first row of the table.

Step 6) Now select the entire range including the first row where we have already calculated the investment’s FV.

Step 7) Go to the Data tab > Forecast group > What-if Analysis > Data Table.

Step 8) From the Data Table dialog box that opens up next, refer to the interest rate (6%) from the original data table as the Column Input Value.

Step 9) Click on Okay.

The Data table will calculate the future investment value for each of the interest rates in the first column using the same input values and formula as has been used to calculate the FV in the first row of the table.

This shows how sensitive is the future value of my investment to the interest rate. Increasing the interest rate by each one percentage leads to how much change in the FV of investment.

This is how you can instantly see how changes in input variables will affect the final results of any data model. Hope you found it interesting.

Two variables sensitivity analysis in Excel

If you liked doing a single-variable sensitivity analysis above, you’ll love the two-variable sensitivity analysis that you can perform in Excel.

In this example, I have a short Profit & Loss statement for my business. This is calculated using a set level of quantity sold and price per unit which is 1000 units sold at the rate of $50 per unit.

This yields me a net profit of $25,000.

To see how I can better optimize my business, I want to see the net profit my business makes at different volumes and prices of sold units.

We will again leverage the What-If Analysis tool of Excel to see how sensitive my business to the volume and price levels of the units is sold.

Step 1) Create a two-dimensional table with different volumes on one side (within a column) and different prices on the other side (as a row).

Step 2) In the first cell of the two-dimensional table, write in the same formula as used in the P&L statement to calculate the net profit for the volume and price in the original dataset i.e., for 1000 units and price of $50 per unit.

Make sure to refer to cells from the original data table.

Step 3) Select this entire range (two-dimensional table) including the first cell with the pre-defined volume and price.

Step 4) Go to the Data tab > Forecast Group > What-if Analysis > Data Table.

Step 5) As the Row Input Value, refer to the Volume from the original data table as we have written different volume levels within the row.

Step 6) As the Column Input Value, refer to the Price from the original data table as we have written different price levels within the column.

Step 7) Press Okay to have the net profit calculated for each combination of price and units’ level within the matrix.

Wufh! This is insane.

Had you tried doing the manually (calculating the revenue and cost of sales and then the net profit) for like so many combinations of volume and price levels, it would have taken you hours to populate this table?

And let alone the chances of manual errors with such voluminous calculations. Populating them all together in a table would have been another task.

So, speaking of the What-if Analysis tool of Excel, it simplified the job for us multiplefold. It’s about pulling together different levels of inputs. setting up a formula, designating the input variables, and then clicking the Okay button.

Excel handles the rest.

Pro Tip!

The What-if Analysis tool has other tools that are equally helpful:

• Goal Seek: It is used when you have a specific target outcome and need to find the necessary input to achieve it.
• Scenario Analysis / Scenario Manager: This is used when you want to explore and compare multiple sets of assumptions or input values to see how they affect your model’s results.

While they are not exactly meant for sensitivity analysis, both these tools are just too good for making informed decisions based on different possibilities and outcomes.

Conclusion

In this guide, we have unveiled how to use sensitivity analysis in of Excel.

It helps you understand the dynamics of your data model and the influence of each input variable on the output. Leveraging the sensitivity analysis tool of Excel, you can not only understand the critical elements of your dataset, but you can make informed decisions that are resilient to change.

• Practicing the one-variable and two-variable examples explained above, you can easily master sensitivity analysis in Excel. And after you’ve done that, make sure to check out the following Excel tutorials by Spreadsheeto.
• Quick Analysis Tool in Excel: Full Tutorial (2024)
• How to Add the Data Analysis Toolpak in Excel (2024)
• How to Add Line of Best Fit in Excel (Easy Method)

How to Tab Down in Excel (Must-Know Method)

In today’s data-driven world, proficiency in Excel is an indispensable skill for nearly everybody.

You can enhance your Excel efficiency many folds by understanding the art of navigation within Excel. This will enable you to work smarter, and not harder.

Especially if you want to move swiftly and frequently across cells in all directions, mark this guide as a must-read for yourself

In the article that follows, I am going to show you how you can use the Tab key (there on the left of your keyboard) to move between cells. And other advanced methods to navigate between your Excel sheet to streamline your workflow.

Table of Contents

What is the tab key and how does it work in Excel

Not having to rely on the mouse can speed up your work significantly. Especially in Excel, when all you need are the right keyboard shortcuts

Talking of keyboard shortcuts, we’ve seen the Tab key on our keyboards.

It is placed on the left of your keyboard.

Generally, if you are working with cells in Excel, to move between cells, you use the arrow keys.

Using the arrow keys, you can move in all four directions around the selected cell. Similar to the arrow keys, we have two other keys that can help you move around in Excel

• Tab key: The Tab key will move you to the next column. In other words, towards the cell on the right.
• Tab key + Shift key: Pressing the Tab key together with the Shift key will navigate you to the previous column (towards the left cell).
• Enter key: The Enter key will move you to the next row. Or to the next cell on the bottom.
• Enter key + Shift key: Press Enter + Shift key combination to navigate you to the previous row (upwards).

This is the basic use of the Tab and Enter keys in Excel.

In a nutshell, the tab key is used for horizontal movement in Excel (left or right). Whereas, the Enter key is used for vertical movement within Excel (upwards or downwards).

How to Indent in Excel

Not in Excel but generally if you use the Tab key, you’d see it is used to indent a word or a sentence (is pressed by placing the cursor behind the word). Or, otherwise to add an extended space.

However, note that the Tab key just doesn’t work like that in Excel.

If you want to indent the text added within a cell in Excel or add an extended space in Excel, the Tab key won’t help you with that

As soon as you press the tab key, it will move you to the next cell instead.

Although the Tab key doesn’t work to indent the content of cells in Excel, this doesn’t mean you cannot indent in Excel.

Excel offers a distinguished tool to support indentation within cells. Check this out here.

Step 1) Select the cell containing the content that you want to indent.

Step 2) Go to the Home tab > Alignment group > Indentation button.

Step 3) Pressing the right indent button will indent the contents of the cell towards the right once.

So, you can click it as many times as you want the text indented.

This works exactly how the tab button would work in other apps to indent text

How to Add a Line Break in Excel

When you input data in cells in Excel, you’d often want the text to be split into two lines.

Normally, in other text-based applications, to add a line break and start a new line, we press the “Enter” key. However, as is the case with the Tab key, pressing the Enter key in Excel won’t add a line break.

Instead, it will navigate you to the next cell towards the right

Fortunately, it’s not like you cannot add a line break in Excel in its entirety. You can add a line break in Excel by following these steps.

Step 1) Activate the cell where you want to add a line break.

Step 2) Take your cursor to the point where the line break is to be added.

Step 3) Press the Alt key + Enter key.

Step 4) This will add a line break at this point.

The Alt key + Enter key shortcut works to insert line breaks within a cell in Excel.

Moving through an Excel book

This section is a treasure for keyboard wizards who want to navigate around through their Excel sheet and workbook (as a whole) in a jiffy

In addition to the Tab key shortcuts discussed above, here are some very handy Excel keyboard shortcuts to move around a sheet:

• Arrow keys: Move selection cell in the direction of the arrow (up, down, left, or right) for once.

• Ctrl + Arrow keys: Jumps to the edge of the data region in the direction of the arrow (bypassing any empty cells in between).

If there is no data in the previous or next cells at all, it jumps to the first cell (ctrl +left arrow key) or last cell (ctrl + right arrow key) in the row. Or the column (with ctrl and up or down arrow key).

This helps you quickly navigate across large data sets or sheets. For example, pressing Ctrl + Right Arrow will move the cell selection to the last filled cell in that row. Or to the last cell of that row if all the next cells are empty

• Ctrl + Pg Up key: Takes you to the previous sheet in the workbook (before the currently active sheet).

• Ctrl + Pg Dn key: Takes you to the next sheet in the workbook (after the currently active sheet).

Try out these shortcuts to sail around your worksheets seamlessly.

Conclusion

Efficient Excel navigation is a very impressive (and essential skill) skill. It makes you look like a pro, if anything, and speeds up your work insanely.

By understanding and utilizing the right shortcuts and techniques, you can achieve better productivity and enhanced efficiency in your Microsoft Excel jobs. In the above tutorial, we’ve seen how you can use the Tab key to move around cells in your Excel sheet

Also, we have uncovered ways how you can indent text within a cell and, line breaks to it and other handy shortcuts to navigate through your Excel workbook.

Since the key to proficiency in Excel is practicing, make sure you keep doing that and check out the following Excel tutorials by Spreadsheeto to continue learning.

• F4 shortcut not working in Excel? How to fix it!
• How to Refresh a Pivot Table in Excel (Step-by-Step)
• How to Unhide Sheets in Excel: Step-by-Step (Unhide Tabs)

How to Separate All Sorts of Data Using Excel Formulas

When it comes to data management and organization, splitting text is the one task that’s inevitable.

It can range from splitting numbers from text to parsing out data based on specific delimiters. Luckily, Excel offers a variety of methods you can use to separate different types of data

Knowing how to leverage Excel formulas to achieve the same result as built-in features adds a plus one to your skill set and makes your workflow more efficient and streamlined.

In this tutorial, we will see different formulas you can use to split names and extract addresses from your database. Download our sample workbook here to practice along the guide.

Table of Contents

Separate first and last name

Separating first and last names in a data set is pretty easy. We will use the LEFT, RIGHT, and FIND functions for this purpose. Let’s see how to separate names in a database below

We will use the following sample data set.

To separate the first name,

Step 1) Select cell B1 and type in your First Name.

Step 2) In cell B2, type the following formula:

Step 3) Press Enter.

Step 4) Double-click the Fill Handle to copy the formula down to the remaining cells.

All first names from the data set appear in the selected column

To extract the last name,

Step 5) Select cell C1 and type in Last Name.

Step 6) In cell C2, type the following formula:

Step 7) Press Enter.

Step 8) Double-click the Fill Handle to copy the formula down to the remaining cells.

The last names in your data set will appear in a new column.

Wasn’t that easy? Try with your data set now!

Separate data based on delimiter

You can combine different functions in Excel to achieve a certain purpose. For separating data based on a delimiter, we will use the TEXTSPLIT function. Let’s see how it’s done below!

We will use the following sample data set where addresses are separated by commas

To do that,

Step 1) Make sure the columns next to your current selection are empty – if not – insert columns corresponding to the number of elements you want to separate.

Step 2) Label each column according to the data to be filled in it.

Step 3) In cell B2, type in the following formula:

Step 4) Press Enter.

The formula will spill output to the adjacent columns. Double-click the fill handle to copy the formula down to the remaining cells

For older versions, use the following formulas:

1. Name:

       2. Extension:

       3. Mail:

       4. Copy the formula down the remaining cells using Fill Handle.

And tada! All the contents of column A are separated by the selected delimiter.

Wasn’t that easy?

Separate text and numbers

Excel lets you combine formulas to separate text and numbers. There are two different ways of achieving the same result. Let’s see how they work below.

We will use the following sample data set.

To do that,

Step 1) Select cell B2.

Step 2) Type in the following formulas:

Step 3) Press Enter.

This formula will return the text part of the dataset in the adjacent empty column

To extract the numbers in a separate column,

Step 4) Type in the following formula in cell C2.

Step 5) Press Enter.

The numbers in the data set will be separated

However, if you are using an older version of MS Excel, you can use the following formula.

Step 1) Select an empty cell next to the data set.

Step 2) Type the following formula to separate text:

Step 3) Press Enter.

This will separate the text.

To separate numbers,

Step 4) Use the following formula:

Step 5) Press Enter.

The numbers in the data set will be separated.

And just like that, all your data has been separated. How cool is that?

Separate special characters

Separating data sets based on special characters is pretty simple. Let’s see how to do that below.

We have the following sample data set.

To do that,

Step 1) Select an empty cell next to your dataset.

Step 2) Type in the following formula:

Step 3) Press Enter.

The formula will spill the separated data set upon encountering the special character

To get the same results in MS Excel 2021 or older versions,

Step 1) Select the cell next to the data set.

Step 2) Type in the following formula:

Step 3) Press Enter.

This will spill the data before the special character in a new column.

To separate the data after the special character,

Step 4) Select cell C2.

Step 5) Type the following formula:

Step 6) Press Enter.

The data after the special character will be separated.

Both the formulas are generic and work for all special characters. This way you don’t need to specify a character each time in the formula.

How easy was that? Try it now!

Separate date and time

Similar to other data types above, we can also separate date and time in a data set. Let’s see how to do that below.

We have the following sample data set.

To do that,

Step 1) Select cell B2.

Step 2) Type in the following formula:

Step 3) Press Enter.

The date and time from the data set will be separated into different columns

To use a different approach,

Step 1) Select a cell next to your data set.

Step 2) Type in the following formula:

Step 3) Press Enter.

This will extract the date from the data set.

To extract the date,

Step 4) Select cell C2.

Step 5) Type in the following formula:

Step 6) Press Enter.

The data in the data set will be separated into different columns.

Cool, no?

Conclusion

In this guide, we saw how to separate data of all sorts in Excel. We saw how to extract numbers from text, split names, and more

We used different formulas like LEFT, RIGHT, MID, FIND, etc. It might take you a little time working with the first formula but once you get a hold of it, you’ll be able to tackle any data separation task.

Excel’s versatility ensures you can adapt any of these methods to suit your specific needs and make accurate and insightful data analysis easier.

To learn more about data separation in Excel, give the following articles a read:

How to Split Text in Formula (TEXTSPLIT Function)

How to Separate Names in Excel (Split First and Last)

How to Separate Date and Time in Excel (Example)

We hope you enjoy reading this as much as we did crafting this!

How to Fix Problems With Excel Formula References

Excel formulas are powerful tools that allow you to analyze data, automate large calculations and perform a variety of different tasks.

However, sometimes a small mistake in the formula can lead to errors like formula references that can lead to incorrect results or unexpected behaviour within the sheet

Knowing how to fix these problems like relative or absolute references, broken links or circular references is crucial for ensuring accuracy and precision in your data.

In this guide, we will see how to fix Excel formula reference problems and more. Download our sample workbook here to practice along the guide

Table of Contents

Use Excel features

The easiest way to find and fix Excel errors is to use the Find and Replace feature. It locates all the #REF errors in the worksheet and lets you remove them as you like. Let’s see how to do that below.

We have the following sample data set.

To find and fix the error,

Step 1) Open the workbook.

Step 2) Press CTRL + F to open the Find and Replace dialogue box.

Step 3) In the Find box, type the error you are encountering – for instance #REF.

Step 4) Leave the Replace box empty and click on Replace All.

This will replace all errors in the workbook with blank cells. You can make your calculations freely.

How cool is that?

Check each worksheet

If you have multiple worksheets opened in your workbook, this is the first thing you need to do. Isolate each worksheet so you can figure out where the actual problem lies.

This method is the easiest way to troubleshoot the error when working on a large scale. In case, the error isn’t fixed, you can create a backup of the workbook and retrieve the data later.

To do that,

Step 1) Copy the data in the problematic sheet.

Step 2) Go to the File tab and select Blank Workbook.

Step 3) Paste the contents of the sheet in the new workbook.

Step 4) Press CTRL + S to save the new workbook.

If Excel shows the formula reference error for this worksheet, we know it’s the one with errors.

If not, repeat the same process for the remaining worksheets until you find the faulty one

Recheck external links

Once you locate the faulty file, now it’s time to remove the errors. Let’s start the error-finding process by checking the easiest of all off the list

If your file contains external links, you need to make sure all links are authentic and lead to the correct website and none of them contain any errors.

To do that,

Step 1) Go to the Data tab on the ribbon and select the Edit Links option from the Queries & Connections section.

Check for any links that might contain errors. If you spot any, break or remove them.

There is a good chance this might not be the error but it will minus one option from the list of problem so we can try the others.

Recheck Excel formulas

The most important fix of all is to recheck each formula and pinpoint where the error lies. This requires troubleshooting the formula in your Excel sheet and making all calculations again.

However, if you have multiple reference errors in a sheet or different sheets in a single workbook, try using the Error Checking tool. It scans all the formulas in the workbook in one go.

To do that,

Step 1) Go to the Formulas tab and select the Error Checking option.

This will run a quick scan through the entire workbook to look for any errors in formulas. If there’s any error, it will display the issue on the screen.

However, if there is no error in the formulas, the following prompt will pop up.

If you think this file might be the source of the problem, try saving it again – the error might appear. If it doesn’t, move on to other files or try the next method

Check all pivot tables

If your workbook has pivot tables, there is a high chance it might be causing the problem. The smallest mistake can lead to a reference error in the sheet

To solve the problem,

Step 1) Click on your pivot table to activate it.

Step 2) From the ribbon, go to the PivotTable Analyze tab.

Step 3) Select the Change Data Source option from the Data section.

Step 4) The Change Data Source dialog box will take you to the source data.

Check every formula in the data set for any errors and make changes if required.

This should fix the problem – but if it doesn’t, we have other solutions prepared

Review charts

If the formula reference error persists, you need to check your charts. This is often the most time-consuming and tiring method – especially if you have multiple charts in a single tab.

A common error in this case is when the linked data series is moved or deleted. Going over the source data and series can fix the problem in no time

Sometimes it is not possible to check the entire chart from A-Z, in such cases, we look at specific points of the chart for errors. Let’s see the locations this includes.

1. Look for errors in the source data of the chart.
2. Check the chart’s Secondary axis for any sort of loopholes.
3. Check the Chart Title, Data Labels, and Axis Labels for any errors in the graph.
4. Right-click the chart and choose Select Data Source. Look for errors in the Horizontal Axis Formula and in the Legend entries (Series).

This should fix the faults of the charts – save the chart to see if it works. If it doesn’t, copy the data and paste it into a new worksheet as a last resort and save it.

If the new file saves without any error message, the error has been resolved and you can delete the previous chart

Check name managers

If the error is still not resolved, try checking the name manager. It refers to named ranges in your worksheet that might contain formulas with errors or incorrect references.

To do that,

Step 1) Go to the Formulas tab and select the Name Manager option from the Name Manager section.

Step 2) Scroll through the dialogue box and check if any formula in the Named Range has an error.

Step 3) If the error exists, delete the named range. Repeat the same for the remaining ranges.

Step 4) Once done with all the ranges, save the file.

If Excel doesn’t display any prompt upon file saving, all the errors have been resolved.

The process can be a bit tiring but it’s sure to give results

Conclusion

In this tutorial, we saw some common Microsoft Excel formula errors and how to fix them. By understanding these common issues, you can prevent errors in your data

We saw how you can find formula reference errors in your worksheet and fix them. This included using the Find and Replace feature, rechecking Excel formulas, and checking worksheets.

We also saw how to fix errors by reviewing charts, checking name managers, and pivot tables. Mastering these strategies can help troubleshoot and fix formula problems confidently.

To learn more about Excel formulas and errors, give the following articles a read:

“Reference isn’t Valid” Excel Error: How to Fix (2024)

How to Find Circular References in Excel (+ How to Fix Them)

How to Fix the #Ref Error in Excel (Step-by-Step)

We hope you enjoyed reading this article as much as we did crafting it!

How to Create a Fillable Form in Excel (Data Entry)

Thinking of fillable forms, the first thing that comes to mind is probably an editable PDF or a Google Form.

Gotcha! You can also create fillable forms in Microsoft Excel and believe me, it is a very effective way to collect and manage data. Whether you’re putting together a customer feedback form or a research survey form, this tutorial will help you get through.

We are going to touch user-friendly options like checkboxes and drop-downs and smart options like data validation to create forms that are not only visually appealing but effective.

Here’s your free practice workbook for this guide that you can download and tag with us along the way

Table of Contents

What are Excel forms?

An Excel form is a dialogue box that asks you to populate pre-defined fields up to a certain length.

It looks like below

It is used to collect data. As you can see in the image above, it offers multiple fields that you can populate and create multiple rows in your data. Helps you to organize information easily and without errors.

More about how you create it in Excel and how it works comes in the sections below.

Create a Fillable Form in Excel

If you’re searching for the option to add a form to Excel somewhere on the ribbon, there’s a 90% chance you’ll not find it there.

That’s because the form is not available in Excel, by default. But the good news, it’s not even difficult to add

Follow these simple steps to add the option to create a form in Excel.

Step 1) Go to the File tab > Excel Options.

Step 2) From the pane on the left, click on Customize Ribbon.

Step 3) Choose All Commands.

Step 4) Scroll down the list of commands to find the command Form.

Step 5) From the list of tabs on the right, choose the tab where you want this command added (for example I am choosing the Home tab).

Step 6) Click on New Group to create a custom group under the Home tab.

Step 7) Click on Add to add the command to create a Form in the Home tab like here.

Now that we have the Form button added to the Home tab, it’s time we go on, and create a form in Excel

I am going to take the example of a customer feedback form (let’s assume for a restaurant). Let’s say each customer has to fill this out before they leave after they’ve had their meal.

And here’s how we do it.

Step 1) Launch your Excel sheet.

Step 2) Make columns for the fields you want to include in the feedback form.

I need to populate the survey form for the customer’s views on their visit to the restaurant, so my relevant fields include the customer’s name, contact number, frequency of visits, and feedback on different aspects of the service

I am going to populate all these fields as columns with column headers in my Excel sheet as follows.

Step 3) Select the cell range for the form (including the column headers).

Step 4) Go to the Insert tab > Tables > Table to convert it into a table.

Step 5) Press okay to convert the specified range into a table.

Step 6) Now click anywhere within the table.

Step 7) Go to the Home tab > your custom group > Form button.

You will see the customized data entry form dialog box pop up for our relevant fields

Step 8) Type whatever you want to within each of the fields like here.

Step 9) Click on close to close this data entry form and see the data populated in your Excel table.

Step 10) Or, if you want to add further data rows, click on New and add another row of information.

Doing so you can add as many rows of information as you want.

This is simple. You can navigate your customers to this feedback form to capture their feedback, seamlessly and without any errors

Here is more about the buttons you see on the form.

• New: By clicking on the new button, you can start populating a new entry in the form by automatically saving the previous one.
• Delete: The delete button deletes the current field entries from the form.
• Restore: The Restore button restores the immediately previous position of the field entries.
• Find Previous / Find Next: Use both these buttons to toggle to the previous and the next information entry.
• Criteria: You can define criteria for the people filling out the form to follow/take a cue from before they begin filling it.

By clicking on the Criteria button, you can populate the field entries as you do normally. However, this will not be stored as an entry in the Excel table. You can go back to filling in the form entries by clicking on the Form button and continue recording entries once you’ve set up the criteria.

And this is how you use the Form tool of Excel to create a fillable form.

But what if you want to restrict some fields? For example, we only want the customers to enter their contact number in the field of contact number and not any text (like not available or private number, etc.)

You can apply data validation rules to the table below.

Step 1) Select the column where the contact numbers are to be populated.

Step 2) Go to the Data tab > Data tools group > Data Validation.

Step 3) Set the restriction to the whole number only.

Step 4) Set the rule to greater than 0 (every contact number will be greater than 0, hopefully).

Step 5) Click ok.

Now, if you try entering anything other than whole numbers in the field for contact numbers within the Form dialog box, you get the following error

This way you can restrict the data entered by the users in the form to a desired input type.

Similarly, you can create a drop-down list by again going to Data validation tools and setting up the rule for a list of values that you can then stipulate yourself.

Say I want the users to give feedback on the Food, Ambience, and everything else as Excellent, Good, Satisfactory, or Poor only

No other subjective comments.

Step 6) Select the columns where this feedback is to be populated.

Step 7) Go to the Data tab > Data tools group > Data Validation.

Step 8) In the Validation criteria, select List.

Step 9) Write the desired grades in the Source field separated by a comma.

Step 10) Click on Okay.

Now launch the Form dialog box and try entering any comment other than these pre-defined grades in the relevant fields.

Excel will prevent you from doing so by giving the following error

Pro Tip!

Note that making a drop-down list of feedback options to choose from does help prevent undesired input. However, unlike within the worksheet, where when you click on a data-validated cell, Excel launches the drop-down list of options to choose an input from, the Form dialog box won’t show the drop-down list of options.

This might confuse the users about populating the field entries while they try to fill out the form. To avoid that, you must guide the users (through some other way) that certain field entries have restricted input criteria that must be followed.

Potential problems with Excel forms

The Form tool of Excel helps data entry in big time but, has its fair share of problems. Here’s what you should bear in mind

• You cannot use any formulas in it.
• Doesn’t allow any formatting options (like what if a customer wants to give the feedback that the food was “JUST OUT OF THIS WORLD!!”. Like that, boldened and italicized). They can’t do that with the Form dialog box.
• It doesn’t allow advanced field types like drop-down lists, radio buttons, checkboxes, etc.
• No in-built data validation rules. You have to set them up separately.
• The Form tool is only limited to the active worksheet of an Excel file. Using a single form, you cannot input data across multiple worksheets or workbooks
• You have little control over how the form looks or behaves beyond the default settings provided by Excel.

With these limitations, the Form tool of Excel is best for simpler data entry tasks and may not be the best option for complex data entries.

Conclusion

This step-by-step guide comprehensively covers the end-to-end steps to create a form in MS Excel that’s functional, user-friendly, and dynamic

An Excel form can be specifically designed to meet your specific data entry needs. By using the data validation tool, you can streamline the input process to make sure there are no data entry errors and there is consistency in data collection.

Creating fillable forms in Excel is a skill that can take your data game to the next level. If you enjoyed reading this tutorial, make sure you check out the following Excel blogs by Spreadsheeto too.

• How to Lock Formulas in Excel (But Allow Data Entry)
• How to Use Data Validation in Excel: Full Tutorial (2024)
• How to Add Yes or No Options in Excel (Drop-down & Box)

How to Center Text in Excel (Works Every Time)

As you store data in Excel (some of which might be text), the alignment of the data within the cells is a core aspect.

Centering (or aligning) text in Excel is a fundamental feature. It not only adds to the visual appeal of your Excel sheet but also increases the readability of your data.

For instance, if you’re preparing a report, a dashboard, or a simple fillable form in Excel, unless the alignment of each field is in place, your masterpiece will not make enough impact.

In this Excel tutorial, we are going to explore a variety of methods that you can use to center text in Excel. These methods apply to all old and new versions of Excel so, you can download our free sample workbook for this guide here and tag along with us

Table of Contents

Center text from the alignment buttons

Here, I have some data in Excel that’s pretty out of order. Some text is left-aligned, and some is right-aligned

I will introduce you to the most straightforward method (step-by-step) to center all this text in Excel at once

And that is, to use the alignment buttons on the Ribbon > Home tab.

Step 1) Select the cell or range of cells you want to center.

Step 2) Go to the Excel ribbon > Home tab > Alignment group > click on the Center-alignment button icon (the four centered horizontal lines).

As soon as you press this button, swish! The text in the selected cells will be aligned in the center of the cell

Easy it is. Also, as you continue to add more text to these cells, the alignment won’t change. It will remain center-aligned.

This equally applies to the deletion of text from the cell.

Center text using keyboard shortcut keys

To use the same method as, above but not your cursor, read this section (especially for keyboard enthusiasts)

Working around your spreadsheet speedily and do not want to waste time reaching out for the cursor to center-align some cells? Do this.

Step 1) Press down the shift key and use the arrow buttons to select the cells where you want to center-align the text.

Down arrow key to select the next cells downwards and right arrow key to select the next cells rightwards

Step 2) Press the Alt key + H to activate the home tab.

Step 3) Then, press the A key and then the C key (sequentially and not simultaneously).

Pressing the sticky keys Alt > H > A > C in this sequence will center the text in the selected cells.

There you go, keyboard wizards

Center text by indenting it

Unpopular way but you can also center text in Excel by manually indenting it towards the right.

To do so, follow these steps

Step 1) Select the cell containing the text that you want to center.

Step 2) Go to the Home tab > Alignment group, and click on the Increase Indent button (represented by a right arrow passing through four centered lines).

Clicking it once will indent the text in the cell towards the right by one space.

Step 3) Keep clicking this button to indent the text right until you have reached the center of the cell.

Apparently, the text is center aligned

You’d have to do this separately for each cell as the length of text in each cell is different. As the text increases, the text would be placed in the center of the cell with lesser indentation.

The only difference between the indenting and the auto center-alignment method is that with this method if you add more text to this cell, the text won’t center in automatically.

It will continue going to the right of the existing text as the cell is not center-aligned. It is only indented to the center of the cell. So, this method works to center the text visually but not actually

Keeping this difference in mind, choose between the two methods (center alignment or indentation) to center text in Excel.

Center text across multiple cells

An Excel sheet contains 17 billion cells. So why should centering only be about a single cell?

If you want to center a text between multiple cells that looks like this:

There are two ways how you can achieve this

Center Text by Using Merge and Center

The Merge and Center feature of Excel is used to combine multiple cells into one and center the text within that merged cell

Let me show you that here.

Step 1) Select the range of cells you want to merge and center the text in.

Step 2) Go to the Home tab > Alignment group > click the Merge & Center button.

Step 3) The selected cells will merge into one large cell, and the text will be centered within it.

This method is simple until here. But if you have data in all cells that you’re merging, Excel will merge them all and retain the data in the upper left cell only

Center text across selection

Another method to achieve the centering of text across multiple selected cells (but without merging them) is to use the “Center text across selection” feature of Excel.

Step 1) Select the range of cells where you want to center the text across.

Step 2) Go to the Home tab > Alignment group > click on the small arrow at the bottom-right corner to launch the Format Cells dialog box

Step 3) Go to the Alignment tab.

Step 4) Under Horizontal alignment section, select Center Across Selection from the dropdown menu.

Step 5) Click Okay.

This will effectively center the text in the selected cells

However, what must be understood is that the text is only visually centered between Cell B2, C2, and D2.

It still sits in Cell B2 only (check the formula bar)

As you type any data in say, Cell D2, the text centering will shrink to Cell B2 and C2 only.

Centering data across multiple cells using this method saves you from losing data by merging cells.

Conclusion

This guide must have helped you master the different techniques to center text in single and multiple cells in Excel. Pat yourself on the back for learning the basic but very essential tips to create more organized and visually appealing spreadsheets

Though simple knowing such tips and less commonly known methods help you stay on top. To learn similarly exciting Microsoft Excel tips and tricks, hop on to the following Excel blogs by Spreadsheeto.

• How to Combine Rows in Excel (Without Losing Data)
• How to Save a Copy of an Excel File (Original Intact)
• How to Highlight a Row in Excel (All Scenarios Shown)