© Roy Cox (2005 - 2020)

.

Data Layout in Microsoft Excel.

Microsoft Excel can be used as a Database and if you take a close look you will find specific Database Functions such as DGet, Dsum etc. Add to this PivotTables, the SubTotal Function, Sumif, CountIf, etc and you can build quite powerful databases in Excel. In addition, the later versions have the powerful Table feature. So, when you're ready to keep track of contacts, sales, expenditures, or whatever it is you're using Excel for, you will find plenty of tools to work with. However, to use these tools the data must be laid out correctly so that it organises what you're tracking in such a way that you can easily manipulate the data, create formulas to work with the data, or even export it to various formats and link it into existing applications (i.e. Database Applications). Below are some guidelines to follow to help keep your data nice, neat, and easy to work with: Keep your data in one continuous block. This means that you've got field names across the top row, and data starting directly underneath (row 2). Don't skip any rows to "break up" the data (you can easily use colours instead), and don't leave blank or unnecessary columns cluttering up your sheet. If you don't use it, get rid of it. Remember this is data so don’t go over the top with formatting and colours.

Titles/Headers

It's best to use a header row and a good idea is leave clear the top two or three rows above the header, and then keep your data underneath. Keep your data formats right. Never use apostrophes in front of numbers or text. Using the apostrophe tells Excel to store whatever it is you've typed as text, and why would you want numbers stored as text? You don't. If you need leading zeros, you can use a custom format to accomplish it. Remember if you need to sort numbers stored as text, they will sort incorrectly. By following hese simple rules, you'll be on the fast track to easy data analysis and manipulation!

Adding Data to the Database

Data can be added directly to the Database by a user. You can control what they enter using Data Validation to perhaps allow only Numbers or maybe dates, build a list of choices using Data Validation’s list feature, prevent duplicate data being entered. When a row of data in a worksheet is very wide with several columns and requires repeated horizontal scrolling, consider using a data form to add, edit, find, and delete rows.

The Excel DataForm

Few users realise that Excel has an in-built Data Entry Form for adding and working with data that is set up correctly. A data form provides a convenient method to enter or display one complete row of information in a range or table without scrolling horizontally. You may find that using a data form can make data entry easier than moving from column to column when you have more columns of data than can be viewed on the screen. Use a data form when a simple form of text boxes that list the column headings as labels is sufficient and you don't need sophisticated or custom form features, such as a ListBox , ComboBox or other more advanced controls. In earlier versions of Excel, the DataForm was displayed simply by selecting a cell within the table of data then opening the Data menu and, finally, clicking Form in the Menu. Basically, the DataForm is the same in all current versions of Excel. However, accessing it changed with the introduction of Excel 2007. Unfortunately, the command to access the data form is not on the Ribbon and you cannot add it directly to the Data Tab, which would make sense. Fortunately, you can add it to the Quick Access ToolBar.

Add a button to the Quick Access Toolbar(QAT)

1 . Click the arrow next to the Quick Access Toolbar, and then click More Commands. 2 . In the Choose commands from box, click All Commands, and then select the Form Control button in the list. 3 . Click Add, and then click OK. 4 . On the Quick Access Toolbar, click the Form Control button. If you aren’t sure what the Quick Access Toolbar is then read The Quick Access ToolBar. Learn how to customise it here.

Using the DataForm

To display the DataForm, click a cell in the range or table to which you want to add the form. If you see a message that states "Too many fields in the data form" you need to reduce the number of columns, because a data form can only contain up to 32 columns of data. One solution when this happens is to insert a blank column, effectively breaking one range into two ranges. If you use this approach then you would need to create a separate data form for the columns to the right of the blank column. I would really recommend keeping to no more than 32 columns of data. You can use a data form to add, find, change, and delete rows in a range or table.

Add a new row of data:

After displaying the data form, click the New Button. In the data form type the data for the new row in the fields provided. If a Field contains a formula then the form will not allow an entry there. To move to the next field in the row, press Tab. To move to the previous field, press Shift+Tab. After you have finished typing the data, press Enter to save your changes and add the row to the bottom of the range or table. If you see a message that states "Cannot extend list or database," this could mean that existing data may be overwritten if you continue. If you add a row to a table or range by using a data form, Excel expands the data downward. If expanding the table would overwrite existing data, Excel displays this message. You must rearrange the data on your worksheet so that the range or table can expand downward from the last row. I would not recommend having any data or notes below the table to avoid this issue. Before you press Enter, you can undo any changes by clicking Restore in the data form. Any data that you have typed in the fields is discarded.

Find a row by navigating:

To move through rows one at a time, use the scroll bar arrows in the data form. To move through 10 rows at a time, click the scroll bar in the area between the arrows. To move to the next row in the range or table, click Find Next. To move to the previous row in the range or table, click Find Prev.

Find a row by entering search criteria:

Click Criteria, and then enter the comparison criteria in the data form. All items that begin with the comparison criteria are filtered. For example, if you type Will as a criterion, Excel finds "William", Williamson”, Williams”, etc.To find text values that share some characters but not others, use a wildcard character as your criterion. The following wildcard characters can be used as comparison criteria for filters, and when you search for and replace content. A question mark (?) will find any single character, e.g. sm?th finds "smith" and "smyth" An asterisk (*) can be used to find any number of characters, such as, *east finds "Northeast" and "Southeast" A tilde (~) followed by a question mark, asterisk, or tilde, for example, fy91~? finds "fy91?" To find rows that match the criteria, click Find Next or Find Prev. To return to the data form so that you can add, change, or delete rows, click Form.

Change data in a row:

Find the row that you want to change using one of these methods described above. To move to the next field in the row, press Tab. To move to the previous field, press Shift+Tab. Make the changes that you want and after you finish, press Enter to update the row. Excel automatically moves to the next row. Note: Before you press Enter, you can undo any changes by clicking Restore.

Delete a row

In the data form, find the row that you want to delete. Click Delete. Warning: Excel prompts you to confirm the operation. You cannot undo a row deletion after you confirm it.

Close a data form

To close the data form and return to the worksheet, click Close in the data form.

Working with data in Excel

Page Title

Making Excel work for you.
If you find this article useful You can help maintain my site by donating. Simply click the image below
Buy me a coffeeBuy me a coffee

© Ut duis incididunt ex officia

.

Data Layout in Microsoft Excel.

Microsoft Excel can be used as a Database and if you take a close look you will find specific Database Functions such as DGet, Dsum etc. Add to this PivotTables, the SubTotal Function, Sumif, CountIf, etc and you can build quite powerful databases in Excel. In addition, the later versions have the powerful Table feature. So, when you're ready to keep track of contacts, sales, expenditures, or whatever it is you're using Excel for, you will find plenty of tools to work with. However, to use these tools the data must be laid out correctly so that it organises what you're tracking in such a way that you can easily manipulate the data, create formulas to work with the data, or even export it to various formats and link it into existing applications (i.e. Database Applications). Below are some guidelines to follow to help keep your data nice, neat, and easy to work with: Keep your data in one continuous block. This means that you've got field names across the top row, and data starting directly underneath (row 2). Don't skip any rows to "break up" the data (you can easily use colours instead), and don't leave blank or unnecessary columns cluttering up your sheet. If you don't use it, get rid of it. Remember this is data so don’t go over the top with formatting and colours.

Titles/Headers

It's best to use a header row and a good idea is leave clear the top two or three rows above the header, and then keep your data underneath. Keep your data formats right. Never use apostrophes in front of numbers or text. Using the apostrophe tells Excel to store whatever it is you've typed as text, and why would you want numbers stored as text? You don't. If you need leading zeros, you can use a custom format to accomplish it. Remember if you need to sort numbers stored as text, they will sort incorrectly. By following hese simple rules, you'll be on the fast track to easy data analysis and manipulation!

Adding Data to the Database

Data can be added directly to the Database by a user. You can control what they enter using Data Validation to perhaps allow only Numbers or maybe dates, build a list of choices using Data Validation’s list feature, prevent duplicate data being entered. When a row of data in a worksheet is very wide with several columns and requires repeated horizontal scrolling, consider using a data form to add, edit, find, and delete rows.

The Excel DataForm

Few users realise that Excel has an in-built Data Entry Form for adding and working with data that is set up correctly. A data form provides a convenient method to enter or display one complete row of information in a range or table without scrolling horizontally. You may find that using a data form can make data entry easier than moving from column to column when you have more columns of data than can be viewed on the screen. Use a data form when a simple form of text boxes that list the column headings as labels is sufficient and you don't need sophisticated or custom form features, such as a ListBox , ComboBox or other more advanced controls. In earlier versions of Excel, the DataForm was displayed simply by selecting a cell within the table of data then opening the Data menu and, finally, clicking Form in the Menu. Basically, the DataForm is the same in all current versions of Excel. However, accessing it changed with the introduction of Excel 2007. Unfortunately, the command to access the data form is not on the Ribbon and you cannot add it directly to the Data Tab, which would make sense. Fortunately, you can add it to the Quick Access ToolBar.

Add a button to the Quick Access Toolbar(QAT)

1 . Click the arrow next to the Quick Access Toolbar, and then click More Commands. 2 . In the Choose commands from box, click All Commands, and then select the Form Control button in the list. 3 . Click Add, and then click OK. 4 . On the Quick Access Toolbar, click the Form Control button. If you aren’t sure what the Quick Access Toolbar is then read The Quick Access ToolBar. Learn how to customise it here.

Using the DataForm

To display the DataForm, click a cell in the range or table to which you want to add the form. If you see a message that states "Too many fields in the data form" you need to reduce the number of columns, because a data form can only contain up to 32 columns of data. One solution when this happens is to insert a blank column, effectively breaking one range into two ranges. If you use this approach then you would need to create a separate data form for the columns to the right of the blank column. I would really recommend keeping to no more than 32 columns of data. You can use a data form to add, find, change, and delete rows in a range or table.

Add a new row of data:

After displaying the data form, click the New Button. In the data form type the data for the new row in the fields provided. If a Field contains a formula then the form will not allow an entry there. To move to the next field in the row, press Tab. To move to the previous field, press Shift+Tab. After you have finished typing the data, press Enter to save your changes and add the row to the bottom of the range or table. If you see a message that states "Cannot extend list or database," this could mean that existing data may be overwritten if you continue. If you add a row to a table or range by using a data form, Excel expands the data downward. If expanding the table would overwrite existing data, Excel displays this message. You must rearrange the data on your worksheet so that the range or table can expand downward from the last row. I would not recommend having any data or notes below the table to avoid this issue. Before you press Enter, you can undo any changes by clicking Restore in the data form. Any data that you have typed in the fields is discarded.

Find a row by navigating:

To move through rows one at a time, use the scroll bar arrows in the data form. To move through 10 rows at a time, click the scroll bar in the area between the arrows. To move to the next row in the range or table, click Find Next. To move to the previous row in the range or table, click Find Prev.

Find a row by entering search criteria:

Click Criteria, and then enter the comparison criteria in the data form. All items that begin with the comparison criteria are filtered. For example, if you type Will as a criterion, Excel finds "William", Williamson”, Williams”, etc.To find text values that share some characters but not others, use a wildcard character as your criterion. The following wildcard characters can be used as comparison criteria for filters, and when you search for and replace content. A question mark (?) will find any single character, e.g. sm?th finds "smith" and "smyth" An asterisk (*) can be used to find any number of characters, such as, *east finds "Northeast" and "Southeast" A tilde (~) followed by a question mark, asterisk, or tilde, for example, fy91~? finds "fy91?" To find rows that match the criteria, click Find Next or Find Prev. To return to the data form so that you can add, change, or delete rows, click Form.

Change data in a row:

Find the row that you want to change using one of these methods described above. To move to the next field in the row, press Tab. To move to the previous field, press Shift+Tab. Make the changes that you want and after you finish, press Enter to update the row. Excel automatically moves to the next row. Note: Before you press Enter, you can undo any changes by clicking Restore.

Delete a row

In the data form, find the row that you want to delete. Click Delete. Warning: Excel prompts you to confirm the operation. You cannot undo a row deletion after you confirm it.

Close a data form

To close the data form and return to the worksheet, click Close in the data form.

Working with data in

Excel

How to Install an Addin

Page Title

Logotype