Excel Articles

Article / Updated 08-28-2023

In Excel 2013, you can insert and delete individual cells or even ranges that don’t neatly correspond to entire rows or columns. When you do so, the surrounding cells shift. In the case of an insertion, cells move down or to the right of the area where the new cells are being inserted. In the case of a deletion, cells move up or to the left to fill in the voided space. Deleting a cell is different from clearing a cell’s content, and this becomes apparent when you start working with individual cells and ranges. When you clear the content, the cell itself remains. When you delete the cell itself, the adjacent cells shift. When shifting cells, Excel is smart enough that it tries to guess which direction you want existing content to move when you insert or delete cells. If you have content immediately to the right of a deleted cell, for example, Excel shifts it left. If you have content immediately below the deleted cell, Excel shifts it up. You can still override that, though, as needed. In the following exercise, you insert and delete cells. In the Lesson 5 Mortgage file from the preceding exercise, select A1:A6 and then choose Home→Delete. Excel guesses that you want to move the existing content to the left, and it does so. Click cell A1, and choose Home→Insert. Excel guesses that you want to move the existing content down, which is incorrect. The content in column B is off by one row, as shown in this figure. Press Ctrl+Z to undo the insertion; then from the Home tab, click the down arrow to the right of the Insert button and choose Insert Cells. The Insert dialog box opens, as shown. Select Shift Cells Right and then click OK. A new cell A1 is inserted, and the previous A1 content moves into B1. Save the changes to the workbook.

Article / Updated 06-07-2023

To share Excel 2019 workbooks from your OneDrive, you follow these steps: Open the workbook file you want to share in Excel 2019 and then click the Share button at the far right of the row with the Ribbon. If you’ve not yet saved the workbook on your OneDrive, a Share dialog box appears inviting you to upload the workbook file to OneDrive. Once you have clicked the OneDrive button and the file is uploaded to the cloud, the Share dialog box changes into the Send Link dialog box (similar to the one shown in the figure) where you specify the people with whom to share the file. Begin typing the name or e-mail address of the first person with whom you want to share the workbook in the text box with the insertion point. When Excel finds a match to the person’s name in your Outlook address book or verifies the e-mail address you entered, click the button below this text box to add this recipient. (Optional) Click the Anyone with This Link Can Edit drop-down button to open the Link Settings dialog box where you can modify the people for whom the link works, deny editing privileges to those with whom you share the file, and/or set an expiration date after which the link is no longer operational before clicking the Apply button. By default, Excel 2019 creates a sharing link that enables anyone who can access the workbook file online access to the file even when they are not logged into Office 365 or OneDrive. To restrict access to only coworkers in your company who are logged into Office 365, click the People in <organization> option (where organization is the name of your company as in People in Mind Over Media, the name of my company). To restrict the file sharing to only those to whom you’ve given prior access to the workbook file or its folder on your SharePoint site, click the People with Existing Access option. To create a sharing link that only particular people can use, click the Specific People option before you click the Apply button. Then, in the Send Link dialog box, click the ellipsis (…) to the right of the Send Link title and click Manage Access on the drop-menu to open the Permissions dialog box where you select the names of the people with whom to share the workbook file before you click the back arrow button to return to the Send Link dialog box. By default, Excel allows the people with whom you share your workbooks to make editing changes to the workbook that are automatically saved on your OneDrive. If you want to restrict your recipients to reviewing the data without being able to make changes, be sure to click the Allow Editing check box to remove its check mark before you click Apply. If you wish to set an expiration date after which the sharing link is no longer operational, click the Set Expiration Date button to open the pop-up calendar where you select an expiration date by clicking it in the calendar. After selecting the expiration date, click somewhere in the dialog box to close the pop-up calendar and enter the date in the Link Settings dialog box. (Optional) Click the Add a Message text box and type any personal message that you want to incorporate as part of the e-mail with the generic invitation to share the file.By default, Excel creates a generic invitation. After adding all the recipients with whom you wish to share the workbook file in this manner, click the Send button in the Send Link pane.As soon as you click this Share button, Excel e-mails the invitation to share the workbook to each of the recipients. All the people with whom you share a workbook receive an e-mail message containing a hyperlink to the workbook on your OneDrive. When they follow this link (and sign into the site if this is required), a copy of the workbook opens on a new page in their default web browser using the Excel Online web app. If you’ve given the user permission to edit the file, the web app contains an Edit Workbook drop-down button. When the coworkers with whom you’ve shared the workbook click this button in Excel Online, they have a choice between choosing the Edit in Excel or Edit in Excel Online option from its drop-down menu. When the user chooses Edit in Excel, the workbook is downloaded and opened in his version of Excel. When the user chooses Edit in Excel Online, the browser opens the workbook in a new version of the Excel Online, containing Home, Insert, Data, Review, and View tabs, each with a more limited set of command options than Excel 2019, which you can use in making any necessary changes and which are automatically saved to workbook on the OneDrive when you close Excel Online. While sharing a workbook with the default Anyone Can Edit option, all changes made by the people with whom you’ve shared the workbook are automatically saved by the AutoSave feature. If you happen to have the workbook open in Excel 2019 on your computer with the same worksheet displayed, their editing changes automatically appear in your worksheet (in as close to real time as the speed of your Internet access provides). Likewise, all the editing changes that you make to the workbook in Excel 2019 are automatically updated in their workbooks in Excel Online. Microsoft refers to this process as co-authoring. If a questionable editing change appears in your worksheet when co-authoring with a coworker, add a comment to the cell containing the edit-in-question (Review-->New Comment) that communicates your reservations about the change they made. A small balloon then appears above the cell where you made the comment in the user’s worksheet in Excel Online. When the coworker clicks this balloon, Excel Online displays the text of your comment calling into question their edit in a Comments task pane. They can then reply to your reservations by typing their explanation for the change in the same comment in this task pane and then updating it in your workbook by clicking the Post button or they can just go ahead and make any necessary updates reflecting your reservations directly in the worksheet in Excel Online.

Article / Updated 06-07-2023

Excel can help you make all sorts of calculations. Here's a selection of Excel's statistical worksheet functions. Each one returns a value into a selected cell. Check out these functions for central tendency and variability. Function What it calculates AVERAGE Mean of a set of numbers AVERAGEIF Mean of a set of numbers that meet a condition AVERAGEIFS Mean of a set of numbers that meet one or more conditions HARMEAN Harmonic mean of a set of positive numbers GEOMEAN Geometric mean of a set of positive numbers MODE.SNGL Mode of a set of numbers MEDIAN Median of a set of numbers VAR.P Variance of a set of numbers considered to be a population VAR.S Variance of a set of numbers considered to be a sample STDEV.P Standard deviation of a set of numbers considered to be a population STDEV.S Standard deviation of a set of numbers considered to be a sample STANDARDIZE A standard score based on a given mean and standard deviation These handy functions for relative standing can also be very useful. Function What it calculates RANK.EQ Rank of a number in a set of numbers. If more than one number has the same rank, it returns the top rank of those numbers. RANK.AVG Rank of a number in a set of numbers. If more than one number has the same rank, it returns their average. PERCENTRANK.INC Rank of a number in a set of numbers, expressed as a percent of the numbers it's greater than or equal to. PERCENTRANT.EXC Rank of a number in a set of numbers, expressed as a percent of the numbers it's greater than. PERCENTILE.INC The indicated percentile in a set of numbers, in terms of "greater than or equal to." PERCENTILE.EXC The indicated percentile in a set of numbers, in terms of "greater than." QUARTILE.INC The 1st, 2nd, 3rd, or 4th quartile of a set of numbers, in terms of "greater than or equal to." QUARTILE.EXC The 1st, 2nd, 3rd, or 4th quartile of a set of numbers, in terms of "greater than." These functions for correlation and regression are also good ones to know. Function What it Calculates CORREL Correlation coefficient between two sets of numbers PEARSON Same as CORREL. (Go figure!) RSQ Coefficient of determination between two sets of numbers (square of the correlation coefficient) SLOPE Slope of a regression line through two sets of numbers INTERCEPT Intercept of a regression line through two sets of numbers STEYX Standard error of estimate for a regression line through two sets of numbers

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.