Home > Excel Help Portal > Excel Databases

<<  (Page 2)  Previous              Next  (Page 4)  >>

Excel Databases

The ExcelUser portal offers links to help pages created by top
Excel web sites. This section discusses using Microsoft Excel
databases.

101. Excel Filters -- Advanced Filter -- Complex Criteria Complex Criteria This page gives details on criteria used for specialized Advanced Filters. For an introduction to Advanced Filters, click here. The samples on this page assume a data layout as shown at right. The database is in columns A:D and the criteria range starts in cell F1. Note: When you... Keywords: Advanced Filter, Criteria, Dates, Errors, Formulas, LARGE, FIND

 
102. How to Find and Replace Tildes and Wildcard Characters Microsoft Excel uses the tilde (~) as a marker to indicate that the next character is a literal. When you click Find or Replace on the Edit menu to replace or search for a tilde (~), an asterisk (*), or a question mark (?), you must precede... KB#214138. Keywords: Wildcard

 
103. How to Use Advanced Filter to Exclude Records You can use the ISNA and MATCH worksheet functions exclude records in an Excel database that do not match another list. To create exclusion criteria, use the following formula... KB#183512. Keywords: Advanced Filter, Criteria, Formulas, MATCH

 
104. How to use Wildcard Characters in Microsoft Excel 2000 Parameter Queries This article describes various wildcard characters that you can use in parameter queries in Microsoft Query. A parameter query, when you run it, prompts for values (criteria) to use to select the records for the result set so that the same query... KB#211459. Keywords: Criteria, Query, Wildcard

 
105. Excel -- Sorting Data The Basics Sort by One Column Sort by Two or Three Columns Using the Sort Buttons Sort by Four or More Columns In Excel, it's easy to sort your data by using the Sort buttons on the toolbar. But, be careful, or one column may be sorted, while others are not. Occasionally, you may need to sort by... Keywords: Sorting, Toolbar

 
106. Processor Spikes at 100 Percent When You Click AutoFilter Arrow In Microsoft Excel 2000, when you click the arrow for a filtered list, the processor usage spikes to 100 percent. In Microsoft Excel 2002, the processor returns to its usual state when a mouse-over event occurs. However, in Excel 2000, the... KB#323785. Keywords: AutoFilter, Dates, File Name, Files

 
107. Help using Excel database functions These are more complex versions of the basic functions Sum, Average and Count. They are part of a series known as database functions and they are used to interrogate lists of data held on a worksheet. For the selected table of data these functions will perform their processing only on records (or... Keywords: SERIES, Criteria, Rank, Arrays, Formulas, Links, Hyperlinks, DAVERAGE, DCOUNT

 
108. How to Count Rows Displayed After Data Has Been Filtered This article contains a sample Microsoft Visual Basic for Applications procedure that counts the number of rows that remain after you run AutoFilter or Advanced Filter on a... KB#148621. Keywords: Advanced Filter, AutoFilter, VBA, Macro, Web

 
109. How to Remove Smart Tags from a Workbook This article explains how to remove smart tags from a... KB#288958. Keywords: AutoCorrect, Smart Tag

 
110. How to sort Cells Without Sorting Linked Cells in Excel 2000 This step-by-step article shows you how to sort linked data without affecting cells that are linked to the original data. If you create links to a range of cells and you sort the data to which the cells are linked, the linked cells are updated... KB#212079. Keywords: Sorting, Formulas, Links, OFFSET

 
111. Error Message: This Operation Is Attempting to Change a Filtered Range on Your Worksheet and Cannot Be Completed When you try to create a list from a filtered range of cells in Excel 2003, you receive the following error message: This operation is attempting to change a filtered range on your worksheet and cannot be completed. To complete this... KB#818244. Keywords: Query, Errors, Error Message

 
112. Excel Uses Microsoft OLE DB Provider for OLAP Services 8.0 for All OLAP Queries If you configure an Online Analytical Processing (OLAP) query to use the older version of the Microsoft OLE DB Provider for OLAP Services, Excel 2002 continues to use the Microsoft OLE DB Provider for OLAP Services 8.0. This article describes how... KB#286510. Keywords: Query, OLE, Registry, DB

 
113. How to Configure Excel 2003 to Use Microsoft OLE DB Provider for OLAP Services 7.0 If you configure a Microsoft Online Analytical Processing (OLAP) query to use the older version of Microsoft OLE DB Provider for OLAP Services, Excel 2003 continues to use Microsoft OLE DB Provider for OLAP Services 8.0. This article describes how... KB#827295. Keywords: Query, OLE, Registry, DB

 
114. How to Use Wildcards in Parameter Queries This article includes examples that show you how to use wildcards in parameter queries in Microsoft... KB#164117. Keywords: Criteria, Query, Wildcard

 
115. How to use RAND() to Generate Randomly Distributed Integers in Excel 2000 This step-by-step article describes how you can use the RAND function to generate randomly sorted, uniformly distributed integers in Microsoft Excel 2000. Excel does not include a built-in method for generating sets of randomly sorted,... KB#214090. Keywords: Sorting, RAND

 
116. Paste Rows Keywords: AutoFilter, Sorting, Errors, Printing, VBA, Macro, Web, Comments

 
117. How to Set the Descriptive Name of an Addin File In Microsoft Excel 2000, it is possible to create a descriptive name and descriptive text for an add-in file that is listed in the Add-Ins dialog... KB#214014. Keywords: Add-In, Analysis ToolPak, Query, VBA, Files

 
118. How to Extract an Exact Match of Text from a Database In Microsoft Excel, to filter records in a list by using the Advanced Filter command, the text to be matched must be preceded by an equal () sign in the criteria range. Use the following format in the appropriate criteria cell ' text string... KB#214100. Keywords: Advanced Filter, Criteria, Sorting, Sales, Format, MONTH

 
119. How to Sort Cells Without Sorting Linked Cells If you create links to a range of cells and you sort the data to which the cells are linked, the linked cells are updated with the sorted data. This article contains an example that sorts linked data without affecting cells that are linked to... KB#174537. Keywords: Sorting, Formulas, Links, OFFSET

 
120. Displaying AutoFilter criteria Excel's AutoFilter feature definitely ranks right up there when it comes to handy tools. This feature, which you access with the Data, Filter, AutoFilter command, works with a range of cells set up as a database or list. When AutoFiltering is turned on, the row headers display drop-down arrows that... Keywords: Charts, AutoFilter, Criteria, Format, Errors, Formulas, Printing, VBA

 
121. How to Remove Duplicate Records or Create List of Unique Records When Microsoft Excel imports or combines many records, it may create duplicates. This article describes how to create a compiled list of unique... KB#262277. Keywords: Advanced Filter, Criteria, Sorting

 
122. Errors Quitting Office Apps with IntranetWare Network When you quit a Microsoft Office 2000 program that runs on a Novell IntranetWare network, you may receive the following message This connection must be maintained for Novell Directory Services use on tree tree name . It can only be removed... KB#206734. Keywords: Query, DLL, Third-Party, Errors

 
123. Locking cells to protect critical data One easy way to achieve this is to make A16 and B2 the only unlocked cells on the worksheet. By default, all cells are locked. You can use Format - Cells - Protection - uncheck the Locked checkbox to unlock the cells.In Excel 2000 and earlier you can simply protect the worksheet with Tools -... Keywords: Sorting, Format, Macro, VBA, Web

 
124. Shortcut keys Great Shortcut Keys in Excel. Excel offers a number of shortcut keys. Here are a few of my favorites.Enter the same value in multiple cells: Select the entire range you want to fill. Type the value. Then, hit CTRL+ENTER.Put multi-line headings in a single row of cells to make insure sorting and... Keywords: Sorting, Sales, Formulas, Clipboard, VBA, Web

 
125. Elapsed Time Counting Techniques Keywords: Criteria, Days, Format, Web, Comments, OFFSET, COUNTIF

 
126. "Syntax Error (Missing Operator)..." Error Performing Query with a Date When you enter a date in the Criteria pane of your query, you receive an error message similar to the following Syntax error (missing operator) in query expression '( Table Name . Fieldname ts '100-11-16 00:00:00'})'. where Table Name is the name... KB#248222. Keywords: Criteria, Query, Errors, Error Message

 
127. How to Use Excel to Generate Reports for Mailbox Resources The Exchange Administrator program of Exchange 2000 can export mailbox or public folder resource reports to a .csv file. This article describes how to generate reports for your mailbox resources by using Microsoft Excel to work with an imported... KB#328827. Keywords: Sorting, Export, File Name, Window

 
128. Excel -- Data Validation -- Custom Validation Criteria Examples Custom Validation Criteria Examples Prevent Duplicates Prevent duplicate entries in a range on the worksheet. In this example, Employee Numbers will be entered in cells B3:B10. Limit the Total Prevent entry of a value that will cause a range to exceed a set total. In this example, the total budget... Keywords: Criteria, Dates, Formulas, Validation, COUNTIF, TRIM, WEEKDAY

 
129. Multiply Hours by Rate Jeff: I have figured out how to add Hour:Min, but now that I have that total - How do I multiply that times Dollars per hour?If the total is in A10, and dollars per hour is in A11, then use =A10*24*A11Richard: I have a macro to sort a worksheet. It works when the sheet is unprotected, but not when... Keywords: Sorting, Download, Macro, VBA, Web

 
130. Rational Databases in Excel Jeff: I have figured out how to add Hour:Min, but now that I have that total - How do I multiply that times Dollars per hour?If the total is in A10, and dollars per hour is in A11, then use =A10*24*A11Richard: I have a macro to sort a worksheet. It works when the sheet is unprotected, but not when... Keywords: Sorting, Download, Macro, VBA, Web

 
131. Spreadsheet Programs Jeff: I have figured out how to add Hour:Min, but now that I have that total - How do I multiply that times Dollars per hour?If the total is in A10, and dollars per hour is in A11, then use =A10*24*A11Richard: I have a macro to sort a worksheet. It works when the sheet is unprotected, but not when... Keywords: Sorting, Download, Macro, VBA, Web

 
132. Worksheet Protection after Sorting Jeff: I have figured out how to add Hour:Min, but now that I have that total - How do I multiply that times Dollars per hour?If the total is in A10, and dollars per hour is in A11, then use =A10*24*A11Richard: I have a macro to sort a worksheet. It works when the sheet is unprotected, but not when... Keywords: Sorting, Download, Macro, VBA, Web

 
133. Excel select Random numbers from a column There are several approaches to this. Here is one way to accomplish this. Let's say that your 50 numbers are in cells A2:A51.Highlight cells B2:B51Enter the formula =RAND() and hit Ctrl+Enter. This will enter the formula in all 50 cells at onceCopy B2:B51 with Ctrl+cPasteSpecial the formulas into... Keywords: Rank, Sorting, Formulas, VBA, Web, RAND

 
134. Formulas Calculate Incorrectly After Changing Worksheet Name When you recalculate your worksheet, some formulas may calculate incorrect... KB#248174. Keywords: Sorting, Formulas, VBA

 
135. Ranking without repetitive ranks Here's a question I see often. "I'm trying to use the RANK function to sort the values in Column A, but, the problem arises when I have two or more cells with the same value. Then, the RANK also repeats some positions. For example, if I have, 50, 60, 62, 62, 67 in Column A I would get 5,4,2,2,1 in... Keywords: Rank, Sorting, Formulas, VBA, Web, COUNTIF

 
136. Duplicate repeated entries in a list You've probably seen an Excel worksheet (like the sheet on the left, below) in which one entry in column A applies to several rows of data. Sort such a list and you get a real mess, because rows with empty cells in the sort column move to the top or bottom (depending on the sort order). When a list... Keywords: Charts, Sorting, Format, Formulas, Printing

 
137. Pivot Table Multiple Consolidation Example Use the multiple consolidation range feature of PivotTables to summarize accounting data arranged in columns by month. Summarize the PivotTable by quarter using the built-in PivotTable grouping feature. Use the multiple consolidation range feature of pivot tables to summarize accounting data... Keywords: Query, Download, Format, PivotTables

 
138. Dialog Box to Filter Data Use a button, dialog box and simple VBA macro to filter data on a worksheet. When you click the button and enter values for year and month, the list is automatically filtered. You can create an AdvancedFilter with a criteria range on a hidden worksheet. If you control the values in the hidden... Keywords: Criteria, Download, Format, Macro, VBA

 
139. Count AutoFiltered rows Q. When I use Excel's AutoFiltering, the status bar displays the number of qualifying rows. But for no apparent reason, that number often vanishes. How do I keep this number visible while I work? AutoFiltering a list hides rows that don't meet your filter criteria. After applying filtering... Keywords: Charts, Criteria, Download, Format, Formulas, Printing, SUBTOTAL

 
140. Importing a text file into a worksheet Excel users have long envied 1-2-3's ability to insert the contents of a text file into a worksheet. Until Excel 2000, all versions of Excel required you to open the text file separately, then copy and paste the data to your worksheet. Excel 2000 (and later) makes the process much easier. You might... Keywords: Charts, Query, Import Data, Format, Formulas, Printing, Text Files

 
141. Advanced VBA Tutorial Exporting Excel Data in Special Formats You can use a VBA procedure to export data in comma delimited format. Or, change the delimiting character to export the data separated by semicolons, etc Importing Text Files into Open Workbook You can use either of two Visual Basic for Applications methods... Keywords: Sorting, Export, Format, Arrays, VBA, Files, Text Files

 
142. PivotTables With Accounting Data This workbook demonstrates three pivot tables built using the multiple consolidation range option with source data in standard accounting format. The Pivot tables take advantage of Excel's ability to group dates in PivotTables. The powerful grouping capability of pivot tables can be used to bring... Keywords: Data Table, Dates, Download, Format, PivotTables

 
143. Comparison of Three Sort Methods Comparison of the time required to sort a Visual Basic for Applications Array using three methods. You can sort an array by transferring it to the worksheet and using Excel's built in sort. Another sorting method is the Visual Basic for Applications "Bubble Sort". Quick Sort, adapted from the... Keywords: Sorting, Download, Format, Arrays, VBA

 
144. How to filter out duplicate data Sure, just select the range where the duplicates are. Go to data, Advanced Filter, select "Copy to new location", leave the "Critiera" field empty, and make sure you have chekced "Unique Entries Only". MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty... Keywords: VBA, Web

 
145. Color Cells based on Criteria Keywords: Criteria, Colors, Fonts, VBA

 
146. Four Methods of Adding Totals to a List Example of using subtotals, array formulas and database formulas to add totals to a list of data on a worksheet You can add totals to a list of data on a worksheet in many ways. You can use an AutoFilter and sort and subtotal the data. Excel provides array formulas that can be used to process an... Keywords: AutoFilter, Criteria, Sorting, Download, Format, Arrays, Formulas, PivotTables

 
147. Increase the number of rows or columns? Q. How can I increase the number of rows or columns in a worksheet? In Excel, every workbook has 256 columns and 65,526 rows. Versions prior to Excel 97 have only 16,384. These values are fixed and cannot be changed. Despite what must amount to thousands of requests, Microsoft refuses to increase... Keywords: Charts, Query, Format, Formulas, Printing

 
148. Compare 3 Lists Conditionally "I have been racking my brains for the last week trying to do a conditional count between two or three lists and this pointed me in the right direction. Below is the modified formula.... Keywords: Query, Formulas, Links, Arrays, VBA, LEFT

 
149. Delete Duplicates Here is a quicky to spin down through a list and remove the duplicates. Be sure you list is sorted before beginning. Public CurRow, oZ, uY,... Keywords: Criteria, VBA

 
150. Inserting Automatic Page Breaks You can use the subtotal feature to add a page break after each change in a sort key. For example, your can subtotal on vendor as shown in the figure below and Excel will add a page break for each vendor value. Use menu option Data, Subtotals and choose the Page Break Between Groups option. ; You... Keywords: Sorting, Arrays, VBA

 

<< (Page 2)  Previous              Next  (Page 4) >>