Data in an Excel worksheet is easier to analyze when formatted as a table. To help you understand tables and their many features, including sorting, filtering, slicers, and calculated columns, Belleyre Books has published a new video.
0:04 Excel 2016 Table from the book Microsoft Excel 2016 In 90 Pages by Beth Brown
0:10 In this video you will learn how to:
0:12 Format related data as a table.
0:15 Add a Total row to the table.
0:18 Sort and filter a table.
0:21 Create a slicer for filtering table data.
0:26 Create a calculated column.
0:33 In this spreadsheet, I will convert the range of inventory data to a table and then demonstrate the features of a table.
0:44 Notice the rows contain the same type of data in each column. A table is for grouping related information and this must be considered when deciding to format a range into a table.
0:51 The first step to creating a table is to select the range to be formatted.
0:56 Next, select the Format as Table command on the Home tab and the pick a table style.
1:02 Excel detected that the first row of data contained headers.
1:06 If your first row of data does not contain headers, Excel will automatically add generic headers for you.
1:15 If you’re not happy with this style, click Quick Styles on the Design tab and choose a different one.
1:27 To expand a table, simply drag the sizing handle.
1:30 Or, when the last cell in the table is selected, simply press the Tab key to add a new row.
1:39 Let’s add a new inventory item.
1:43 Notice that the new row has all the same formatting as the previous rows in the table.
1:48 You can also right-click in a table for commands to add or delete rows and columns.
1:54 To summarize table data, I’ll add a Total row.
1:57 To do this, click anywhere in the table and then in the Table Style Options on the Design tab, click Total Row.
2:06 A total is automatically added for the last column, but you can click in any total row cell and select how you want the column to be analyzed.
2:16 Tables make it easy to sort data.
2:18 Simply, click the drop-down list for the column you want to sort by and then select a Sort command.
2:25 The drop-down lists for the columns also have commands for filtering, or querying, table data.
2:32 For example, let’s see which items have less than 60 in stock.
2:35 For this, we’ll use the Number Filters command.
2:44 You can type the criteria and then select OK to filter the table.
2:50 To again display all the data, click the Clear Filter command.
2:58 Another way to filter data is with a slicer.
3:01 It’s a filtering tool that remains on the worksheet.
3:06 To add a slicer, on the Design tab, click the Insert Slicer command.
3:14 Choose which columns you want to have slicers displayed for
3:21 Slicers can be sized and moved anywhere on the worksheet.
3:35 To apply a filter, simply click on criteria.
3:42 To clear the filter, click the Clear Filter button.
3:52 The last thing I’ll demonstrate is how to create a calculated column.
3:58 Let’s insert a column and change the name to Value.
4:08 From anywhere in the column, I’ll add a formula to calculate the value of the inventory of each item.
4:26 Notice how cell references are relative to the table data rather than the exact cell row and column.
4:34 When I press Enter, formulas are automatically copied to the entire column.
4:40 Now we need to simply apply the appropriate format.
4:46 And then we can quickly add a new summary in the Total row
4:51 And there you have it.
5:04 Formatting related data as a table allows you to quickly analyze data.
5:13 When formatting data as a table, include only consistent rows of data. Do not include a row with totals; add it after the table is created.
5:25 All this and more can be found in Microsoft Excel 2016 In 90 Pages by Beth Brown. Available at Amazon.com.
Tables and much more are covered in Microsoft Excel 2016 In 90 Pages.
Are you trying to create a formula that performs different actions depending on the value in a cell? Then you’re in need of the IF function. To help you understand the IF function, nested IF functions, and using other functions in an IF, Belleyre Books has published a new video.
0:03 Excel 2016 The If Function
0:07 From the book Microsoft Excel 2016 In 90 Pages by Beth Brown
0:13 In this video you will learn
0:14 How to the use the IF function to perform an action based on the outcome of a logical test.
0:21 How to use a function as an argument in an IF.
0:28 How to create a formula with nested IF functions.
0:35 In this spreadsheet, I have test scores and averages.
0:40 I will create three formulas that use the IF functions to complete the Status, Grade, and Notes columns.
0:52 For the Status column, I want to create a formula that displays Passing if the Average is 60 or above. For averages below 60, I want to display Failing.
1:03 Since the IF function performs an action based on the outcome of a logical comparison, we can use it to compare the Average value to 60 and then display the appropriate message.
1:13 To do this, I enter a formula that uses the IFfunction in cell F3.
1:18 As you type, Excel displays an Intellisense function guide to remind you of the arguments needed.
1:32 In this case, the logical test is E3>=60
1:37 We type a comma
1:39 and then we enter the action to take when the logical test is true, which in this case is to display Passing.
1:48 We type a comma and then enter the outcome when the test is FALSE
1:52 which is to display Failing
1:54 Be sure to type the close parenthesis to complete the function.
2:00 As you can see, 80.3 is greater than 60 so Passing is displayed.
2:04 Now, let’s copy the formula to the rest of the column.
2:10 Failing is displayed when the average is less than 60.
2:15 To determine the letter grade associated with an Average, we will need to make several comparisons.
2:22 To do this, a formula that uses nested IF functions can be used.
2:33 In cell G3 we’re going to compare the Average to the score needed for an A
2:40so when E3>=90 we want to display an A
2:46 However, when that isn’t true we have several alternatives, so we’ll use another IF function to make a comparison.
2:59 So if E3>=80 we want to display a B
3:01 And here again, when that isn’t true we still have several comparisons to make.
3:07 So we’ll use another IFfunction
3:13 If E3>=70, we’ll display a C
3:23 And if that isn’t true, we’ll make a comparison to see if E3>=60
3:32 that requires us to display a D and that leaves us with just one last outcome
3:39 to display an F. Now we need to type all the closing parens.
3:43 As you can see Excel helps you out by highlighting the matching parens.
3:53 Let’s fill down. The correct letter grade has been calculated for each class.
4:01 As you can see, the failing letter grade compared all the values until it came to the very last outcome.
4:11 In the Note column, I want to display one message when the letter grade is an A or B, and a different message for grades below a B.
4:19 For this comparison, we’ll use the OR function.
4:22 The OR function returns TRUE when one or more of its arguments are TRUE.
4:42 So if the value in G3 is equal to an A
4:47 or when G3 is equal to a B
4:52 we want a TRUE outcome
4:56 in which case we will display “Great job!”
5:05 When we don’t have a TRUE outcome we want to display “Study more.”
5:21 And there you have it. By using the IF, nested IFs, and functions as arguments, you can create logical formulas in your spreadsheet.
5:34 Use the IF function when you want to perform an action based on the logical outcome of a comparison.
5:42 Use nested IF functions when you need to make more than one comparison.
5:49 You can use other functions as arguments in an IF.
6:00 All this information and more can be found in Microsoft Excel 2016 In 90 Pages by Beth Brown available at Amazon.com.
Cell references and much more are covered in Microsoft Excel 2016 In 90 Pages.
Have you ever created a formula and then have the cell references change unexpectedly when you copied the formula to another cell? It may be a problem with the type of cell references you used in your formula. To help you nail cell references, Belleyre Books has published a new video showing you just how to create different reference types and what changes to expect when your formula gets copied.
0:00 Excel 2016 Relative, Absolute, and Mixed Cell References
0:04 From the book Microsoft Excel 2016 In 90 Pages by Beth Brown
0:14 In this video you will learn
0:16 How relative, absolute, and mixed cell reference behave when copied to another cell.
0:18 How to use the F4 key to easily convert between reference types when creating a formula.
0:31 Cell References
0:32 Are used in formulas to refer to data stored in a spreadsheet cell.
0:37 The type of cell reference you use depends on
0:42 where you want to copy the formula.
0:45 Relative cell references, such as B12, are the default. They change relative to the new cell location they are copied to.
0:46 Absolute cell references, such as $B$12, use dollar signs to indicate a cell reference should not change when copied to another location.
0:59 Mixed cell references, such as $B12 and B$12, use dollar signs in front of either a column letter or row number to indicate which part of the reference is to remain unchanged when copied to a new location.
1:22 In this spreadsheet, I will demonstrate the various cell reference types.
1:31 I want to create a formula to calculate the taxes on the menu items.
1:37 To do this, I enter a formula in cell C4 to multiply the price times the tax rate.
1:47 To copy this formula to other cells in this column,
1:51 I use the Fill handle.
1:56 But wait, there’s something wrong.
2:00 Because the reference to the tax rate is relative,
2:03 it changed when copied to the other cells.
2:14 Let’s delete the formulas and create a new one that uses an absolute cell reference that won’t change when copied.
2:26 When we get to the reference that is to remain unchanged,
2:30 we can type dollar signs in front of the column letter and row number
2:34 or we can just press the F4 key to cycle through reference types.
2:40 I’m pressing the F4 key now
2:42 to cycle through the reference types.
2:45 I’ll stop at absolute reference with dollar signs in front of both the column letter and row number.
2:52 Now let’s use fill down to copy the formula to the remaining column cells.
3:02 As you can see the absolute reference remains unchanged and the relative reference changed as needed.
3:18 Next, let’s total the price and tax amounts.
3:28 The formula for the first item is =B5+C5. Now let’s fill down.
3:38 As you can see the relative referencing changed to refer to the appropriate item
3:46 calculating individual totals.
3:49 To demonstrate mixed cell references, I’m going to complete a table that calculates prices for up to 5 orders of an item.
3:59 The formula needs to multiply the total by the number of orders.
4:06 The first reference should always refer to column D. Let’s use the F4 key to get to the correct reference.
4:25 The second reference should always refer to row 3.
4:38 Now let’s fill across. As you can see, the mixed cell references allow the formula to correctly calculate.
4:52 Next, let’s fill down to calculate the other items.
4:57 Mixed references allow the same formula to be copied across columns and down rows without error.
5:20 Using cell references where possible in formulas is a good practice.
5:26 Use the appropriate type of cell reference (relative, absolute, or mixed) in formulas to avoid errors when copying.
5:35 Press the F4 key when entering a cell reference to change the reference type quickly and easily.
5:46 Although not demonstrated, to create a reference to a cell on another sheet, include the sheet name, as in ‘Sheet 2’!B4.
5:54 Notice the single quotation marks around the sheet name.
5:58 You will need to include those when your sheet name contains a space.
6:07 All this information and more can be found in Microsoft Excel 2016 In 90 Pages by Beth Brown
6:14 available at Amazon.com
Cell references and much more are covered in Microsoft Excel 2016 In 90 Pages.
The essential guide, Microsoft Excel 2016 In 90 Pages ($8.95/$7.95) is now available at Amazon.com. Perfect for anyone using Microsoft Excel 2016 for Windows. Basic through advanced topics are covered for every level of computer user. Implement formulas, functions, charts, tables, PivotTable reports, and other advanced features.
Microsoft Excel 2016 In 90 Pages guides you through the process of creating spreadsheets. With step-by-step instructions, screenshots, and tables detailing Excel commands and features, you will be immediately on track to analyzing data. Look inside to learn how to:
- Create and format a spreadsheet using practices that optimize data analysis.
- Ensure accurate data with data validation and Excel’s AutoComplete, AutoCorrect, Auto Fill, and spelling checker features.
- Produce the most impact through conditional formatting and sparklines.
- Organize related data into tables that sort, filter, use slicers, have a totals row, and use calculated columns to analyze data.
- Visualize data relationships through charts, including pie, line, bar, treemap, waterfall, and stock charts.
- Use formulas and functions to generate new information.
- Perform What-If? analysis with scenarios, goal seek, and data tables.
- Generate PivotTable and PivotChart reports for tables and Data Models.
- Create Data Models by defining relationships between tables.
- Work more effectively by using multiple sheets, inserting cell comments, creating macros, and designing custom templates.
- Learn how to protect worksheet data and work collaboratively.
Please feel free to contact me with any questions or comments.
Microsoft Excel 2016 In 90 Pages will be released in a few days! So excited to be adding the next book to the In 90 Pages series. Look for Excel companion videos to be added to the Belleyre Books YouTube channel as well.
Whether you’re a beginner or a power user, you’ll find Microsoft Excel 2016 In 90 Pages to be a handy guide. Just like Microsoft Word 2016 In 90 Pages, the features of this new book include screenshots, step-by-step instructions, and tables, all organized in a logical way to take you start to finish with creating your spreadsheets. You can expect an In 90 Pages book to cut the fluff and get to the stuff!
While you’re waiting for the book (and eBook), click the link below to download a Microsoft Excel 2016 Shortcuts Quick Reference. This one-page sheet will give you the shortcuts you need, allowing you to keep your hands on the keyboard while you work.
Download Microsoft Excel 2016 In 90 Pages Shortcuts Quick Reference
Several notable improvements to Word 2016 are available through recent updates for subscription based users. Updates include:
- Grammar & Style proofing option
- Improved spelling checker
- Writing style suggestions
- A new way to view documents: side to side
- Improved picture background removal
Grammar checker, writing style, and spelling checker improvements come by way of a digital writing assistant that Microsoft calls Editor. Grammar and style proofing is accessed by clicking File > Options > Proofing and then clicking Writing Style and selecting Grammar & Style. The grammar tool now explains why a word or phrase has been flagged with a blue double line, along with suggestions for corrections. Writing style issues are flagged with a dotted gold underline that you right-click for explanation and suggestions. The improved spelling checker displays meanings for suggested alternatives to better guide you with corrections.
Spelling and grammar checking is covered in Microsoft Word 2016 In 90 Pages (pages 12-13). The new Office updates require minor changes to the section content:
To correct a red flagged word or phrase (spelling error):
1. Right-click the text. A menu is displayed similar to:
2. Click a correctly spelled word to replace the unknown word.
Click Ignore All to remove the squiggly line from this and every occurrence of the spelling in the document.
Click Add to Dictionary to remove the squiggly line and add the word to the dictionary file so that it will be recognized in all future Word documents.
To correct a blue flagged word or phrase (grammar error):
1. Right-click the text. A menu is displayed similar to:
2. Click a word or phrase to replace the flagged text.
Click Ignore Once to remove the blue double line from this occurrence.
Click Options for “Grammar” to display the Grammar Settings dialog box.
Another addition to recent software updates is the View > Side to Side command. Click this command to be able to scroll through your multi-page document like a stack of papers that gets shuffled from side to side. In this view, the only Zoom option available is to display your document as tiles.
The last update I’ll mention is the background removal tool which has been improved for easier background removal in pictures.
Posted in Word
Tagged Word 2016
Spring is a beautiful time of year. Longer days, warmer weather, and flowers blossoming. Even more exciting for many is the anticipation of receiving the long-awaited college degree, diploma, certificate, or one of the many other forms of recognition from hard work, dedication, and focus on higher education. With this accomplishment comes the prospect of a better job. And with the prospect of a better job comes the job search.
Job search requires a résumé–the document that summarizes everything about you in one little piece of paper. Since employers heavily rely on the résumé to determine interview candidates, it’s important to use a format that is up-to-date. Below I’ve summarized the process I used in creating a résumé for a soon-to-graduate college student. You can find more detailed information for the features discussed below in Microsoft Word 2016 In 90 Pages.
- Standard 8 1/2″ x 11″ paper size
- Decide on a font that’s easy to read. For example, Calibri or Times New Roman.
- Click Layout > Columns > More Columns and set dialog box options similar to:
- Reduce margins slightly on all sides (you’ll have to experiment with what works without getting too narrow).
- Type Your Name into the header and center the text.
- Select all the header text (Your Name) and use the following steps to change the character spacing:
1. On the Home tab, click the Font dialog box launcher.
2. In the Font dialog box, click the Advanced tab.
3. Change the Spacing option to Expanded and then experiment with the By option to get the look you want. (I used 5 pt.)
- For the left column, you want a little space between lines. I changed the Spacing Before paragraph format to 6 pt.
- The Professional Profile paragraph should be brief and formatted as justified. If your statement is longer, you may want to include it as a centered paragraph under your name in the header.
- The remaining paragraphs in the left column have a right alignment.
- The section titles (Experience, Education, etc.) have a paragraph Shading format.
- The items under each job are a bulleted list. Each bullet point starts with an action word for good résumé style. I included several examples in the screenshot.
Good luck with your résumé!
Today is 3/14. Happy Pi day!
Pi (π) is the circumference of a circle divided by its diameter. This ratio is always the same, for every circle of every size. And it has an infinite number of decimal places. π is used for calculations involving diameter, volumes of cylinders, the double helix of DNA, waves, ripples, and spectra, the pupil of the eye, and rainbows, to name just a few. Here are some digits:
7260249141273. . .
If you’re using Excel, the function PI() represents π to 15 digits!
Theodor Seuss Geisel, more affectionately known as Dr. Seuss, was born on March 2, 1904. According to one biography, it was The Cat in the Hat, written as a children’s primer containing 220 vocabulary words, that made Dr. Seuss a household name. But like most authors, Seuss had his fair share of trial and tribulation–his first book, And to Think That I Saw It on Mulberry Street, was rejected 27 times before finally being published.
Even though my son and daughter are well into adulthood, the bookshelves in my house still contain dozens of Seuss books. The books hold such wonderful memories of the days when my kids and I would just “sit and read” for hours on end, with me reading aloud one Seuss story after another and often times rereading the favorites again and again. Eventually, first my son and then my daughter read the stories to me. And then finally the books sat more or less unopened as my kids moved on to other things. I didn’t give it a second thought; it seemed like a natural progression.
Strange thing with the Seuss books though is that we have had occasions to return to them. When the “Grinch” movie came out, my kids wanted to review the story as presented in the book. When social and environmental issues were discussed in school, Horton Hears a Who!, The Lorax, and The Sneetches seemed to take on new meaning. Of special significance to my whole family is Green Eggs and Ham, which we all read again when my daughter landed the role of Sam I Am in her school’s adaptation of Seussical the Musical.
Copyright Beth Brown
How appropriate that the last book Dr. Seuss worked on bridged the gap between young and old(er). Oh, The Places You’ll Go! reflects a message of both wisdom and motivation and is a treasure for every high school and college graduate and anyone else who hasn’t read it. Happy Birthday, Dr. Seuss, and thank you for your timeless masterpieces that have far-reaching appeal for the young and the not-so-young who are wise enough to want to hear the messages you reveal through laughter, silliness, and captivating illustrations.
Note: This is an update to a post made March 2, 2015.
Using Word 2016? Perhaps you’ve watched videos to learn how to set tab stops, create tables, and format a Word document. Maybe you’ve even taken a class where you muddled through the 1,000+ page textbook. You’ve mastered the skills and now you’re ready to apply your knowledge.
But, wait. You’re scratching your head and looking at the beautifully formatted document that’s ready to be distributed to the customer mailing list you’ve so carefully cultivated and now you can’t quite remember the steps for mail merge.
What do you do? Hop on YouTube and hope you hit it lucky with the right video that hopefully won’t require 20 minutes of your time? Or are you going to flip through that doorstopper of a textbook? There is another option… Flip to page 67 of your concise, convenient, 1/4″ thick copy of Microsoft Word 2016 In 90 Pages and follow the quick and easy step-by-step instructions (with helpful screenshots) to achieve your goal with time leftover to get a coffee.
And just in case you are deep into the mail merge process, here’s a tip you may find helpful (it’s also on page 70 of Microsoft Word 2016 In 90 Pages):
Store Your Address in Word
Before you start the steps for printing envelopes or labels, you may want to store your return address in Word so that it can automatically be used during the merge process:
- Click File > Options > Advanced. A dialog box is displayed.
- Scroll to the General options and type your return address in the Mailing address box.
- Click OK.