To keep an area of an Excel worksheet visible while you scroll to another area of the worksheet, you can either freeze specific rows or columns in one area to lock them in place or create multiple worksheet areas that can scroll separately from each other by splitting rows and columns. Excel is a powerful application—but it can also be very intimidating. That’s why we’ve put together this beginner’s guide to getting started with Excel. It will take you from the very beginning (opening a spreadsheet), through entering and working with data, and finish with saving and sharing. XLSX is a file format created with Microsoft Excel, version from 2007 and younger. This file is a spreadsheet. All data distributed document and stored in the cells. Each cell has a unique address, which is denoted by the letters and Arabic numerals. XLSX file format is based on the Open XML. To reduce the size of the file is compressed ZIP. Flow is now integrated into Microsoft Excel! With this integration that uses the For a selected row trigger and the Flow launch panel, you can create and trigger on-demand flows for selected rows in any Excel table on spreadsheets hosted in SharePoint or OneDrive for Business. The Microsoft Flow for Excel add-in enables you to connect your data to a wide range of services such as SharePoint.

We’re pleased to announce that Microsoft Flow is now integrated into Excel. With this integration that uses the For a selected row trigger and the Flow launch panel, you can create and trigger on-demand flows for selected rows in any Excel table on spreadsheets hosted in SharePoint or OneDrive for Business. The Microsoft Flow for Excel add-in enables you to connect your data to a wide range of services such as SharePoint, Outlook 365, Dynamics 365, Teams, Visual Studio Online, Twitter, Approvals, etc. In this post, we’ll walk you through this new capability with a hands-on example.

To get started, in Excel, go to the Insert tab in the ribbon and select Store. Then, in the dialog, search for Microsoft Flow. Then, click Add.

Let’s imagine that you work for Cronus Energy, a multi-national energy production company, which generates energy through wind turbines and hydroelectric power plants. Cronus is on the lookout for better ways to streamline and standardize internal processes to make things easier for their employees. They’ve identified a key process they want to modernize:

Transfer market data entered by Commercial Analysts (minimum energy to generate, maximum energy, and target energy based on revenue goals) to SharePoint so that the Operations team can decide which turbines to use for the week. After moving the data to SharePoint, they also want to send an alert to the team on Microsoft Teams and facilitate a discussion if needed. The Operations team uses a SharePoint list called Turbine Energy Distributions with the columns shown below.
Their development team is already short on resources, so they want to be able to stand up the solution quickly while avoiding as much custom development as possible. Let’s see how Flow can help.

The Commercial Analysts at Cronus Energy enter market data in a spreadsheet hosted in SharePoint. To follow along, download this spreadsheet and upload it to SharePoint or OneDrive for Business.
To get started, click the Flow menu from under the Data tab in the ribbon.
This will open the Flow launch panel in Excel where you will be prompted to Sign in and consent to the permissions requested by the add-in. Click Accept.

Once you’ve signed in, you can explore several templates to quickly connect to a wide variety of services with minimal set up. Scroll down the screen and choose Create an item in SharePoint for a selected row.
Selecting the template will prompt you for your credentials and provide additional details about the template. Click Continue.
In the trigger (For a selected row), click the drop-down next to the Table field and select your table, e.g. Table1. The trigger may be collapsed; if so, click on Edit and confirm that the Table field is set to Table1.
The For a selected row trigger is similar to other manual triggers like the Flow button for mobile or SharePoint’s For a selected item – users can be prompted for inputs when they run the flow (Text, Yes/No, File, Email, or Number) and all flows run with the credentials of the invoker. For this flow, add a Text input called Message with the hint text of “Enter a message for the team.”
In the SharePoint-Create item action, enter the Site Address and List Name for Cronus Energy’s Turbine Energy Distributions List.
Click the Week field and select Week from the Dynamic content pane.
The parameters in the Dynamic content pane consist of your table’s columns – Week, Min Energy (mWh), Target Energy (mWh), Max Energy (mWh), Price ($/mWh), Revenue, and Profit, information about the person invoking the flow – Timestamp, User email, User id, and User name, and lastly any “manual” Outputs you add to the trigger like Message.
Repeat this for the Energy Target, Min Energy, and Max Energy.
Now, add a Microsoft Teams – Post message action. Choose a Team and Channel to post your message to. In the Message field, enter a link to the newly created item along with the Message populated by the flow invoker.
Save the flow by clicking the Save button.

Select a row in the table and then click Run flow in the Flow launch panel.
The first time you run this flow, you’ll be asked to confirm your credentials. You can also learn more about what this flow does. Click Continue.
Now enter a message to send to your team, requesting feedback. Click Run flow.

Voila! An item is created in SharePoint with details from the row you selected in Excel and a message is posted on your behalf including your note asking for feedback and a link to the item.

Now that you’ve created the flow, you can share it with colleagues either by adding them as an owner of the flow or as a run-only user. The latter allows you to maintain ownership of the flow, while enabling your colleagues to run it. In Flow, head over to My Flows and choose the Create an item in SharePoint for a selected row flow. Note – To run the flow, they must have access to the spreadsheet.

Under Manage Run-Only Users, click Add another person.
Here you can enter individuals, AD security groups, O365 groups, or even anyone that has access to the SharePoint list. For each connector used in the flow, you can decide whether the invoker should bring their own credentials (“Provided by run-only user”) or use your credentials (“Use this connection”). Click Save to add the user as a run-only user.

We hope you enjoy this new update. If you have ideas for templates or other feedback, please leave us comments below or post on our Community forums.

Data entry can sometimes be a big part of using Excel.

With near endless cells, it can be hard for the person inputting data to know where to put what data.

A data entry form can solve this problem and help guide the user to input the correct data in the correct place.

Excel has had VBA user forms for a long time, but they are complicated to set up and not very flexible to change.

In this blog post, we’re going to explore 5 easy ways to create a data entry form for Excel.

Video Tutorial

Excel Tables

We’ve had Excel tables since Excel 2007.

They’re perfect data containers and can be used as a simple data entry form.

Creating a table is easy.

1. Select the range of data including the column headings.
2. Go to the Insert tab in the ribbon.
3. Press the Table button in the Tables section.

We can also use a keyboard shortcut to create a table. The Ctrl + T keyboard shortcut will do the same thing.

Make sure the Create Table dialog box has the My table has headers option checked and press the OK button.

We now have our data inside an Excel table and we can use this to enter new data.

To add new data into our table we can start typing a new entry into the cells directly below the table and the table will absorb the new data.

We can use the Tab key instead of Enter while entering our data. This will cause the active cell cursor to move to the right instead of down so we can add the next value into our record.

When the active cell cursor is in the last cell of the table (lower right cell), pressing the Tab key will create a new empty row in the table ready for the next entry.

This is a perfect and simple data entry form.

Data Entry Form

Excel actually has a hidden data entry form and we can access it by adding the command to the Quick Access Toolbar.

Add the form command to the Quick Access Toolbar.

1. Right click anywhere on the quick quick access toolbar.
2. Select Customize Quick Access Toolbar from the menu options.

This will open up the Excel option menu on the Quick Access Toolbar tab.

1. Select Commands Not in the Ribbon.
2. Select Form from the list of available commands. Press F to jump to the commands starting with F.
3. Press the Add button to add the command into the quick access toolbar.
4. Press the OK button.

We can then open up data entry form for any set of data.

1. Select a cell inside the data which we want to create a data entry form with.
2. Click on the Form icon in the quick access toolbar area.

This will open up a customized data entry form based on the fields in our data.

Microsoft Forms

If we need a simple data entry form, why not use Microsoft Forms?

This form option will require our Excel workbook to be saved into SharePoint or OneDrive.

The form will be in a browser and not in Excel, but we can link the form to an Excel workbook so that all the data goes into our Excel table.

This is a great option if multiple people or people outside our organization need to input data into the Excel workbook.

We need to create a Form for Excel in either SharePoint or OneDrive. The process is the same for both SharePoint or OneDrive.

Excel To Excellence Foundation

1. Go to a SharePoint document library or a OneDrive folder where the Excel workbook is going to be saved.
2. Click on New and then choose Forms for Excel.

This will prompt us to name the Excel workbook and open up a new browser tab where we can build our form by adding different types of questions.

We first need to create the Form and this will create the table in our Excel workbook where the data will get populated.

Then we can share the form with anyone we want to input data into Excel.

When a user enters data into the form and presses the submit button, that data will automatically show up into our Excel workbook.

Power Apps

Power Apps is a flexible drag and drop formula based app building platform from Microsoft.

We can certainly use it to create a data entry from for our Excel data.

In fact, if we have a table of data set up, Power Apps will create the app for us based on our data. It can’t be any easier than that.

Excel To Excel Export

Sign in to the powerapps.microsoft.com service ➜ go to the Create tab in the navigation pane ➜ select Excel Online.

We’ll then be prompted to sign in to our SharePoint or OneDrive account where our Excel file is saved to select the Excel workbook and table with our data.

This will generate us a fully functional three screen data entry app.

1. We can search and view all the records in our Excel table in a scroll-able gallery.
2. We can view an individual record in our data.
3. We can edit an existing record or add new records.

This is all connected to our Excel table, so any changes or additions from the app will show up in Excel.

Power Automate

Power Automate is a cloud based tool for automating task between apps.

But we can use the button trigger to make an automation that captures user input and adds the data into an Excel table.

We’ll need to have our Excel workbook saved in OneDrive or SharePoint and have a table already setup with the fields we want to populate.

To create our Power Automate data entry form.

1. Go to flow.microsoft.com and sign in.
2. Go to the Create tab.
3. Create an Instant flow.
4. Give the flow a name.
5. Choose the Manually trigger a flow option as the trigger.
6. Press the Create button.

This will open up the Power Automate builder and we can build our automation.

1. Click on the Manually trigger a flow block to expand the trigger’s options. This is where we’ll find the ability to add input fields.
2. Click on the Add an input button. This will give us options to add a few different types of input fields including Text, Yes/No, Files, Email, Number and Dates.
3. Rename the field to something descriptive. This will help the user know what type of data to input when they run this automation.
4. Click on the three ellipses to the right of each field to change the input options. We’ll be able to Add a drop-down list of option, Add a multi-select list of options, Make the field optional or Delete the field from this menu.
5. After we have added all our input fields, we can now add a New step to the automation.

Search for the Excel connector and add the Add a row into a table action. If you’re on an Office 365 business account, use the Excel Online (Business) connectors, otherwise use the Excel Online (OneDrive) connectors.

Now we can set up our Excel Add a row into a table step.

1. Navigate to the Excel file and table where we are going to be adding data.
2. After selecting the table, the fields in that table will appear listed and we can add the appropriate dynamic content from the Manually trigger a flow trigger step.

Now we can run our Flow from the Power Automate service.

1. Go to My flows in the left navigation pane.
2. Go to the My flows tab.
3. Find the flow in the list of available flows and click on the Run button.
4. A side pane will pop up with our inputs and we can enter our data.
5. Click Run flow.

We can also run this from our mobile device with the Power Automate apps.

1. Go to the Buttons section in the app.
2. Press on the flow to run.
3. Enter the data inputs in the form.
4. Press on the DONE button in the top right.

Whichever way we run the flow, a few seconds later the data will appear in our Excel table.

Conclusions

Whether we require a simple form or something more complex and customize-able, there is a solution for our data entry needs.

We can quickly create something inside our workbook or use an external solution that connects to and loads data into Excel.

We can even create forms that people outside our organization can use to populate our spreadsheets.

Let me know in the comments what is your favourite data entry form option.