Excel Articles

Step by Step / Updated 09-16-2022

You can add text comments to particular cells in an Excel 2013 worksheet. Comments act kind of like electronic pop-up versions of sticky notes. For example, you can add a comment to yourself to verify a particular figure before printing the worksheet or to remind yourself that a particular value is only an estimate. In addition to using notes to remind yourself of something you’ve done or that remains to be done, you can also use a comment to mark your current place in a large worksheet. You can then use the comment’s location to quickly find your starting place the next time you work with that worksheet. To add a comment to a cell, click on the cell to which you want to add the comment and follow these steps:

Article / Updated 08-01-2022

Excel’s AutoFilter feature makes filtering out unwanted data in a data list as easy as clicking the AutoFilter button on the column on which you want to filter the data and then choosing the appropriate filtering criteria from that column’s drop-down menu. If you open a worksheet with a data list and you don’t find Excel’s AutoFilter buttons attached to each of the field names at the top of the list, you can display them simply by positioning the cell pointer in one of the cells with the field names and then clicking the Filter command button on the Ribbon’s Data tab or pressing Ctrl+Shift+L or Alt+AT. The filter options on a column’s AutoFilter drop-down menu depend on the type of entries in the field. On the drop-down menu in a column that contains only date entries, the menu contains a Date Filters option to which a submenu of the actual filters is attached. On the drop-down menu in a column that contains only numeric entries (besides dates) or a mixture of dates with other types of numeric entries, the menu contains a Number Filters option. On the drop-down menu in a column that contains only text entries or a mixture of text, date, and other numeric entries, the menu contains a Text Filters option. Doing basic filtering in Excel 2019 by selecting specific field entries In addition to the Date Filters, Text Filters, or Number Filters options (depending on the type of field), the AutoFilter drop-down menu for each field in the data list contains a list box with a complete listing of all entries made in that column, each with its own check box. At the most basic level, you can filter the data list by clearing the check box for all the entries whose records you don’t want to see in the list. This kind of basic filtering works best in fields such as City, State, or Country, which contain many duplicates, so you can see a subset of the data list that contains only the cities, states, or countries you want to work with at the time. The easiest way to perform this basic type of filtering on a field is to first deselect the check box in front of the (Select All) option at the top of the field’s list box to clear the check boxes, and then select each of the check boxes containing the entries for the records you do want displayed in the filtered data list. After you finish selecting the check boxes for all the entries you want to keep, you click OK to close the AutoFilter drop-down menu. Excel then hides rows in the data list for all records except for those that contain the entries you just selected. The program also lets you know which field or fields have been used in the filtering operation by adding a cone filter icon to the column’s AutoFilter button. To restore all the records to the data list, you can remove the filtering by clicking the Clear command button in the Sort & Filter group of the Data tab of the Ribbon or by pressing Alt+AC. When doing this basic kind of list filtering, you can select specific entries from more than one field in this list. The following image illustrates this kind of situation. Here, I want only the employees in the company who work in the Engineering and Information Services departments in the Chicago and Seattle offices. To do this, I selected only the Engineering and Information Services entries in the list box on the Dept field’s AutoFilter drop-down menu and only the Chicago and Seattle entries in the list box on the Location field’s AutoFilter drop-down menu. As you can see above, after filtering the Employee data list so that only the records for employees in either the Engineering or Information Services department in either the Chicago or Seattle office locations are listed, Excel adds the cone filter icon to the AutoFilter buttons on both the Dept and Location fields in the top row, indicating that the list is filtered using criteria involving both fields. Keep in mind that after filtering the data list in this manner, you can then copy remaining records that make up the desired subset of the data list to a new area in the same worksheet or to a new sheet in the workbook. You can then sort the data (by adding AutoFilter buttons with the Filter command button on the Data tab), chart the data, analyze the data, or summarize the data in an Excel pivot table. Using the Text Filters options in Excel 2019 The AutoFilter drop-down menu for a field that contains only text or a combination of text, date, and numeric entries contains a Text Filters option that when you click or highlight displays its submenu containing the following options: Equals: Opens the Custom AutoFilter dialog box with the Equals operator selected in the first condition. Does Not Equal: Opens the Custom AutoFilter dialog box with the Does Not Equal operator selected in the first condition. Begins With: Opens the Custom AutoFilter dialog box with the Begins With operator selected in the first condition. Ends With: Opens the Custom AutoFilter dialog box with the Ends With operator selected in the first condition. Contains: Opens the Custom AutoFilter dialog box with the Contains operator selected in the first condition. Does Not Contain: Opens the Custom AutoFilter dialog box with the Does Not Contain operator selected in the first condition. Custom Filter: Opens the Custom AutoFilter dialog box where you can select your own criteria for applying more complex AND or conditions. Using the Date Filters options in Excel 2019 The AutoFilter drop-down menu for a field that contains only date entries contains a Date Filters option that when you click or highlight displays its submenu containing the following options: Equals: Opens the Custom AutoFilter dialog box with the Equals operator selected in the first condition. Before: Opens the Custom AutoFilter dialog box with the Is Before operator selected in the first condition. After: Opens the Custom AutoFilter dialog box with the Is After operator selected in the first condition. Between: Opens the Custom AutoFilter dialog box with the Is After or Equal To operator selected in the first condition and the Is Before or Equal To operator selected in the second AND condition. Tomorrow: Filters the data list so that only records with tomorrow’s date in this field are displayed in the worksheet. Today: Filters the data list so that only records with the current date in this field are displayed in the worksheet. Yesterday: Filters the data list so that only records with yesterday’s date in this field are displayed in the worksheet. Next Week: Filters the data list so that only records with date entries in the week ahead in this field are displayed in the worksheet. This Week: Filters the data list so that only records with date entries in the current week in this field are displayed in the worksheet. Last Week: Filters the data list so that only records with date entries in the previous week in this field are displayed in the worksheet. Next Month: Filters the data list so that only records with date entries in the month ahead in this field are displayed in the worksheet. This Month: Filters the data list so that only records with date entries in the current month in this field are displayed in the worksheet. Last Month: Filters the data list so that only records with date entries in the previous month in this field are displayed in the worksheet. Next Quarter: Filters the data list so that only records with date entries in the three-month quarterly period ahead in this field are displayed in the worksheet. This Quarter: Filters the data list so that only records with date entries in the current three-month quarterly period in this field are displayed in the worksheet. Last Quarter: Filters the data list so that only records with date entries in the previous three-month quarterly period in this field are displayed in the worksheet. Next Year: Filters the data list so that only records with date entries in the calendar year ahead in this field are displayed in the worksheet. This Year: Filters the data list so that only records with date entries in the current calendar year in this field are displayed in the worksheet. Last Year: Filters the data list so that only records with date entries in the previous calendar year in this field are displayed in the worksheet. Year to Date: Filters the data list so that only records with date entries in the current year up to the current date in this field are displayed in the worksheet. All Dates in the Period: Filters the data list so that only records with date entries in the quarter (Quarter 1 through Quarter 4) or month (January through December) that you choose from its submenu are displayed in the worksheet. Custom Filter: Opens the Custom AutoFilter dialog box where you can select your own criteria for more complex AND or conditions. When selecting dates for conditions using the Equals, Is Before, Is After, Is Before or Equal To, or Is After or Equal To operator in the Custom AutoFilter dialog box, you can select the date by clicking the Date Picker button (the one with the calendar icon) and then clicking the specific date on the drop-down date palette. When you open the date palette, it shows the current month and the current date selected. To select a date in an earlier month, click the Previous button (the one with the triangle pointing left) until its month is displayed in the palette. To select a date in a later month, click the Next button (the one with the triangle pointing right) until its month is displayed in the palette. Using the Number Filters options in Excel 2019 The AutoFilter drop-down menu for a field that contains only number entries besides dates or a combination of dates and other numeric entries contains a Number Filters option that when you click or highlight it displays its submenu containing the following options: Equals: Opens the Custom AutoFilter dialog box with the Equals operator selected in the first condition. Does Not Equal: Opens the Custom AutoFilter dialog box with the Does Not Equal operator selected in the first condition. Greater Than: Opens the Custom AutoFilter dialog box with the Is Greater Than operator selected in the first condition. Greater Than or Equal To: Opens the Custom AutoFilter dialog box with the Is Greater Than or Equal To operator selected in the first condition. Less Than: Opens the Custom AutoFilter dialog box with the Is Less Than operator selected in the first condition. Less Than or Equal To: Opens the Custom AutoFilter dialog box with the Is Less Than or Equal to operator selected in the first condition. Between: Opens the Custom AutoFilter dialog box with the Is Greater Than or Equal To operator selected in the first condition and the Is Less Than or Equal To operator selected in the second AND condition. Top 10: Opens the Top 10 AutoFilter dialog box so that you can filter the list to just the ten or so top or bottom values or percentages in the field. Above Average: Filters the data list to display only records where the values in the field are greater than the average of the values in this field. Below Average: Filters the data list to display only records where the values in the field are less than the average of the values in this field. Custom Filter: Opens the Custom AutoFilter dialog box where you can select your own criteria for more complex AND or conditions. Making it to the Top Ten! The Top Ten option on the Number Filters option’s submenu enables you to filter out all records except those whose entries in that field are at the top or bottom of the list by a certain number (10 by default) or in a certain top or bottom percent (10 by default). Of course, you can only use the Top Ten item in numerical fields and date fields; this kind of filtering doesn’t make any sense when you’re dealing with entries in a text field. When you click the Top Ten option on the Number Filters option’s submenu, Excel opens the Top 10 AutoFilter dialog box where you can specify your filtering criteria. By default, the Top 10 AutoFilter dialog box is set to filter out all records except those whose entries are among the top ten items in the field by selecting Top in the drop-down list box on the left, 10 in the middle combo box, and Items in the drop-down list box on the right. If you want to use these default criteria, you simply click OK in the Top 10 AutoFilter dialog box. The image below shows you the sample employee data list after using the Top 10 Items AutoFilter to display only the records with the top ten salaries in the data list. You can also change the filtering criteria in the Top 10 AutoFilter dialog box before you filter the data. You can choose between Top and Bottom in the leftmost drop-down list box and between Items and Percent in the rightmost one. You can also change the number in the middle combo box by clicking it and entering a new value or using the spinner buttons to select one. Filtering an Excel data list on a field’s font and fill colors or cell icons Just as you can sort a data list using the font or fill color or cell icons that you’ve assigned with the Conditional Formatting feature to values in the field that are within or outside of certain parameters, you can also filter the list. To filter a data list on a font color, fill color, or cell icon used in a field, you click its AutoFilter button and then select the Filter by Color option from the drop-down menu. Excel then displays a submenu from which you choose the font color, fill color, or cell icon to use in the sort: To filter the data list so that only the records with a particular font color in the selected field — assigned with the Conditional Formatting Highlight Cell Rules or Top/Bottom Rules options — appear in the list, click its color swatch in the Filter by Font Color submenu. To filter the data list so that only the records with a particular fill color in the selected field — assigned with the Conditional Formatting Highlight Cell Rules, Top/Bottom Rules, Data Bars, or Color Scales options — appear in the list, click its color swatch in the Filter by Font Color submenu. To filter the data list so that only the records with a particular cell icon in the selected field — assigned with the Conditional Formatting Icon Sets options — appear in the list, click the icon in the Filter by Cell Icon submenu.

Cheat Sheet / Updated 04-20-2022

As an integral part of the Ribbon interface used by the major applications included in Microsoft Office 2010, Excel gives you access to hot keys that can help you select program commands more quickly. As soon as you press the Alt key, Excel displays the mnemonic letter choices on the various tabs and command buttons on the Ribbon. Then, simply press the mnemonic (or not-so-mnemonic) letters to perform a particular task.

Cheat Sheet / Updated 04-18-2022

At first glance, you might have trouble making sense of the many menus, tabs, columns, and rows of the Excel 2010 user interface. This Cheat Sheet will help you navigate your way by showing you keystrokes for moving the cell cursor to a new cell, simple rules of data-entry etiquette, common causes of some formula error values, and a quick list of the best Excel 2010 features.

Cheat Sheet / Updated 03-28-2022

In Microsoft Excel 2019, you can use the keyboard to select cells and access hot key sequences that can greatly speed up the process of selecting program commands. Excel 2019 also makes it easy to set regional dates, share workbooks saved on your OneDrive, and add SmartArt graphics to your worksheets. In Excel 2019, you also can sort multiple fields in a data list, create a new pivot table, or add a description to a user-defined function. This Cheat Sheet is a handy reference to these Excel functions.

Cheat Sheet / Updated 03-21-2022

At first glance, you might have trouble making sense of the many menus, tabs, columns, and rows of the Excel 2016 user interface. However, you can figure out what you're doing by using keystrokes to move the cell cursor to a new cell, following simple rules of data-entry etiquette, discovering common causes of some formula error values, and a reading a quick list of the best Excel 2016 features.

Cheat Sheet / Updated 03-14-2022

At first glance, you might have a little trouble making sense of the Excel 2019 worksheet window with its many components. Just keep in mind that when you’re unsure of the purpose of a particular command button displayed in the Excel worksheet window, all you have to do is to position the mouse pointer on that button’s icon to have Excel display the button’s name, its shortcut keys (if it has them), and, often, a short description of the button’s function.

Cheat Sheet / Updated 03-10-2022

Companies and organizations are always interested in business intelligence — raw data that can be turned into actionable knowledge. This need for business intelligence manifests itself in many forms. Dashboards are reporting mechanisms that deliver business intelligence in a graphical form. Most data analysis benefits from a spreadsheet, so Excel is inherently part of any business-intelligence tool portfolio. This Cheat Sheet provides some useful information and tips for working with Excel dashboards and reports.

Cheat Sheet / Updated 03-09-2022

Microsoft Power Query has its own formula language and its own functions. Here are a handful of Power Query functions that will help you better massage and transform your data. These functions should prove to be some of the most useful in terms of data transformation.

Cheat Sheet / Updated 03-01-2022

Excel shortcut keys allow you to perform certain tasks using only the keyboard, the idea being that you increase your efficiency when you limit the number of instances your hands have to move back and forth from the keyboard to the mouse. Getting in the habit of using these shortcut keys can help you work more efficiently when using the Visual Basic Editor.