Sorting by a Custom List in Excel

Sort by a Custom List in Microsoft Excel

You can use a custom list to sort in a user-defined order. Excel provides built-in, day-of-the-week and month-of-the year custom lists, and you can also create your own custom list.

1. Select a column of data in a range of cells, or make sure that the active cell is in a table column.
2. On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.
The Sort dialog box is displayed.
3. Under Column, in the Sort by or Then by box, select the column that you want to sort by a custom list.
4. Click the drop down button under Order and select Custom List.
5. In the Custom Lists dialog box, select the list that you want. Or you can type in you own list by cliking the Add button and typing; for example High, Medium, Low, separated by comma.
6. Click Ok twice.

Sort Rows

1. Select a row of data in a range of cells, or make sure that the active cell is in a table column.
2. On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.
The Sort dialog box is displayed.
3. Click Options.
4. In the Sort Options dialog box, under Orientation, click Sort left to right, and then click Ok.
5. Under Row, in the Sort by box, select the row that you want to sort.
6. Do one of the following:
By value
1. Under Sort On, select Values.
2. Under Order, do one of the following:
1. For text values, select A to Z or Z to A.
2. For number values, select Smallest to Largest or Largest to Smallest.
3. For date or time values, select Oldest to Newest or Newest to Oldest.
By cell colour, font colour, or cell icon
1. Under Sortn, select Cell Color, Font Color, or Cell Icon.
2. Click the arrow next to the button, and then select a cell color, font color, or cell icon.
3. Under Order, select On Left or On Right.

Sort by more than one Column or Row

You might sort by more than one column or row when you have data that you want to group by the same value in one column or row, and then sort another column or row within that group of equal values. For example, if you have a Department and Employee columns, you can first sort by Department (to group all the employees in the same department together), and then sort by name (to put the names in alphabetical order within each department).

Again, if you want to find a way of categorising employees into a seniority list when such employees have the same date of employment, consider sorting by another column beside the Date Employed column. You can sort by up to 64 columns.

For best results, the range of cells that you sort should have column headings.

1. Select the entire range of cells with two or more columns of data, or make sure that the active cell is in a table with two or more columns.
2. On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.
The Sort dialog box is displayed.
3. Under Column, in the Sort by box, select the first column that you want to sort.
4. Under Sort On, select the type of sort by doing one of the following:
1. To sort by text, number, or date and time, select Values.
2. To sort by format, select Cell Color, Font Color, or Cell Icon.
5. Under Order, select how you want to sort by doing one of the following:
1. For text values, select A to Z or Z to A.
2. For number values, select Smallest to Largest or Largest to Smallest
3. For date or time values, select Oldest to Newest or Newest to Oldest.
4. To sort based on a custom list, select Custom List.
6. To add another column to sort by, click Add Level button, and then repeat steps three through five.
7. To copy a column to sort by, select the entry, and then click Copy Level.
8. To delete a column to sort by, select the entry, and then click Delete Level.
You must keep at least one entry in the list that is being used in the sorting.
9. To change the order in which the columns are sorted, select an entry, and then click the Up or Down arrow to change the order.

General Issues/Problems with Sorting

If you get unexpected results when sorting your data, do the following:

>Check to see if the values returned by a formula have changed. If the data that you have sorted contains one or more formulas, the return values of those formulas can change when the worksheet is recalculated. In this case, make sure that you reapply the sort or do the sort again to get up-to-date results.

Unhide rows and columns before you sort. Hidden columns are not moved when you sort columns, and hidden rows are not moved when you sort rows. Before you sort data, it's a good idea to unhide any hidden columns and rows.

Check the locale setting. Sort orders vary by locale setting. Make sure that you have the proper locale setting in Regional Settings or Regional and Language Options in Control Panel on your computer (details of control panel setting is not covered here).

Enter column headings in only one row. If you need multiple line labels, wrap the text within the cell. Detaigls of text wrapping in cells will be covered in later posts ion this blog, it will be covered under "Editing Workbook."

Turn on or off the heading row. It's usually best to have a heading row when you sort a column to make it easier to understand the meaning of the data. By default, the value in the heading is not included in the sort operation. Occasionally, you may need to turn on or off the heading so that the value in the heading is or is not included in the sort operation. Select 'My data has headers' in the sort dialog box to exclude column headers in the sort data. Then clear 'My data has headers' to include the first row of data in the sorting because it is not a column heading.

Data Sorting in Microsoft Excel

Sorting data is an integral part of data analysis. You might want to put a list of names in alphabetical order, compile a list of product inventory levels from highest to lowest, or order rows by colours or icons. Sorting data helps you quickly visualize and understand your data better, organize and find the data that you want, and ultimately make more effective decisions. To find the top or bottom values in a range of cells or table, such as top 10 grades or bottom 5 sales amounts, use AutoFilter or conditional formatting.

Sort data in a range or table

You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and newest to oldest) in one or more columns. You can also sort by a custom list (such as Large, Medium, and Small) or by format, including cell colour, font colour, or icon set. Most sort operations are column sorts, but you can also sort by rows.

Sort criteria are saved with the workbook so that you can reapply the sort each time that you open the workbook for an Excel table, but not for a range of cells. If you want to save sort criteria so that you can periodically reapply a sort when you open a workbook, then it's a good idea to use a table. This is especially important for multicolumn sorts or for sorts that take a long time to create.

In Excel 2007, you can apply sort states with up to sixty-four sort conditions to sort data by, but earlier versions of Excel support sort states with up to three conditions only. To avoid losing sort state information in earlier versions of Excel when the workbook is opened in an earlier version; you may want to change the sort state to one that uses no more than three conditions.

All sort state information remains available in the workbook, even after opening in an earlier version and is applied when the workbook is opened again in Excel 2007, unless the sort state information is edited in the earlier version of Excel.

Sort Text

1. Select a column of alphanumeric data in a range of cells, or make sure that the active cell is in a table column containing alphanumeric data.
2. On the Home tab, in the Editing group, and then click Sort & Filter.
3. Do one of the following:

1. To sort in ascending alphanumeric order, click Sort A to Z.
2. To sort in descending alphanumeric order, click Sort Z to A.
Optionally, you can do a case-sensitive sort.
4. On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.
5. In the Sort dialog box, click Options.
6. In the Sort Options dialog box, select Case sensitive.
7. Click OK twice.

Note

1. Ensure that all data is stored as text. If the column that you want to sort contains numbers stored as numbers and numbers stored as text, then you need to format them all as text. If you do not, the numbers stored as numbers are sorted before the numbers stored as text.
2. To format all of the selected data as text, on the Home tab, in the Font group, click the Format Cell Font button, click the Number tab, and then under Category, click Text.
3. Remove any leading spaces before sorting the data. In some cases, data imported from another application might have leading spaces inserted before data.

Sort Numbers

1. Select a column of numeric data in a range of cells, or make sure that the active cell is in a table column containing numeric data.
2. On the Home tab, in the Editing group, click Sort & Filter, and then do one of the following:
1. To sort from low numbers to high numbers, click Sort Smallest to Largest.
2. To sort from high numbers to low numbers, click Sort Largest to Smallest.
Important

Check to ensure that all numbers are stored as number. If the results are not what you expected, the column might contain numbers stored as text and not as numbers. For example, negative numbers imported from some accounting systems or a number entered with a leading ' (apostrophe) are stored as text. For more information, see Convert numbers stored as text to numbers in Section five of this book.

Sort Dates or Times

1. Select a column of dates or times in a range of cells, or make sure that the active cell is in a table column containing dates or times.
2. On the Home tab, in the Editing group, click Sort & Filter, and then do one of the following:
1. To sort from an earlier to a later date or time, click Sort Oldest to Newest.
2. To sort from a later to an earlier date or time, click Sort Newest to Oldest.
Important
1. Check that dates and times are stored as dates or times. If the results are not what you expected, the column might contain dates or times stored as text and not as dates or times. For Excel to sort dates and times correctly, all dates and times in a column must be stored as a date or time serial number. If Excel cannot recognize a value as a date or time, the date or time is stored as text. You can also see Section five on how to convert dates stored as text to dates.
2. If you want to sort by days of the week, format the cells to show the day of the week. If you want to sort by the day of the week regardless of the date, convert them to text by using the TEXT function. However, the TEXT function returns a text value, and so the sort operation would be based on alphanumeric data.

Sort by Cell Colour, Font Colour or Icon

If you have manually or conditionally formatted a range of cells or table column, by cell colour or font colour, you can also sort by these colours. You can also sort by an icon set created through a conditional format.
1. Select a column of data in a range of cells, or make sure that the active cell is in a table column.
2. On the Home tab, in the Editing group, click Sort & Filter, and then click Custom Sort.
The Sort dialog box is displayed.
3. Under Column, in the Sort by box, select the column that you want to sort.
4. Under Sort On, select the type of sort.
Do one of the following:
1. To sort by cell colour, select Cell Color.
2. To sort by font colour, select Font Color.
3. To sort by an icon set, select Cell Icon.
5. Under Order, select how you want to sort.
Do one of the following:
1. To move the cell colour, font colour, or icon to the top or left, select On Top for a column sort, and On Left for a row sort.
2. To move the cell colour, font colour, or icon to the bottom or right, select On Bottom for a column sort and On Right for a row sort.
6. To specify the next cell colour, font colour, or icon to sort by, click Add Level, and then repeat steps four through five.
Make sure that you select the same column in the Then by box and that you make the same selection under Order.
7. Keep repeating steps five through six for each additional cell colour, font colour, or icon that you want included in the sort.
8. Click Ok.

Protecting a Shared Workbook

If the workbook you want to protect is shared

1. If the workbook is already shared and you want to assign a password to protect the sharing, you must first unshare the workbook by doing the following:
1. Have all other users save and close the shared workbook to avoid losing their work.
2. To keep a copy of the change history information that is usually lost when you unshare a workbook, do the following:
1. Click on the Review tab, in the Changes group, click Track Changes, and then click Highlight Changes.
2. In the When list, select All.
3. Clear the Who and Where check boxes.
4. Select the List changes on a new sheet check box, and then click OK.
5. Print the History worksheet or copy the history to another workbook.
Change history is the information that is maintained about changes made in past editing sessions of a shared workbook. The information includes the name of the person who made each change, when the change was made, and what data was changed.
2. In the shared workbook, on the Review tab, in the Changes group, click Share Workbook.
3. On the Editing tab, make sure that you are the only person listed in the 'Who has this workbook open now' list.
4. Clear the 'Allow changes by more than one user at the same time. This also allows workbook merging' check box. If this check box is not available, then you must unprotect the workbook before clearing the check box.
5. Clic Ok. When you are prompted about the effects on other users, click Yes.
If needed, give specific users access to ranges, protect worksheets, protect workbook elements, and set passwords for viewing and editing.
6. If prompted, save the workbook.

To Unprotect Workbook

1. Click on the Review tab, in the Changes group, click 'Unprotect Workbook.'
2. If you are prompted, enter the password and then click OK.

Protecting Workbook's Structure and Window

Protecting the Structure and Windows of a Workbook

You can lock the structure of a workbook to prevents users from adding or deleting worksheets or from displaying hidden worksheets in that workbook. You can also prevent users from changing the size or position of worksheet windows. Note that workbook structure and window protection applies to the entire workbook.

To prevent a user from accidentally or deliberately changing, moving, or deleting important data, you can protect certain worksheet or workbook elements, with or without a password.

NOTE: a. Workbook and worksheet element protection should not be confused with workbook-level password security. Element protection cannot protect a workbook from users who have malicious intent.
b. If the workbook is already shared and you want to assign a password to protect the sharing, you must first un-share the workbook.

Protect worksheet or workbook elements

When you protect a worksheet, all cells on the worksheet are locked by default, and users cannot make any changes to a locked cell. For instance, they cannot insert, modify, delete, or format data in a locked cell. You can, however, specify which elements users will be allowed to change when you protect the worksheet.

Steps to Protect Worksheet Elements

1. Select the worksheet that you want to protect.
2. To unlock any cells or ranges that you want other users to be able to change:
1. Select each cell or range that you want to unlock.
2. Click on the Home tab, in the Cells group, click Format, and then click Format Cells.
3. On the Protection tab, clear the Locked check box, and then click OK.
3. To hide any formulas that you don't want to be visible, do the following:
1. In the worksheet, select the cells that contain the formulas that you want to hide.
2. Click on the Home tab, in the Cells group, click Format, and then click Format Cells.
3. On the Protection tab, select the Hidden check box, and then click OK.
4. Now to protect the worksheet proper; click on the Review tab, in the Changes group, click Protect Sheet.
5. In the 'Allow all users of this worksheet to' list, select the elements that you want users to be able to change.
6. In the 'Password to unprotect sheet' box, type a password for the sheet, click Ok.
7. Retype the password to confirm it and click Ok.

Protect workbook elements

1. Click on the Review tab, in the Changes group, click Protect Workbook.
2. Then click Protect structure and window.
3. Under 'Protect workbook for', do any of the following:

1. To protect the structure of a workbook, select the Structure check box.
2. To keep workbook windows in the same size and position each time the workbook is opened, select the Windows check box.
4. Note that STRUCTURE prevents unauthorized users from; viewing worksheets that you have hidden, deleting, hiding, or changing the names of worksheets, inserting new worksheets or chart sheets, moving or copying worksheets to another workbook. In PivotTable reports; prevents them from displaying the source data for a cell in the data area, or displaying page field pages on separate worksheets. For SCENARIOS; it prevents them from creating a scenario summary report or recording new macros. The WINDOWS option prevents unauthorized users from changing the size and position of the windows for the workbook when the workbook is opened, moving, resizing, or closing the windows. But Users will be able to hide and unhide windows.

Workbook elements

5. To prevent other users from removing workbook protection, in the Password (optional) box, type a password, click Ok, and then retype the password to confirm it.

Connecting to Access Data from Excel

Connect to Access Data from Excel

To bring refreshable Access data into Excel, you can create a connection, to the Access database and retrieve all of the data from a table or query. The main benefit of connecting to Access data is that you can periodically analyse this data in Excel without repeatedly copying or exporting the data from Access. After you connect to the data, you can also automatically refresh (or update) your Excel workbooks from the original Access database whenever the database is updated with new information. For example, you may want to update an Excel summary budget report that you distribute every month so that it contains the current month's data.

1. From Excel window, click the cell where you want to put the data from the Access database.
2. Click on the Data tab, in the Get External Data group, click From Access.
3. In the Look in list, locate and double-click the Access database that you want to import.
4. In the Select Table dialog box, click the table or query that you want to import, and then click Ok.
5. In the Import Data dialog box, do the following:
1. Under Select how you want to view this data, do one of the following:
1. To view the data as a table, select Table.
2. To view the data as a PivotTable report, select PivotTable report.
3. To view the data as a PivotChart and PivotTable report, select PivotChart and PivotTable report.
2. Under Where do you want to put the data? Do one of the following:
1. To return the data to the location that you selected, click Existing worksheet.
2. To return the data to the upper-left corner of the new worksheet, click New worksheet.
6. Click Ok.

 

Copy Access Data into Excel

From Microsoft Access, you can copy data from a datasheet view and then paste the data into an Microsoft Excel worksheet. Additionally, you can take the following steps to copy data from Microsoft Access to Microsoft Excel:

1. Start Access, and then open the table, query, or form that contains the records that you want to copy.
2. Click on the Home tab, click View, and then click Datasheet View.
3. Select the records that you want to copy. If you want to select specific columns, drag across adjacent column headings.

4. On the Home tab, in the Clipboard group, click Copy.
5. Start Excel, and then open the worksheet that you want to paste the data into.
6. Click in the upper-left corner of the worksheet area where you want the first field name to appear.
7. To ensure that the copied records do not replace existing records, make sure that the worksheet has no data below or to the right of the cell that you click.
8. Click on the Home tab, in the Clipboard group, click Paste.

Practical Export of Data Between Access and Excel

Operation to Export Data Between Access and Excel

In my previous post I showed the several ways to exchange data between Microsoft Access and Microsoft Excel. whether to bring data into Excel from Access, to bring data into Access from Excel; you can take the following steps to accomplish it.

1. If you are working in Microsoft Access and want to export data to Microsoft Excel, open the source database.
2. In the Navigation Pane, select the object that contains the data that you want to export. (It can be a table, a query, or a form).
3. Review the source data to ensure that it does not contain any error indicators or error values. (If there are any errors, you must resolve them before you export the data to Excel. Otherwise, errors can occur during the export operation, and null values might be inserted into fields).
4. If the source object is a table or a query, decide whether you want to export the data with or without its formatting.
5. Choose the destination workbook and file format.
6. During the export operation, Access prompts you to specify the name of the destination workbook. The workbook is then created if it does not exist. But if it exists, it is overwritten if the data, including the formatting is exported, otherwise; if the data, but not the formatting is exported, the workbook is not overwritten, instead a new worksheet is added to the workbook, and is given the name of the object from which the data is being exported.
7. If the destination Excel workbook is open, close it before you continue.

Export the Data

8. In the source database, if you want to export only a portion of a table, query, or form, open the object and select the records you want.


9. On the External Data tab, in the Export group, click Excel.
10. In the Export - Excel Spreadsheet dialog box, accept the suggested name for the Excel workbook or enter another name.
11. In the File Format box, select the file format that you want.
12. If you are exporting a table or a query, and you want to export formatted data, select 'Export data with formatting and layout'.
13. To view the destination Excel workbook after the export operation is complete, select the 'Open the destination file after the export operation is complete' check box.
14. If the source object is open, and if you selected one or more records in the view before starting the export operation, you can select Export only the selected records. However, if you want to export all of the records displayed in the view, leave this check box cleared.

View that dialog box below.

15. Click Ok.

If the export operation fails due to an error, Access displays a message that describes the cause of the error. Otherwise, Access exports the data to the destination workbook in Excel. Access then displays a dialog box in which you can create a specification that uses the details from the export operation.

 

 

Save the Export Specification

1. Click Yes if you want to save the details of the export operation for future use. This helps you repeat the same export operation in the future without having to go through the entire steps in the wizard each time.
2. In the Save as box, type a name for the export specification.
3. If you want to perform the operation at fixed intervals (such as weekly or monthly), select the Create Outlook Task check box.
4. Click Save Export.

If Outlook is installed, Access starts it immediately. This creates a Microsoft Office Outlook 2007 task that lets you run the specification by clicking a button.

5. In Outlook, review and modify the task settings (such as Due date and Reminder).
6. To make the task recur, click Recurrence. This dialog box below shows the task scheduler with some typical settings.
7. Click Save and Close.

Run the Saved Task

1. In the Outlook Navigation Pane, click Tasks, and then double-click the task that you want to run.
2. On the Task tab, in the Microsoft Office Access group, click Run Import.
3. In Excel, open the destination workbook, and then verify that all of the data were copied into the correct cells.

Exchange Data Between Excel and Access

How to Exchange Data Between Microsoft Excel and Access

There are several ways to exchange data between Microsoft Office Access and Microsoft Office Excel. To bring data into Excel from Access, you can copy data from an Access datasheet and paste it into an Excel worksheet, connect to an Access database from an Excel worksheet, or export Access data into an Excel worksheet.

To bring data into Access from Excel, you can copy data from an Excel worksheet and paste it into an Access datasheet, import an Excel worksheet into an Access table, or link to an Excel worksheet from an Access table.

The word IMPORT has two different meanings between Excel and Access. In Excel, the word import means to make a permanent connection to data that can be refreshed. In Access, the word import means to bring data into Access once, but without a data connection. You CANNOT save an Excel workbook as an Access database. Neither Excel nor Access provides functionality to create an Access database from Excel data.

Export Access Data to Excel

You may want to work with Access data in an Excel workbook in order to take advantage of the data analysis and charting features, the flexibility in data arrangement and layout, or the many functions that are not available in Access. You can copy data from a Microsoft Office Access 2007 database into a worksheet by exporting a database object to a Microsoft Office Excel 2007 workbook. You do this by using the Export Wizard in Office Access 2007.

By using the Export Wizard in Access, you can export an Access database object, such as a table, query, or form, or selected records in a view into an Excel worksheet. When you perform an export operation, you can save the details for future use, and even schedule the export operation to run

Scenarios for Exporting Access Data to Excel

The following are common situations for exporting data from Access to Excel:

1. Your department or workgroup uses both Access and Excel to work with data. You store the data in Access databases, but you use Excel to analyze the data and to distribute the results of your analysis. Your team currently exports data to Excel when they need to, but if you would like to make this process more efficient, you might need to export date between the two office applications.
2. Another scenario may be that you are a long-time user of Access, but your manager prefers to view reports in Excel. At regular intervals, you do the work of copying the data into Excel, but you would like to automate this process to save yourself time, so you would need to export data.
   Note
1. To export data from Access to Excel, you must be working in Access. Excel does not provide any mechanism for importing data from an Access database. Again, you cannot save an Access database or table as an Excel workbook by using the Save As command of Access. You can only use the Save As command of Access to save an Access object in the current database as another Access database object.
2. You can export a table, query, or form. You can also export selected records in a view.
3. You cannot export macros, modules, or reports to Excel. When you export a form or datasheet that contains subforms or subdatasheets, only the main form or datasheet is exported. You must repeat the export operation for each subform and subdatasheet that you want to view in Excel.
4. You can only export one database object in a single export operation. However, you can merge the data in multiple worksheets in Excel after completing the individual export operations.