Excel 2019 For Dummies book cover

Excel 2019 For Dummies

By: Greg Harvey Published: 10-23-2018

The bestselling Excel book on the market — now in a new edition covering the latest version of Excel!

Excel is the spreadsheet and data analysis tool of choice for people across the globe who utilize the Microsoft Office suite to make their work and personal lives easier. It is estimated that 1 in 7 people on the planet use Microsoft Office! If you’re one of them, and want to get up to speed on the latest changes in Excel, you’ve come to the right place.

Excel 2019 For Dummies has been updated to reflect the major changes and features made to Excel and covers everything you need to know to perform any spreadsheet task at hand. It includes information on creating and editing worksheets, formatting cells, entering formulas, creating and editing charts, inserting graphs, designing database forms, adding database records, using seek-and-find options, printing, adding hyperlinks to worksheets, saving worksheets as web pages, adding existing worksheet data to an existing webpage, pivot tables, pivot charts, formulas and functions, Excel data analysis, sending worksheets via e-mail, and so much more!

  • Get to know the new Excel interface  
  • Become a pro at the spreadsheet and data analysis tool that’s available as part of the Microsoft Office suite
  • Find time-tested and trusted advice from bestselling author and expert Greg Harvey
  • Use Excel to streamline your processes and make your work life easier than ever  before

Written by a bestselling author and seasoned educator, Excel 2019 For Dummies makes it easier than ever to get everything out of this powerful data tool.

Articles From Excel 2019 For Dummies

page 1
page 2
page 3
29 results
29 results
Excel 2019 For Dummies Cheat Sheet

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.

View Cheat Sheet
5 New Charts to Visually Display Data in Excel 2019

Article / Updated 08-26-2021

When you think "Excel charts," you probably envision a conservative-looking pie or column chart, the kind that financial wizards have been churning out since the 1990s. Excel still makes those charts (very well), but there are also many other options for presenting data in fresh ways that will make your audience sit up and take notice. This article explains some the new chart types in Excel 2019 and shows you how to create, format, and interpret them. Sunburst: More Than Just a Pretty Pie Chart Pie charts are great, right? They're easy to understand, as each slice makes up a part of the whole, and you can see at a glance the relative sizes of the slices. Where pie charts fall down is that they can illustrate only one data series. As you can see below, my data range had two series (Q1 and Q2), but only Q1 made it into the chart. A donut chart attempts to overcome that limitation by allowing different data series in the same chart, in concentric rings rather than slices. Donut charts can be hard to read, though, and there's no place to put the series names. For example, in the chart each ring represents a quarter (Q1, Q2) but there's no way to put the Q1 and Q2 labels on this type of chart. Furthermore, neither the pie nor the donut can chart hierarchical data. For example, suppose you have some data by continent that is further broken down by country. Sure, you could create a pie chart that shows each country, but you would lose the continent information. It also makes for a very messy looking chart because there are so many countries. A better solution is to use a sunburst chart, a multi-level hierarchical chart that's new to Excel 2019. At first glance, it looks like a donut chart, but rather than each ring representing a separate data series, each ring represents a level in the hierarchy. The center circle is the top level, and the further out you get, the further down you go in the hierarchy. To create a sunburst chart: Make sure that your data is arranged on the spreadsheet in a hierarchical way. Above, for example, the top level items in column A are put on top of the second-level items in column B. Select the entire data range, including all levels of labels. Click Insert → Hierarchy Chart → Sunburst. Format the chart as desired. For example, you might start with the Chart Styles gallery on the Chart Tools Design tab. Treemap: Round Becomes Rectangular Ever wish a pie chart was less, um, round? Okay, I'm attempting humor there, but the basic idea of a treemap chart is that it represents multiple data points as part of a whole, the way a pie or sunburst chart does, but it uses rectangles instead of slices or rings. A treemap chart can be simple, with one level of hierarchy as above, or it can be a rectangular version of a multi-level Sunburst chart if you create it with hierarchical data. To create a treemap chart: If you want the chart to be hierarchical, make sure that your data is arranged on the spreadsheet in a hierarchical way. Select the entire data range, including all levels of labels you want to include. Click Insert → Hierarchy Chart → Treemap. Format the chart as desired. (Don't Go Chasing) Waterfall Charts The waterfall chart type was added to Excel 2019 in response to user demand. Creating this type of chart in earlier Excel versions required a workaround that took a good 30 minutes or more. Now that waterfall is available as a chart type, you can make one with just a few clicks. A waterfall chart is good for showing the cumulative effect of positive and negative values, such as debits and credits to an account, presented in chronological order. For example, consider the data and chart below, which show the cash flow in a small business bank account. Notice that there are two bar colors on this chart: one for positive (blue) and one for negative (red/orange). The first several data points are income, positive numbers. The first one starts at $0 and goes up to $3,500. The next one starts where the previous one left off and goes up $200 more. The next one goes up $500 more. Then there are a series of expenses (negative numbers), and each one starts where the last one ended and goes downward. The chart continues point-by-point, where the rightmost bar's top aligns with $1950, which is the present balance. To create a waterfall chart: Make sure the data appears in the order in which it should appear in the chart. Reorder items if needed. Select the data to be charted, including the data labels. Click Insert → Insert Waterfall, Funnel, Stock, Surface, or Radar Chart. Click Waterfall. Format the chart as desired. Getting Statistical with Box and Whisker Charts A box and whisker chart provides a way to show the distributional characteristics of a pool of data. It summarizes the data pool by breaking it down into quartiles. (A quartile is one-quarter of the data points.) The middle two quartiles (2 and 3) are represented by a box, and the upper and lower quartiles (1 and 4) are represented by vertical lines called whiskers that protrude from the top and bottom of the box. The image below shows an example of a box and whisker chart that shows two data series. The overall variance in the data is represented by the entire area from the top of the upper whisker to the bottom of the lower one. The decimal number in the center of each box is the average value, and the horizontal dividing line in each box represents the median value. Comparing the two groups, it appears that the averages are similar, but that Group A has more variance, and group A's median score is higher. To create a box and whisker chart: Arrange the data sets in columns, with a separate column for each data set. Place text labels describing the data sets above the data. Select the data sets and their column labels. Click Insert → Insert Statistic Chart → Box and Whisker. Format the chart as desired. Box and whisker charts are visually similar to stock price charts, which Excel can also create, but the meaning is very different. For example, the image below shows an Open-High-Low-Close stock chart. The opening and closing prices are represented by the box. If the open price is greater than the closing price, the box is black; if the open price is less, the box is white. The upper whisker represents the daily high, and the lower whisker represents the daily low. Each stock chart sub-type has a very specific format and purpose, and it's not usually fruitful to try to use stock charts for anything other than their intended usage. Automatic Map Labelling with Filled Map Charts In the past, creating a map with numeric data on it has been very difficult in Excel. You had to insert a map graphic and then manually place text boxes over each area with numbers in the text boxes. Excel 2019 makes the process much easier with the filled map chart type. It recognizes countries, states/provinces, counties, and postal codes in data labels, and it displays the appropriate map and places the values in the appropriate areas on the map. To create a filled map: Enter some data that uses country or state names for data labels. Select the data and labels and then click Insert → Maps → Filled Map. Wait a few seconds for the map to load. Resize and format as desired. For example, you could apply one of the chart styles from the Chart Tools Design tab. To add data labels to the chart, choose Chart Tools Design → Add Chart Element → Data Labels → Show. Pouring Out Data with a Funnel Chart Let's look at one more new chart type: the funnel chart. A funnel chart shows each data point as a horizontal bar, with longer bars for greater values. The bars are all centered and stacked vertically. If you sort the data from largest to smallest, the overall effect looks like a funnel. You don't have to sort the data from largest to smallest; the bars can appear in any order. To create a funnel chart: Enter the labels and data. Put them in the order you want them to appear in the chart, from top to bottom. You can convert the range to a table to sort it more easily. Select the labels and data and then click Insert → Insert Waterfall, Funnel, Stock, Surface, or Radar Chart → Funnel. Format the chart as desired.

View Article
Inking Improvements to Get the Most Out of Office 2019 and Microsoft Surface

Article / Updated 11-19-2018

Office 2019 is the most touch-aware Office version to date, with features that can annotate Office files with comments and sketches, convert handwriting to text, and even convert freehand drawings to vector line art images. All these activities are collectively known as inking. Some of the inking features are available only if you have a touchscreen device such as a Microsoft Surface computer and an active stylus, but other features work with any touch-screen device (even if you are just using your finger to draw), and still others can be used on non-touch devices by "drawing" with an ordinary mouse or trackball. This article takes a look at the inking capabilities of Office 2019. What Are Microsoft Surface Devices? Microsoft Surface is Microsoft's own line of computers with touch-sensitive screens. There are several models, all sharing one thing in common: the focus on touch-screen input. A stylus is preferred for touch-screen entry, but you can also use fingers. Early models came with a stylus pen, but with current models, the stylus pen is sold separately. There are several models available, each targeting a different kind of user. The Surface, Surface Go, and Surface Pro are tablets with optional detachable keyboards and also optional stylus pens. They're designed to be used primarily as tablets. The Surface Laptop and Surface Book are notebooks PCs with detachable screens. They're designed to be used primarily as laptops. The Surface Studio is an all-in-one desktop that can convert to a digital drafting table. The Surface Hub is a touch-screen whiteboard. Stylus Pen Basics Surface devices, as well as many other devices with touch-sensitive screens, are designed to accept input from stylus pens. However, not all stylus pens are created equal. Some pens are positional, which means they detect the location of the tip in relation to the screen. Other pens are camera-based, using digital-enabled paper to detect where the stylus touches the writing surface. Accelerometer pens detect the pen movement and the contact with the writing surface. The type of pen that works with the latest Microsoft Surface products is an active pen, which is a more advanced technology than any of the others. An active pen has its own electronic components inside the pen that transmit data to the pen controller in the computer (or added on to the computer). Some active pens also have buttons on them that have special functions in certain applications. (For example, a pen might have a clickable eraser button on the top that advances the slide show in PowerPoint.) Microsoft Surface devices require an active pen if you want to take advantage of the full array of inking features in Office 2019 and other applications. That's not to say that other pens won't work at a basic level, though. If you are using the pen primarily as a mouse replacement, touching the screen to click or drag things, just about any pen will do. It's only when you get down to pen-specific functions such as creating shaded drawings or writing a lot of hand-written notes that the active pen becomes important. The company that makes the latest Surface-compatible active pens is called N-Trig. It was purchased by Microsoft in 2015. Here are some of the features that distinguish one pen from another: Pressure sensitivity: With a regular pen, the harder you push down, the darker or thicker the line is. Pressure-sensitive pens simulate that behavior. Latency: The delay between when you move the pen and when the results appear on your screen is the latency. Lower is better. Tilt functionality: Some real-life pens (pencils, crayons, markers, etc.) create a different type of line (wider, more shaded) if you tilt them as you draw. Pens with tilt-functionality simulate that feature. Can I Use a Stylus Pen with Other PCs and Tablets? Yes, as long as the device supports that type of stylus pen. You can use a Surface pen with a non-surface device that has a touch-screen that works with active pens, and you can use other brands of pens with a Microsoft Surface device. Compatibility is conditional though, depending on the generation and technology; you aren't going to be able to use the latest-technology pen with a 10-year-old tablet, and vice-versa. Now that you understand the basics of stylus pen technology, let's take a look at how Microsoft Office puts stylus pens to work. Adding the Draw Tab to the Ribbon In most Office 2019 applications, if you have a touch-sensitive screen, the Draw tab appears automatically on the Ribbon. If it doesn't for some reason, or if you don't have a touch-sensitive screen (but still want to try out the inking features using your mouse), you can enable it by doing the following: Click File → Options → Customize Ribbon. In the righthand pane, click to place a check mark next to Draw. Click OK. Inking Features Common to All Office Applications Some of the tools on the Draw tab are the same in every Office 2019 application. Excel has the fewest inking features available, so let's look at that app first. To draw, click the Draw button on the Drawing tab. This activates whichever pen, pencil, or highlighter is selected in the Pens group. When a pen is selected, it has a down arrow on its button. You can click this down arrow to open a menu to customize the tool (color, thickness, and so on). The color and thickness choices in Excel and PowerPoint are pretty basic, but in Word, you have a lot more choices, including sparkly glitter rainbow pens! To add a new pen to the Pens gallery, click Add Pen and then choose Pen, Pencil, or Highlighter as desired. A new pen is added to the gallery with a down arrow on it so that you can customize it. The Eraser button changes to an eraser so that whatever you touch or drag across is erased. The Ink to Shape button enables you to draw a shape and have it immediately converted into a vector-based line art shape, similar to the shapes you would draw using the Insert → Shapes command. It doesn't work on existing shapes; you have to select the command and then draw the shape. It also doesn't work with shapes more complex than a hexagon. The Lasso Select button enables you to draw a shape around the drawing parts to include in a selection. This enables you to select multiple drawings at once. This tool is not available in Word. The Ink to Math button enables you to write a math formula using a stylus pen (or your finger) and have it converted to an Equation Editor math formula. When you click Ink to Math, a math Input Control window opens. You can then write your math in the yellow shaded box. After you click OK, the converted formula appears as a floating object and the Equation Tools Design tab appears to enable further editing. Ink Replay recreates the process of drawing your inked marks in a video clip. It only works for inked drawings, not for Ink to Shape or Ink to Math drawings. Inking Features in Word In addition to the basic tool set covered in the preceding section, Word 2019 has a couple of unique inking features. Ink Editor The Ink Editor enables you to edit typed documents using your stylus pen. This feature can come in handy if you need to edit documents when your device is in tablet mode (no hardware keyboard available), for example. To try out the Ink Editor, do the following: Type some text in a Word document. Click Draw → Ink Editor to enable the feature. Click the bottom part of the Ink Editor button to open a menu and click Ink Gesture Help. A Help pane opens explaining the basic editing gestures you can use; this is a great cheat sheet as you begin learning. Click the Draw button and then click any of the pens in the Pens gallery. Use the pen to draw a horizontal line straight through some text that you want to delete, or draw a squiggly line through it that crosses it out. Draw a circle around a word with the pen. The word becomes selected. Try out several of the other editing marks in the Help pane. When you're done, click Learn more about ink gestures at the bottom of the Help pane for more information. Close the Help pane. Drawing Canvas The Drawing Canvas creates a rectangular box within a document where you can draw. Sure, you can draw anywhere on a document, but the drawing tends to get intertwined with the document text. By creating a drawing canvas, you localize the drawing to one place, and you can work with the entire canvas as a single object. The Drawing Canvas is also a great place to try out the special drawing capabilities of an active stylus pen. Try, for example, varying the angle and pressure of the pen for interesting drawing effects. Inking Features in PowerPoint While PowerPoint doesn't have the glitter rainbow ink that Word does, it does have a couple of very good features: a drawing ruler and an Ink to Text feature. Drawing Ruler Because PowerPoint is the go-to Microsoft Office application for creating simple graphics (because of the large, wide-open canvas areas that slides provide), it's not uncommon to want to draw freehand on a PowerPoint slide, either with the stylus pen or with the drawing tools (Insert → Shapes). The Ruler tool (on the Draw tab) gives you an on-screen ruler; you can then use your pen stylus to draw on the slide, and the line will align neatly with the ruler edge. You can even tilt the ruler to any angle and move it around. To try it out, follow these steps: Start a new PowerPoint presentation and change the layout of the slide to Blank (Home → Slide Layout → Blank). Click Draw → Ruler. Drag the ruler to the desired location. (Use the mouse, a finger, or the stylus.) Adjust the ruler's angle by rotating it with your fingers on the touchscreen, or if you have a wheel or ball on your mouse, by rotating that. Use three fingers to rotate the ruler by five-degree increments. Choose a pen and drag along the ruler's edge to draw a straight line. Click Draw → Ruler to turn the ruler off. The ruler disappears but the line you drew stays. You can also use the ruler to align individual items or groups of items. Select an item and drag it until its selection handle snaps to the ruler. Or select multiple items (use the Ctrl key) and then drag the set to snap to the ruler. Ink to Text Sometimes when you are giving a presentation, you might annotate it with some handwritten notes onscreen. Later you may want to go back and convert those notes to typed text. PowerPoint 2019 makes it easy to do that. Try it out: On a PowerPoint slide, use your stylus pen or finger to write some text. Click Draw → Ink to Text. The pointer turns into a lasso. Drag across the handwritten text to select it. The text is immediately converted into typed text.

View Article
How to Edit in Excel Online

Article / Updated 11-09-2018

Microsoft offers several Office Online web apps for Word, Excel, PowerPoint, and OneNote as part of your Windows account and OneDrive storage in the cloud. You can use Excel Online to edit worksheets saved on your OneDrive online right within your web browser. This comes in real handy for those occasions when you need to make last-minute edits to an Excel sheet but don’t have access to a device on which Excel 2019 is installed. As long as the device has an Internet connection and runs a web browser that supports Excel Online (such as Internet Explorer on a Surface Pro tablet or even Safari on a MacBook Pro), you can make eleventh-hour edits to the data, formulas, and even charts that are automatically saved in the Excel workbook file on your OneDrive. The great part about using Excel Online to edit a copy of your online workbook is that it runs successfully under the latest versions of Microsoft’s Edge Explorer as well as under the latest versions of many other popular web browsers, including Mozilla Firefox for Windows, Mac, and Linux as well as Macintosh’s Safari web browser on the iMac and iPad. To edit a workbook saved on your OneDrive with the Excel Online, you follow these simple steps: Launch the web browser on your device that supports the Excel web app and then go to Office.live.com and sign in to your Windows account. The Microsoft Office Home web page welcomes you to your Office 365 account. Under Apps on this page, you see a bunch of buttons for each of the online apps. Click the Excel button under Apps. Excel Online display a Welcome to Excel screen in your web browser. This screen is somewhat similar to the Open screen in Excel 2019. Across the top of this screen, beneath the label New, a bunch of template thumbnails appear in a single row, starting with New Blank Workbook. Below the row of Excel templates, you see the following options for selecting the file you want to edit: Recent (the default) to list all the workbooks that have recently been uploaded to your OneDrive. Pinned to list just the workbook files that you’ve pinned (to pin a file, mouse over the name of the file in the Recent list and then click the push pin icon that appears after its filename). Shared with Me to list only those workbook files that have been shared with you. Discover to list shared workbooks that others are currently working on. Upload and Open to display a dialog box where you can select a local workbook file to upload and save on OneDrive for editing with Excel Online. Note that if the device on which you’re using Excel Online has Excel installed on it, the Excel Open dialog box will appear. Otherwise, the file management dialog box for the device’s operating system will appear (as in, File Explorer on a Windows machine and Finder on a Mac). If you can’t locate the workbook file you want to edit using these options, click the Search Apps, Documents, People, and Sites text box at the top of the Welcome to Excel screen and start typing its filename here. As you type, Excel Online will display a list of results matching the characters that you’ve entered. Locate the Excel workbook file you want to edit and then click its filename. As soon as you select the name of the workbook file to edit, Excel Online opens the workbook in a new tab in your web browser, in the Editing view complete with a File button and the Home, Insert, Data, Review, and View Ribbon tabs. You can then use the option buttons on the Home and Insert tabs (most of which are identical to those found on the Home and Insert tabs on the Excel 2019 Ribbon) to modify the layout or formatting of the data and charts on any of its sheets. You can use the options on the Data tab to recalculate your Excel workbook and sort data in ascending or descending order on its worksheets. You can also use the options on the Review add and display comments in the cells of the worksheets as well as options on the View tab to turn off Headings and Gridlines and switch back to Reading view. You can also add new data to the worksheets as well as edit existing data just as you do in Excel 2019. Note that if you open a workbook file in Excel Online that contains features that can’t be displayed in your browser, the file will open in a new tab without the Ribbon and a “There are some features in your workbook that we can’t show in the browser” alert will appear above the Worksheet area. To continue and edit a copy of the workbook file with Excel Online without the features that can’t be displayed, click the Edit Workbook drop-down button and then click the Edit in Browser option on the drop-down menu (if Excel is installed on your device and you want access to all the workbook’s features, click the Edit in Excel option instead). When you select the Edit in Browser option, an Edit a Copy dialog box appears. When you click the Edit a Copy button, a Save As dialog box appears and shows Editable appended to the original filename. After you click Save in this dialog box, the copy of the original workbook file opens in Excel Online in Editing view. When you’re finished editing the Excel workbook, click the Close button on your web browser’s tab to save your changes. If you want to save a copy under a new filename in the same folder on the OneDrive, click File → Save As and then click the Save As option to open a Save As dialog box where you can edit the filename that appears in the text box before you click its Save button. (Or select the Overwrite Existing Files check box if you want to save the changes under the same filename.) The Excel Online app is a whiz at making simple editing, formatting, and layout changes to your spreadsheet data and charts using common commands on its Home, Insert, and even Chart Tools tab (when a chart is selected). However, Excel Online has no support for graphic objects other than shapes and pictures saved to disk that you can insert with very limited formatting and editing options. For those times when you need more editing choices, you can open the workbook in a local copy of Excel (assuming that the device you’re using has some compatible version of Excel installed on it) by clicking the Edit in Excel command button on the Ribbon. Or, you can download a copy of the Excel workbook to your local office computer (where you do have Excel 2019 installed) by selecting File → Save As → Download a Copy and make the more advanced edits to this downloaded copy of the file after you get back to the office.

View Article
Excel 2019: How to Create Excel Macros to Automate Commands

Article / Updated 11-09-2018

Excel macros allow you to automate commands to simplify your tasks. Excel 2019 enables you to add an optional Developer tab to the Ribbon that contains its own Record Macro command button (among other command buttons that are very useful when doing more advanced work with macros). To add the Developer tab to the Excel 2019 Ribbon, follow these two steps: Click File → Options or press Alt+FT to open the Excel Options dialog box. Click Customize Ribbon, then select the Developer check box under Main Tabs in the Customize the Ribbon list box on the right side of the dialog box, and then click OK. Even if you don’t add the Developer tab to the Ribbon, the Excel 2019 Status bar contains a Record Macro (to the immediate right of the Ready status indicator), and the View tab of the Ribbon contains a Macros command button with a drop-down menu containing a Record Macro option. When you turn on the Excel macro recorder in the Record Macro dialog box — opened by clicking the Record Macro button on the Status bar (automatically added once you record your first macro), the Record Macro option on the Macros button’s drop-down menu (Alt+WMR), or even the Record Macro button on the Developer tab (Alt+LR) — the macro recorder records all your actions in the active worksheet or chart sheet when you make them. The Excel macro recorder doesn’t record the keystrokes or mouse actions that you take to accomplish an action — only the VBA code required to perform the action itself. This means that mistakes that you make while taking an action that you rectify won’t be recorded as part of the macro; for example, if you make a typing error and then edit it while the macro recorder is on, only the corrected entry shows up in the macro without the original mistakes and steps taken to remedy them. The Excel macros that you create with the macro recorder can be stored as part of the current workbook, in a new workbook, or in a special, globally available Personal Macro Workbook named PERSONAL.XLSB that’s stored in a folder called XLSTART on your hard drive. When you record an Excel macro as part of your Personal Macro Workbook, you can run that macro from any workbook that you have open. (This is because the PERSONAL.XLSB workbook is secretly opened whenever you launch Excel, and although it remains hidden, its macros are always available.) When you record macros as part of the current workbook or a new workbook, you can run those macros only when the workbook in which they were recorded is open in Excel. When you create a macro with the macro recorder, you decide not only the workbook in which to store the macro but also what name and shortcut keystrokes to assign to the macro that you are creating. When assigning a name for your macro, use the same guidelines that you use when you assign a standard range name to a cell range in your worksheet. When assigning a shortcut keystroke to run the macro, you can assign The Ctrl key plus a letter from A to Z, as in Ctrl+Q Ctrl+Shift and a letter from A to Z, as in Ctrl+Shift+Q You can’t, however, assign the Ctrl key plus a punctuation or number key (such as Ctrl+1 or Ctrl+/) to your macro. To see how easy it is to create a macro with the macro recorder, follow these steps for creating a macro that enters the company name in 12-point, bold type and centers the company name across rows A through E with the Merge and Center feature: Open the Excel workbook that contains the worksheet data or chart you want your macro to work with. If you’re building a macro that adds new data to a worksheet (as in this example), open a worksheet with plenty of blank cells in which to add the data. If you’re building a macro that needs to be in a particular cell when its steps are played back, put the cell pointer in that cell. Click Record Macro button on the Status bar or Alt+WMR or Alt+LR if you have added the Developer tab to the Ribbon. The Record Macro dialog box opens where you enter the macro name, define any keystroke shortcut, select the workbook in which to store the macro, and enter a description of the macro’s function. Replace the Macro1 temporary macro name by entering your name for the macro in the Macro Name text box. Remember that when naming a macro, you must not use spaces in the macro name and it must begin with a letter and not some number or punctuation symbol. For this example macro, you replace Macro1 in the Macro Name text box with the name Company_Name. Next, you can enter a letter between A and Z that acts like a shortcut key for running the macro when you press Ctrl followed by that letter key. Just remember that Excel has already assigned a number of Ctrl+letter keystroke shortcuts for doing common tasks, such as Ctrl+C for copying an item to the Clipboard and Ctrl+V for pasting an item from the Clipboard into the worksheet (see the Cheat Sheet for a complete list). If you assign the same keystrokes to the macro you’re building, your macro’s shortcut keys override and, therefore, disable Excel’s ready-made shortcut keystrokes. (Optional) Click the Shortcut key text box and then enter the letter of the alphabet that you want to assign to the macro. For this example macro, press Shift+C to assign Ctrl+Shift+C as the shortcut keystroke (so as not to disable the ready-made Ctrl+C shortcut). Next, you need to decide where to save the new macro that you’re building. Select Personal Macro Workbook on the Store Macro In drop-down list box to be able to run the macro anytime you like. Select This Workbook (the default) when you need to run the macro only when the current workbook is open. Select New Workbook if you want to open a new workbook in which to record and save the new macro. Click the Personal Macro Workbook, New Workbook, or This Workbook option on the Store Macro In drop-down list to indicate where to store the new macro. For this example macro, select the Personal Macro Workbook so that you can use it to enter the company name in any Excel workbook that you create or edit. Next, you should document the purpose and function of your macro in the Description list box. Although this step is purely optional, it is a good idea to get in the habit of recording this information every time you build a new macro so that you and your coworkers can always know what to expect from the macro when it’s run. (Optional) Click the Description list box and then insert a brief description of the macro’s purpose in front of the information indicating the date and who recorded the macro. Now you’re ready to close the Record Macro dialog box and start recording your macro. Click OK to close the Record Macro dialog box. The Record Macro dialog box closes, the square Stop Recording button appears on the Status bar, and the Record Macro option becomes Stop Recording on the Macros button’s drop-down menu and in the Code group on the Developer tab. On the Macros button’s drop-down menu on the Ribbon’s View tab and Code group on the Developer tab, you find a Use Relative References option. You click this drop-down menu item or command button when you want the macro recorder to record the Excel macro relative to the position of the current cell. For this example macro, which enters the company name and formats it in the worksheet, you definitely need to click the Use Relative References button before you start recording commands. Otherwise, you can use the macro only to enter the company name starting in cell A1 of a worksheet. (Optional) Click the Use Relative References option on the Macros button’s drop-down menu on the View tab or click the Use Relative References button on the Developer tab if you want to be able to play back the macro anywhere in the Excel sheet. Select the cells, enter the data, and choose the Excel commands required to perform the tasks that you want recorded just as you normally would in creating or editing the current worksheet, using the keyboard, the mouse, or a combination of the two. For the example macro, type the company name and click the Enter button on the Formula bar to complete the entry in the current cell. Next, click the Bold button and then click 12 on the Font Size drop-down list in the Font group on the Home tab. Finally, drag through cells A1:E1 to select this range and then click the Merge and Center command button, again on the Home tab. After you finish taking all the actions in Excel that you want recorded, you’re ready to shut off the macro recorder. Click the Stop Recording button on the Status bar or select Stop Recording option on the View or Developer tab on the Ribbon. The square Stop Recording button on the Status bar turns into a Record Macro button (with an icon showing a tiny worksheet with a circle in the left corner). This lets you know that the macro recorder is now turned off and no further actions will be recorded. After you finish recording your first macro in Excel 2019, the Record Macro button continues to appear on the Status bar whenever you use the program. This means that you can click or tap this button to open the Record Macro dialog box whenever you need to create new macros rather than having to select the Record Macro option on the View or Developer tab of the Ribbon, as described in the previous steps. Assigning Excel macros to the Ribbon and the Quick Access toolbar If you prefer, instead of running a macro by selecting it in the Macro dialog box or by pressing shortcut keys you assign to it, you can assign it to a custom tab on the Ribbon or a custom button on the Quick Access toolbar and then run it by clicking that custom button. To assign an Excel macro to a custom group on a custom Ribbon tab, you follow these steps: Click File → Options and then click the Customize Ribbon tab in the Excel Options dialog box (or press Alt+FTC). Excel displays the Customize Ribbon pane in the Excel Options dialog box. Click Macros in the Choose Commands From drop-down list box on the left. Excel lists the names of all the macros created, both those in the current workbook and those that are saved in the PERSONAL.XLSB workbook, in the Choose Commands From list box. Click the name of the custom group on the custom tab to which you want to add the macro in the Main Tabs list box on the right. If you haven’t already created a custom tab and group for the macro or need to create a new one, follow these steps: Click the New Tab button at the bottom of the Main Tabs list. Excel adds both a New Tab (Custom) and New Group (Custom) item to the Main Tabs list while at the same time selecting the New Group (Custom) item. Click the New Tab (Custom) item you just added to the Main Tabs. Click the Rename button at the bottom of the Main Tabs list box and then type a display name for the new custom tab before you click OK. Click the New Group (Custom) item right below the custom tab you just renamed. Click the Rename button and then type a display name for the new custom group before you click OK. In the Choose Commands From list box on the left, click the name of the macro you want to add to the custom group now selected in the Main Tabs list box on the right. Click the Add button to add the selected Excel macro to the selected custom group on your custom tab. If you want to rename the macro and/or assign it a new icon, click the Rename button and make these changes in the Rename dialog box before you click the OK button to close the Excel Options dialog box. After you add a macro to the custom group of a custom tab, the name of the macro appears on a button on the custom tab of the Ribbon. Then, all you have to do to run the macro is click this command button. To assign an Excel macro to a custom button on the Quick Access toolbar, follow these steps: Click the Customize Quick Access Toolbar button at the end of the Quick Access toolbar and then click More Commands on its drop-down menu. Excel opens the Excel Options dialog box with the Quick Access Toolbar tab selected. Click Macros in the Choose Commands From drop-down list box. Excel lists the names of all the macros you created, both those in the current Excel workbook and those that are saved in the PERSONAL.XLSB workbook, in the Choose Commands From list box. Click the name of the macro to add to a custom button on the Quick Access toolbar in the Choose Commands From list box and then click the Add button. Click the Modify button to open the Modify Button dialog box if you want to change the display name and assign a different icon to the macro button. Click OK to close the Excel Options dialog box. After you close the Excel Options dialog box, a custom button sporting its associated macro icon (the default with a standard command flowchart unless you changed it) appears on the Quick Access toolbar. To see the name of the Excel macro assigned to this custom macro button as a ScreenTip, position the mouse pointer over the button. To run the macro, click the button.

View Article
Using Excel 2019 Filters

Article / Updated 11-09-2018

Excel’s Filter feature makes it a breeze to hide everything in a data list except the records you want to see. To filter the data list to just those records that contain a particular value, you then click the appropriate field’s AutoFilter button to display a drop-down list containing all the entries made in that field and select the one you want to use as a filter. Excel then displays only those records that contain the value you selected in that field. (All other records are hidden temporarily.) If the column headings of your data list table don’t currently have filter drop-down buttons displayed in their cells after the field names, you can add them simply by clicking Home →   Sort & Filter →  Filter or pressing Alt+HSF. (Check out these other entry and formatting shortcuts.) For example, in the image below, the Employee Data List was filtered to display only those records in which the Location is either Boston or San Francisco by clicking the Location field’s AutoFilter button and then clicking the (Select All) check box to remove its check mark. Then, the Boston and San Francisco check boxes were selected to add check marks to them before clicking OK. (It’s as simple as that.) After you filter a data list so that only the records you want to work with are displayed, you can copy those records to another part of the worksheet to the right of the database (or better yet, another Excel sheet in the workbook). Simply select the cells, then click the Copy button on the Home tab or press Ctrl+C, move the cell cursor to the first cell where the copied records are to appear, and then press Enter. After copying the filtered records, you can then redisplay all the records in the database or apply a slightly different filter. If you find that filtering the data list by selecting a single value in a field drop-down list box gives you more records than you really want to contend with, you can further filter the database by selecting another value in a second field’s drop-down list. For example, suppose that you select Boston as the filter value in the Location field’s drop-down list and end up with hundreds of Boston records displayed in the worksheet. To reduce the number of Boston records to a more manageable number, you could then select a value (such as Human Resources) in the Dept field’s drop-down list to further filter the database and reduce the records you have to work with onscreen. When you finish working with the Boston Human Resources employee records, you can display another set by displaying the Dept field’s drop-down list again and changing the filter value from Human Resources to some other department, such as Accounting. When you’re ready to display all the records in the database again, click the filtered field’s AutoFilter button (indicated by the appearance of a cone filter on its drop-down button) and then click the Clear Filter from (followed by the name of the field in parentheses) option near the middle of its drop-down list. You can temporarily remove the AutoFilter buttons from the cells in the top row of the data list containing the field names and later redisplay them by clicking the Filter button on the Data tab or by pressing Alt+AT or Ctrl+Shift+L. You can also use Slicer and Timeline filters on your data. Using Excel’s ready-made number filters: Top 10 Excel contains a number filter option called Top 10. You can use this option on a number field to show only a certain number of records (like the ones with the ten highest or lowest values in that field or those in the ten highest or lowest percent in that field or just those that are above or below average of that field). To use the Top 10 option in Excel to filter a database, follow these steps: Click the AutoFilter button on the numeric field you want to filter with the Top 10 option. Then highlight Number Filters in the drop-down list and click Top 10 on its submenu. Excel opens the Top 10 AutoFilter dialog box. By default, the Top 10 AutoFilter chooses to show the top ten items in the selected field. However, you can change these default settings before filtering the database. To show only the bottom ten records, change Top to Bottom in the left-most drop-down list box. To show more or fewer than the top or bottom ten records, enter the new value in the middle text box (that currently holds 10) or select a new value by using the spinner buttons. To show those records that fall into the Top 10 or Bottom 10 (or whatever) percent, change Items to Percent in the right-most drop-down list box. Click OK or press Enter to filter the database by using your Top 10 settings. In the image below, you can see the Employee Data List after using the Top 10 option (with all its default settings) to show only those records with salaries that are in the top ten. David Letterman would be proud! Using Excel’s ready-made date filters When filtering a data list by the entries in a date field, Excel makes available a variety of date filters that you can apply to the list. These ready-made filters include Equals, Before, After, and Between as well as Tomorrow, Today, Yesterday, as well as Next, This, and Last for the Week, Month, Quarter, and Year. Additionally, Excel offers Year to Date and All Dates in the Period filters. When you select the All Dates in the Period filter, Excel enables you to choose between Quarter 1 through 4 or any of the 12 months, January through December, as the period to use in filtering the records. To select any of these date filters, you click the date field’s AutoFilter button, then highlight Date Filters on the drop-down list and click the appropriate date filter option on the continuation menu(s). Using custom autofilters in Excel 2019 In addition to filtering a data list to records that contain a particular field entry (such as Newark as the City or CA as the State), you can create custom AutoFilters that enable you to filter the list to records that meet less-exacting criteria (such as last names starting with the letter M) or ranges of values (such as salaries between $25,000 and $75,000 a year). To create a custom filter for a field, you click the field’s AutoFilter button and then highlight Text Filters, Number Filters, or Date Filters (depending on the type of field) on the drop-down list and then click the Custom Filter option at the bottom of the continuation list. When you select the Custom Filter option, Excel displays a Custom AutoFilter dialog box. You can also open the Custom AutoFilter dialog box by clicking the initial operator (Equals, Does Not Equal, Greater Than, and so on) on the field’s Text Filters, Number Filters, or Date Filters submenus. In this dialog box, you select the operator that you want to use in the first drop-down list box. Then enter the value (text or numbers) that should be met, exceeded, fallen below, or not found in the records of the database in the text box to the right. Operators Used in Custom Excel AutoFilters Operator Example What It Locates in the Database Equals Salary equals 35000 Records where the value in the Salary field is equal to $35,000 Does not equal State does not equal NY Records where the entry in the State field is not NY (New York) Is greater than Zip is greater than 42500 Records where the number in the Zip field comes after 42500 Is greater than or equal to Zip is greater than or equal to 42500 Records where the number in the Zip field is equal to 42500 or comes after it Is less than Salary is less than 25000 Records where the value in the Salary field is less than $25,000 a year Is less than or equal to Salary is less than or equal to 25000 Records where the value in the Salary field is equal to $25,000 or less than $25,000 Begins with Begins with d Records with specified fields have entries that start with the letter d Does not begin with Does not begin with d Records with specified fields have entries that do not start with the letter d Ends with Ends with ey Records whose specified fields have entries that end with the letters ey Does not end with Does not end with ey Records with specified fields have entries that do not end with the letters ey Contains Contains Harvey Records with specified fields have entries that contain the name Harvey Does not contain Does not contain Harvey Records with specified fields have entries that don’t contain the name Harvey If you want to filter records in which only a particular field entry matches, exceeds, falls below, or simply is not the same as the one you enter in the text box, you then click OK or press Enter to apply this Excel filter to the database. However, you can use the Custom AutoFilter dialog box to filter the database to records with field entries that fall within a range of values or meet either one of two criteria. To set up a range of values, you select the “is greater than” or “is greater than or equal to” operator for the top operator and then enter or select the lowest (or first) value in the range. Then, make sure that the And option is selected, select “is less than” or “is less than or equal to” as the bottom operator, and enter the highest (or last) value in the range. Check out the images above and below to see how Excel filters the records in the Employee Data List so that only those records where Salary amounts are between $25,000 and $75,000 are displayed. As shown above, you set up this range of values as the filter by selecting “is greater than or equal to” as the operator and 25,000 as the lower value of the range. Then, with the And option selected, you select “is less than or equal to” as the operator and 75,000 as the upper value of the range. The results of applying this filter to the Employee Data List are shown below. To set up an either/or condition in the Custom AutoFilter dialog box, you normally choose between the “equals” and “does not equal” operators (whichever is appropriate) and then enter or select the first value that must be met or must not be equaled. Then you select the Or option and select whichever operator is appropriate and enter or select the second value that must be met or must not be equaled. For example, if you want Excel to filter the data list so that only records for the Accounting or Human Resources departments in the Employee Data List appear, you select “equals” as the first operator and then select or enter Accounting as the first entry. Next, you click the Or option, select “equals” as the second operator, and then select or enter Human Resources as the second entry. When you then filter the database by clicking OK or pressing Enter, Excel displays only those records with either Accounting or Human Resources as the entry in the Dept field.

View Article
How to Sort in Excel 2019: Data Lists

Article / Updated 11-09-2018

Every data list you put together in Excel will have some kind of preferred order for maintaining and viewing the records. Depending on how you sort Excel to view that list, you may want to see the records in alphabetical order by last name. In the case of a client data table, you may want to see the records arranged alphabetically by company name. In the case of an Employee Data list, the preferred order is in numerical order by the ID number assigned to each employee when he or she is hired. When you initially enter records for a new data list in Excel, you no doubt enter them in either the preferred order or the order in which you retrieve their records. However you start out, as you will soon discover, you don’t have the option of adding subsequent records in that preferred order. Whenever you add a new record, Excel sorts and tacks that record onto the bottom of the database by adding a new row. Suppose you originally enter all the records in a client data list in alphabetical order by company (from Acme Pet Supplies to Zastrow and Sons), and then you add the record for a new client: Pammy’s Pasta Palace. Excel puts the new record at the bottom of the barrel — in the last row right after Zastrow and Sons — instead of inserting it in its proper position, which is somewhere after Acme Pet Supplies but definitely well ahead of Zastrow and his wonderful boys! This isn’t the only problem you can have with the original record order. Even if the records in the data list remain stable, the preferred order merely represents the order you use most of the time in Excel. What about those times when you need to see the records in another, special order? For example, if you usually work with a client data list in numerical order by case number, you might instead need to see the records in alphabetical order by the client’s last name to quickly locate a client and look up his or her balance due in a printout. When using records to generate mailing labels for a mass mailing, you want the records in zip code order. When generating a report for your account representatives showing which clients are in whose territory, you need the records in alphabetical order by state and maybe even by city. To have Excel sort the records in a data list in the correct way, you must specify which field’s values determine the new order of the records. (Such fields are technically known as the sorting keys in the parlance of the database enthusiast.) Further, you must specify what type of order you want to create using the information in these fields. Choose from two possible orders to sort Excel: Ascending order: Text entries are placed in alphabetical order from A to Z, values are placed in numerical order from smallest to largest, and dates are placed in order from oldest to newest. Descending order: This is the reverse of alphabetical order from Z to A, numerical order from largest to smallest, and dates from newest to oldest. Sorting Excel on a single field When you need to sort the data list on only one particular field (such as the Record Number, Last Name, or Company field), you simply click that field’s AutoFilter button and then click the appropriate sort option on its drop-down list: Sort A to Z or Sort Z to A in a text field Sort Smallest to Largest or Sort Largest to Smallest in a number field Sort Oldest to Newest or Sort Newest to Oldest in a date field Excel then re-orders all the records in the data list in accordance with the new ascending or descending order in the selected field. If you find that you’ve sorted the list in error, simply click the Undo button on the Quick Access toolbar or press Ctrl+Z right away to return the list to its order before you selected one of these sort options. Excel shows when a field has been used to sort the data list by adding an up or down arrow to its AutoFilter button. An arrow pointing up indicates that the ascending sort order was used and an arrow pointing down indicates that the descending sort order was used. Sorting on multiple fields in Excel 2019 You need to use more than one field in sorting when the first field you use contains duplicate values and you want a say in how the records with duplicates are arranged. (If you don’t specify another field to sort on, Excel just puts the records in the order in which you entered them.) The best and most common example of when you need more than one field is when sorting a large database alphabetically by last name. Suppose that you have a database that contains several people with the last name Smith, Jones, or Zastrow (as is the case when you work at Zastrow and Sons). If you specify the Last Name field as the only field to sort on (using the default ascending order), all the duplicate Smiths, Joneses, and Zastrows are placed in the order in which their records were originally entered. To better sort these duplicates, you can specify the First Name field as the second field to sort on (again using the default ascending order), making the second field the tie-breaker, so that Ian Smith’s record precedes that of Sandra Smith, and Vladimir Zastrow’s record comes after that of Mikhail Zastrow. To sort Excel records in a data list on multiple fields, follow these steps: Position the cell cursor in one of the cells in the data list table. If the Home tab on the Ribbon is selected, click Custom Sort on the Sort & Filter button’s drop-down list (Alt+HSU). If the Data tab is selected, click the Sort command button. Excel selects all the records of the database (without including the first row of field names) and opens the Sort dialog box. Click the name of the field you first want the records sorted by in the Sort By drop-down list. If you want the records arranged in descending order, remember also to select the descending sort option (Z to A, Largest to Smallest, or Newest to Oldest) in the Order drop-down list to the right. (Optional) If the first field contains duplicates and you want to specify how the records in this field are sorted, click the Add Level button to insert another sort level. Select a second field to sort on in the Then By drop-down list and select either the ascending or descending option in its Order drop-down list to its right. (Optional) If necessary, repeat Step 4, adding as many additional sort levels as required. Click OK or press Enter. Excel closes the Sort dialog box and sorts the records in the data list using the sorting fields in the order of their levels in this dialog box. If you see that you sorted the database on the wrong fields or in the wrong order, click the Undo button on the Quick Access toolbar or press Ctrl+Z to (check out other Excel shortcuts) restore the database records to their previous order. Check out how the search is set up in the Sort dialog box above. In the Employee Data List, the Last Name field was chosen as the first field to sort on (Sort By) and the First Name field as the second field (Then By) — the second field sorts records with duplicate entries in the first field. The records in the Employee Data List were also sorted in alphabetical (A to Z) order by last name and then first name. See the Employee Data List right after sorting. Note how the Edwards — Cindy and Jack — are now arranged in the proper first name/last name alphabetical order. Sorting Excel: Sort items that aren’t a data list The Sort command is not just for sorting Excel records in the data list. You can use it to sort financial data or text headings in the spreadsheet tables you build as well. When sorting regular Excel worksheet tables, just be sure to select all the cells with the data to be sorted (and only those with the data to be sorted) before you open the Sort dialog box by clicking Custom Sort on the Sort & Filter button’s drop-down list on the Ribbon’s Home tab or the Sort button on the Data tab. Excel automatically excludes the first row of the cell selection from the sort (on the assumption that this row is a header row containing field names that shouldn’t be included). To include the first row of the cell selection in the sort, be sure to deselect the My Data Has Headers check box before you click OK to begin sorting. If you want to sort Excel sheet data by columns, click the Options button in the Sort dialog box. Click the Sort Left to Right button in the Sort Options dialog box and then click OK. Now you can designate the number of the row (or rows) to sort the data on in the Sort dialog box.

View Article
How to Add Records to Data Lists in Excel 2019

Article / Updated 11-08-2018

After creating the field names in Excel and one record of the data list and formatting them as a table, you’re ready to start entering the rest of its data as records in subsequent rows of the list. The most direct way to do this in Excel 2019 is to press the Tab key when the cell cursor is in the last cell of the first record. Doing this causes Excel to add an extra row to the data list where you can enter the appropriate information for the next record. When doing data entry directly in a data list table, press the Tab key to proceed to the next field in the new record rather than the →key. That way, when you complete the entry in the last field of the record, you automatically extend the data list, add a new record, and position the cell cursor in the first field of that record. If you press →to complete the entry, Excel simply moves the cell cursor to the next cell outside the data list table. Using the Form button to add records to Excel data lists Instead of entering the records of a data list directly in the table, you can use Excel’s data form to make the entries. The only problem with using the data form is that the command to display the form in a worksheet with a data list is not part of the Ribbon commands. You can access the data form only by adding its command button to the Quick Access toolbar or a custom Ribbon tab. To add this command button to the Excel Quick Access toolbar, follow these steps: Click the Customize Quick Access Toolbar button at the end of the Quick Access toolbar and then click the More Commands item at the bottom of its drop-down menu. Excel opens the Excel Options dialog box with the Quick Access Toolbar tab selected. The Form command button you want to add to the Quick Access toolbar is only available when you click the Commands Not in the Ribbon option on the Choose Commands From drop-down list. Click the Commands Not in the Ribbon option near the top of the Choose Commands From drop-down list. Click Form in the Choose Commands From list box and then click the Add button. Excel adds the Form button to the very end of the Quick Access toolbar. If you so desire, you can click the Move Up and Move Down buttons to reposition the Form button on this toolbar. Click OK to close the Excel Options dialog box and return to the worksheet with the data list. Adding records in Excel via the data form The first time you click the custom Form button you added to the Quick Access toolbar, Excel analyzes the row of field names and entries for the first record and creates a data form. This data form lists the field names down the left side of the form with the entries for the first record in the appropriate text boxes next to them. Here, you can see the data form for the new Employee Data database; it looks kind of like a customized dialog box. The data form Excel creates includes the entries you made in the first record. The data form also contains a series of buttons (on the right side) that you use to add, delete, or find specific records in the database. Right above the first button (New), the data form lists the number of the record you’re looking at followed by the total number of records (1 of 1 when you first create the data form). When creating new entries it will display New Record above this button instead of the record number. All the formatting that you assign to the particular entries in the first record is applied automatically to those fields in subsequent records you enter and is used in the data form. For example, if your data list contains a telephone field, you need to enter only the ten digits of the phone number in the Telephone field of the data form if the initial telephone number entry is formatted in the first record with the Special Phone Number format. That way, Excel takes a new entry in the Telephone file, such as 3075550045, for example, and automatically formats it so that it appears as (307) 555-0045 in the appropriate cell of the data list. The process for adding records to a data list with the data form is simple. When you click the New button, Excel displays a blank data form (marked New Record at the right side of the data form), which you get to fill in. After you enter the information for the first field, press the Tab key to advance to the next field in the record. Whoa! Don’t press the Enter key to advance to the next field in a record. If you do, you’ll insert the new, incomplete record into the database. Continue entering information for each field and pressing Tab to go to the next field in the database. If you notice that you’ve made an error and want to edit an entry in a field you already passed, press Shift+Tab to return to that field. To replace the entry, just start typing. To edit some of the characters in the field, press ← or click the I-beam pointer in the entry to locate the insertion point; then edit the entry from there. When entering information in a particular field, you can copy the entry made in that field from the previous record by pressing Ctrl+' (apostrophe). Press Ctrl+', for example, to carry forward the same entry in the State field of each new record when entering a series of records for people who all live in the same state. When entering dates in a date field, use a consistent date format that Excel knows. (For example, enter something like 7/21/98.) When entering zip codes that sometimes use leading zeros that you don’t want to disappear from the entry (such as zip code 00102), format the first field entry with the Special Zip Code number format. In the case of other numbers that use leading zeros, you can format it by using the Text format or put an ’ (apostrophe) before the first 0. The apostrophe tells Excel to treat the number like a text label but doesn’t show up in the database itself. (The only place you can see the apostrophe is on the Formula bar when the cell cursor is in the cell with the numeric entry.) Press the ↓ key when you’ve entered all the information for the new record. Or, instead of the ↓ key, you can press Enter or click the New button. Excel inserts the new record as the last record in the database in the worksheet and displays a blank data form in which you can enter the next record. When you finish adding records to the database, press the Esc key or click the Close button at the bottom of the dialog box to close the data form. Editing records in Excel in the data form After the database is under way and you’re caught up with entering new records, you can start using the data form to perform routine maintenance on the database. For example, you can use the data form to locate a record you want to change and then make the edits to the particular fields. You can also use the data form to find a specific record you want to remove and then delete it from the database. Locate the record you want to edit in the database by bringing up its data form. To edit the fields of the current record, move to that field by pressing Tab or Shift+Tab and replace the entry by typing a new one. Alternatively, press ← or → or click the I-beam cursor to reposition the insertion point, and then make your edits. To clear a field entirely, select it and then press the Delete key. Ways to Get to a Particular Record in Excel 2019 Keystrokes or Scroll Bar Technique Result Press ↓ or Enter or click the down scroll arrow or the Find Next button Moves to the next record in the data list and leaves the same field selected Press ↑ or Shift+Enter or click the up scroll arrow or the Find Prev button Moves to the previous record in the data list and leaves the same field selected Press PgDn Moves forward ten records in the data list Press PgUp Moves backward ten records in the data list Press Ctrl+↑ or Ctrl+PgUp or drag the scroll box to the top of the scroll bar Moves to the first record in the data list Drag the scroll box to almost the bottom of the scroll bar Moves to the last record in the data list To delete the entire record from the database, click the Delete button in the data form. Excel displays an alert box with the following dire warning: Displayed record will be permanently deleted To delete the record displayed in the data form, click OK. To play it safe and keep the record intact, click the Cancel button. You cannot use the Undo feature to bring back a record you removed with the Delete button! Excel is definitely not kidding when it warns permanently deleted. As a precaution, always save a back-up version of the worksheet with the database before you start removing old records.

View Article
How to Insert Online Images into Your Excel 2019 Sheet

Article / Updated 11-08-2018

Excel 2019 makes it easy to insert online 2-D and 3-D graphic images into your worksheet. The Online Pictures dialog box enables you to use Microsoft’s Bing search engine to search the entire web for 2-D images to use in your Excel sheet. If that’s not enough, you can also download images that you’ve saved in the cloud on your Windows OneDrive. To download an image into your Excel worksheet from any of these sources, you click the Online Pictures button in the Illustrations group on the Insert tab of the Ribbon (Alt+NF). Excel opens the Online Pictures dialog box. When you first open the Online Pictures dialog box, it contains a number of sample Bing searches arranged alphabetically by category with a sample image representing that category displayed. To display the photos and line art available for a particular category, such as Books or Money, click the sample image in its category. If none of the pictures in that or any of the other available categories fit the bill, you can use the search text box in the Online Pictures dialog box to try to locate a more suitable image. The search text box in the Online Pictures dialog box contains a drop-down button to the immediate left with the following search options: Bing (default) to use the Bing search engine to locate images on the web of a particular type that you want to add to your worksheet OneDrive to locate images saved on your SkyDrive to add to your ExcelWorksheet After you click a category in the opening Online Pictures dialog box or perform a Bing search by clicking the dialog box’s Search button (the magnifying glass icon), the Online Pictures dialog box displays a scrollable list of thumbnails of the photos or line art that you can insert into your current worksheet. Above the thumbnails, the Online Pictures dialog box displays a selected Creative Commons Only check box. When this check box is selected, Excel filters the thumbnail images shown to just those that are covered by Creative Commons licensing. Creative Commons licensing grants free distribution of what is otherwise copyrighted material under certain conditions (often noncommercial or educational use). Select the Creative Commons Only check box to remove its check mark only if you’re sure that you are comfortable using images in your worksheets not covered by Creative Commons licensing. To the immediate left of the Creative Commons Only check box, the Online Pictures dialog box contains a Filter button. When you click the Filter button, Excel displays a pop-up menu that enables you to filter the thumbnails displayed below in the Online Pictures dialog box by the image’s size, type, layout, and/or color. At the bottom of the dialog box below the area with the thumbnails, the Online Pictures dialog box displays the message, “You are responsible for respecting others’ rights, including copyright” followed by a Learn More Here hyperlink. Clicking this link opens a Microsoft Copyright web page in your default web browser that gives you basic information on copyright law, including information about what type of creative works are covered by copyright and the doctrine of fair use. To insert one of the located images into the current Excel worksheet, click its thumbnail to select it (shown by the check mark in the box in the upper-left corner and then click the Insert button (or you can simply double-click the thumbnail). If you want to insert more than one of the displayed images into your worksheet, click their thumbnails to select them before you click the Insert button. When you click the Insert button, Excel closes the Online Pictures dialog box and downloads into the active worksheet both the graphic image(s) you’ve selected along with a text box, containing a caption containing author credits for the downloaded picture(s). This caption text box contains two hyperlinks: the first takes you to a web page with more information about the picture and its author, and the second takes you to a web page with more specific information about the picture’s licensing. Inserting 3-D images into your Excel sheet Excel 2019 also supports the use of 3-D images downloaded from the Microsoft Remix 3D online community website using the From Online Sources option on drop-down menu of the 3D Models command button located in the Illustrations group on the Insert tab (Alt+NS3O). When you insert one of these 3-D images into your Excel sheet, you can rotate it so that it can be viewed from any angle you want. To insert a 3-D model, open the Online 3D Models dialog box and select a thumbnail of the model from one of its displayed categories or from a search you perform of the 3-D images uploaded to the Microsoft Remix 3D website. As soon as you click the Insert button, Excel downloads a copy of the 3-D model into the current worksheet with its selection and rotation handles displayed. Inserting local images into your Excel sheet If the 2-D image you want to use in a worksheet is saved on your computer in one of the local or network drives, you can insert it by selecting the Pictures command button on the Insert tab of the Ribbon (Alt+NP). Doing this opens the Insert Picture dialog box (which works just like opening an Excel workbook file in the Open dialog box) where you open the folder and select the local graphics file and then import it into the worksheet by clicking the Insert button. If you have an image of a 3-D model saved on a local or network drive, you can locate, select, and insert it in your worksheet from the Insert 3D Model dialog box opened by selecting the From a File option on the drop-down menu on the 3D Models command button located in the Illustrations group on the Insert tab (Alt+NS3F). If you want to bring in a graphic image created in another graphics program that isn’t saved in its own file, select the graphic in that program and then copy it to the Clipboard (press Ctrl+C). When you get back to your Excel worksheet, place the cursor where you want the picture to appear and then paste the image (press Ctrl+V or click the Paste command button at the beginning of the Home tab; check out these other entry shortcuts).

View Article
Creating Pivot Charts in Excel 2019

Article / Updated 11-08-2018

Excel 2019 offers multiple ways to display your data. After creating an Excel pivot table, you can create a pivot chart to display its summary values graphically in two simple steps: Click the PivotChart command button in the Tools group on the Analyze tab under the PivotTable Tools contextual tab to open the Insert Chart dialog box. Remember that the PivotTable Tools contextual tab with its two tabs — Analyze and Design — automatically appears whenever you click any cell in an existing pivot table. Click the thumbnail of the type of chart you want to create in the Insert Chart dialog box and then click OK. As soon you click OK after selecting the chart type, Excel displays two things in the worksheet with the pivot table: Pivot chart using the type of chart you selected that you can move and resize as needed (officially known as an embedded chart) PivotChart Tools with three contextual tabs — Analyze, Design, and Format — each with its own set of buttons for customizing and refining the pivot chart You can also create a pivot chart from scratch by building it in a similar manner to manually creating a pivot table. Simply select a cell in the data table or list to be charted and then select the PivotChart option on the PivotChart button’s drop-down menu on the Insert tab of the Ribbon (select the PivotChart & PivotTable option on this drop-down menu instead if you want to build a pivot table as well as a pivot chart). Excel then displays a Create PivotChart dialog box with the same options as the Create PivotTable dialog box E. After selecting your options and closing this dialog box, Excel displays a blank chart grid and a PivotChart Fields task pane along with the PivotChart Tools contextual tab on the Ribbon. You can then build your new pivot chart by dragging and dropping desired fields into the appropriate zones. Excel 2019’s new Insights feature (check out other great new features in Excel 2019) is a great way to gain new perspectives on your data by creating instant pivot charts illustrating some of their more interesting and, often, non-evident aspects. To use the Insights feature, all you do is position the cell cursor in one of the cells of your data table before selecting Insert →   Insights or pressing Alt+NDI. Excel then opens an Insights task pane containing thumbnails of suggested pivot charts (and sometimes regular charts) designed to highlight particular aspects of the data that may not be otherwise evident. To create one of the pivot chart suggested in the Insights task pane, all you have to do is click the Insert PivotChart link in the lower left of its thumbnail. Excel then inserts a new worksheet into the current workbook containing the embedded pivot chart complete with its supporting pivot table! Moving pivot charts to separate Excel sheets Although Excel automatically creates all new pivot charts on the same worksheet as the pivot table, you may find it easier to customize and work with it if you move the chart to its own chart sheet in the workbook. To move a new pivot chart to its own chart sheet in the Excel workbook, you follow these steps: Click the Analyze tab under the PivotChart Tools contextual tab to bring its tools to the Ribbon. If the PivotChart Tools contextual tab doesn’t appear at the end of your Ribbon, click anywhere on the new pivot chart to make this tab reappear. Click the Move Chart button in the Actions group. Excel opens a Move Chart dialog box. Click the New Sheet button in the Move Chart dialog box. (Optional) Rename the generic Chart1 sheet name in the accompanying text box by entering a more descriptive name there. Click OK to close the Move Chart dialog box and open the new chart sheet with your pivot chart. This image shows a clustered column pivot chart after moving the chart to its own chart sheet in the Excel workbook. Filtering pivot charts in Excel When you graph the data in an Excel pivot table using a typical chart type, such as column, bar, or line, that uses both an x- and y-axis, the Row labels in the pivot table appear along the x- (or category) axis at the bottom of the chart and the Column labels in the pivot table become the data series that are delineated in the chart’s legend. The numbers in the Values field are represented on the y- (or value) axis that goes up the left side of the chart. You can use the drop-down buttons that appear after the Filter, Legend fields, Axis fields, and Values field in the PivotChart to filter the charted data represented in this fashion like you do the values in the pivot table. As with the pivot table, remove the check mark from the (Select All) or (All) option and then add a check mark to each of the fields you still want represented in the filtered pivot chart. Click the following drop-down buttons to filter a different part of the pivot chart: Axis Fields (Categories) to filter the categories that are charted along the x-axis at the bottom of the chart Legend Fields (Series) to filter the data series shown in columns, bars, or lines in the chart body and identified by the chart legend Filter to filter the data charted along the y-axis on the left side of the chart Values to filter the values represented in the PivotChart Formatting pivot charts in Excel The command buttons on the Design and Format tabs attached to the PivotChart Tools contextual tab make it easy to further format and customize your pivot chart. Use the Design tab buttons to select a new chart style for your pivot chart or even a brand-new chart type. Use the Format tab buttons to add graphics to the chart as well as refine their look. The Chart Tools contextual tab that appears when you select a chart you’ve created contains its own Design and Format tabs with comparable command buttons.

View Article
page 1
page 2
page 3