General Sort
- Click into any cell in the COLUMN you want to sort by within your list. (DO NOT highlight that column as this will sort that column only and leave the rest of your data where it is.)
- Click on the DATA tab.
- Click on either the Sort Ascending or Sort Descending. button.
To sort a range:
- Select the cell range you want to sort.
- Select the Data tab on the Ribbon, then click the Sort command.
- The Sort dialog box will appear.
- Decide the sorting order (either ascending or descending).
- Once you're satisfied with your selection, click OK.
- The cell range will be sorted by the selected column.
Filter Unique Records
- Select a cell in the database.
- On the Excel Ribbon's Data tab, click Advanced.
- In the Advanced Filter dialog box, choose 'Copy to another location'.
- For the List range, select the column(s) from which you want to extract the unique values.
- Leave the Criteria Range blank.
Copying the Results of Filtering
- Select the area you want to filter.
- Display the Data tab of the ribbon.
- Click the Advanced tool, in the Sort & Filter group.
- Set your filtering options as desired.
- Make sure the Copy to Another Location radio button is selected.
- Specify a copy destination in the Copy To field.
- Click on OK.
Navigate to the File > Options menu. In the Excel Options window, open Advanced on the left. Under the Editing Options section, toggle Enable AutoComplete for cell values on or off depending on whether you want to turn this feature on or disable it. Click or tap OK to save the changes and continue using Excel.
Quickly Fill Numbers in Cells without Dragging
- Enter 1 in cell A1.
- Go to Home –> Editing –> Fill –> Series.
- In the Series dialogue box, make the following selections: Series in: Columns. Type: Linear. Step Value: 1. Stop Value: 1000.
- Click OK.
Custom Excel Fill Series
- Select both cells and then grab the lower right box and drag down as many rows as you want.
- You can also create your own Excel custom list.
- If you don't already have these values in a spreadsheet go to Tools/ Options/ Custom Lists and select Add.
- You can show your master list or hide it.
Here are the steps to do this:
- Go to Developer Tab –> Insert –> ActiveX Controls –> Combo Box (ActiveX Control).
- Move your cursor to the worksheet area and click anywhere.
- Right-click on the Combo Box and select Properties.
- In the properties dialogue box, make the following changes:
Using Flash Fill in Excel. Flash Fill automatically fills your data when it senses a pattern. For example, you can use Flash Fill to separate first and last names from a single column, or combine first and last names from two different columns. Note: Flash Fill is only available in Excel 2013 and later.
A Custom List in Excel is very handy to fill a range of cells with your own personal list. It could be a list of your team members at work, countries, regions, phone numbers or customers. The main goal of a custom list is to remove repetitive work and manual errors in inputting.
Excel fills in lists like Names of Months or Days. Custom Lists are lists you can set up based on any criteria so that Excel can Autofill when you enter the first item in the list and click and Drag to Autofill the list. Visit us at Presentation-Process.com for more.
Custom number formats can control the display of numbers, dates, times, fractions, percentages, and other numeric values. Using custom formats, you can do things like format dates to show month names only, format large numbers in millions or thousands, and display negative numbers in red.
Video
- In a new worksheet, type the entries you want to appear in your drop-down list.
- Select the cell in the worksheet where you want the drop-down list.
- Go to the Data tab on the Ribbon, then Data Validation.
- On the Settings tab, in the Allow box, click List.
- Click in the Source box, then select your list range.
Add data validation to a cell or a range
- Select one or more cells to validate.
- On the Data tab, in the Data Tools group, click Data Validation.
- On the Settings tab, in the Allow box, select List.
- In the Source box, type your list values, separated by commas.
- Make sure that the In-cell dropdown check box is selected.
Edit a drop-down list with items that have been entered manually
- On the worksheet where you applied the drop-down list, select a cell that has the drop-down list.
- Go to Data > Data Validation.
- On the Settings tab, click in the Source box, and then change your list items as needed.
Follow these steps to create a custom list:
- Click the File tab and then click Options.
- Click the Advanced tab.
- Click the Edit Custom Lists button in the General section.
- Click inside the List Entries list box and then type each entry (in the desired order), pressing Enter after each list item.
- Click the Add button.
Make sure no hidden rows or columns exist. If you're sorting by a column containing a formula, Excel will recalculate the column after the sort. If the values change after the recalculation, such as with RAND, it may appear that the sort did not work properly, but it did.
You can create a form in Excel by adding content controls, such as buttons, check boxes, list boxes, and combo boxes to a workbook. Other people can use Excel to fill out the form and then print it if they choose to.
We can use the Ctrl and Shift keys to select multiple sheets. Hold the Ctrl key and left-click sheet tabs to add them to the group of select sheets. You can also hold the Shift key and left-click a sheet to select all sheets from the active sheet to the sheet you clicked.
