Tuesday, 6 December 2016
Excel - Working out the difference in Hours
We saw how useful the DATEDIF function can be in an earlier article. But it's time intervals are only day, month and year there's another function available in Excel DATEDIFF but unlike the other one it's not actually supported as a worksheet function. It's a function in Excel's macro language, VBA. But that doesn't mean that we can't use it in our worksheet formulas. Read on...
Creating a Custom function
Function DiffInHours(First, Second)
DiffInHours = DateDiff("h", First, Second)
End Function
Entering a Custom Function
 |
| Entering an Excel custom function |
Settings
The interval argument has these settings:
Setting
|
Description
|
yyyy
|
Year
|
q
|
Quarter
|
m
|
Month
|
y
|
Day of year
|
d
|
Day
|
w
|
Weekday
|
ww
|
Week
|
h
|
Hour
|
n
|
Minute
|
s
|
Second
|
Shift Time function
Excel time values only function within the 24 hour day; if you start work at 19:00 hours
and end at 23:00 hours then Excel will calculate the elapsed time as 4 hours. Should you
work beyond midnight, then the same calculation will produce a negative value as you
would have stopped working at a time that was a lesser value than your starting time.
The following function accepts any starting or ending time and calculates the actual
elapsed time in hours.
Public Function SHIFTIME(Start_Time As Date, End_Time As Date) As Variant
'Accepts : Working shift start and end times.
'Returns : Number of hours worked as a decimal value.
If End_Time >= Start_Time Then
'Day Shift.
SHIFTIME = DateDiff("n", Start_Time, End_Time)
Else
'Night Shift; time to midnight plus time from midnight.
SHIFTIME = _
DateDiff("n", Start_Time, 1) + DateDiff("n", 0, End_Time)
End If
SHIFTIME = SHIFTIME / 60
End Function
Excel - Formula to calculate difference in hours between two dates
Calculate Hours Between Two Dates and Times in Excel
by GREGORY on DECEMBER 10, 2010
Recently I was asked how to calculate the number of hours between two points in time on different days. Since this was in a reader comment, I gave a brief answer that requires a fuller account here.
Dates and Times are all part of the master plan in Excel. Once you “get” the fundamentals, the rest is just icing on the cake. A Date value in Excel looks like this: 40519 A Time value in Excel looks like this: 0.58333
Cell formatting changes how you see these numbers. The Date: 7 Dec, 2010 The Time: 2:00 PM
Dates in Excel
When you type a Date into Excel, you may never see the underlying number, like 40519, but it’s there nonetheless. This a date serial number and it makes Date calculations easy. You ask, “Why is this such a weird-looking number?” Well the Excel folks started a numbering system with Dates. In Excel for Windows they gave 1 Jan, 1900 the serial date number of 1, then continued numbering until this day and beyond. So serial number 40519 represents 7 Dec, 2010.
In Excel for Mac they started numbering Dates beginning with 2 Jan, 1904. (don’t ask) So the serial date 40519 represents 8 Dec, 2014. (Actually it’s known as the 1904 date system. To be clear, Macs can change Excel settings to use the 1900 date system.)
Time in Excel
When you type 2:00 PM into a cell in Excel the underlying value is a fraction, but Excel interprets this as a time serial number and formats the cell accordingly. Try typing 0.25 into a blank cell, then change the cell formatting to a TIME format, and you’ll get something like 6:00 AM. As an aside, you can calculate this fraction for any time value during the day by taking the total number of seconds that have passed from midnight until your time value and dividing by 86,400 seconds in a day. Time Fraction Calculation
Dates and Times Together
In Excel the unit of time is “the Day,” a key fact to know. You’ll notice that Dates are integers, and Time is a fractional number. You can add the two together to get a Date/Time format.
So adding a Date serial number, like 40518, to a Time serial number, like 0.25, gives us 40518.25. Formatting the cell holding this value using “d mmm, yyyy h:mm AM/PM” will show 6 Dec, 2010 6:00 AM.
You can also enter something like 7 Dec, 2010 2:00 PM into a cell and Excel will recognize this as a Date/Time format. However, if you change the cell formatting to General, the underlying number is 40519.05833. So hopefully by now you can see that subtracting two Date/Time formatted numbers can be done mathematically. Subtracting 6 Dec, 2010 6:00 AM from 7 Dec, 2010 2:00 PM is done by Excel “underneath the hood” as 40519.05833 – 40518.25 and the result is 1.3333.
Calculating Hours Between 2 Dates and Times
If we recall that the unit of time is “the Day,” this value represents 1-1/3 days of time. Since there are 24 hours in a day, converting to hours is a simple multiplication 24 * 1.3333 = or 32 hours. (24 * 4/3 to be more precise) Finding the number of hours between two date/times is simple, just subtract the start date/time from the end date/time and multiply the result by 24 hours. If you want to enter the dates and times separately (which is loads easier than typing in a date/time in one cell) then add the date/times together. Hours = ((End_Date+End_Time)-(Start_Date+Start_Time))*24
Here’s a look at a typical worksheet designed to calculate the hours between two dates. Calculate Hours Between Two Dates and Times As you can see, the formula for Hours, in cell F2, shows in the formula bar. And row 3 contains General formatting so you can view the date/time serial numbers for row 2. Change the formatting for cells B2:E2 to match what you normally use for Date and Time data entry.
Extract Time with the MOD Function in Excel by GREGORY on NOVEMBER 1, 2012
I had a reader comment on my last post about how to extract time from a date-time number using the MOD function. Simple really.
The syntax is MOD(number,divisor). The MOD function returns the remainder after number is divided by divisor. A simple example is MOD(5,2), which equals one (1). It works like this: five (5) divided by two (2) equals two (2), with one (1) left over. All numbers are evenly divisible by one (1) so the MOD function returns any fractional part when the second argument is one (1).
In the screen shot below, cell C2 has the Date-Time number: 10/8/12 6:28 PM. It has an underlying serial number: 41990.7698, which you can see in cell C3 with General formatting.
MOD Function Time Extract Using the formula =MOD(C2,1) you can see the result in cells D2 and D3, with different cell formatting. Extracting the Time value from a Date-Time value is simple with the MOD function.
by GREGORY on DECEMBER 10, 2010
Recently I was asked how to calculate the number of hours between two points in time on different days. Since this was in a reader comment, I gave a brief answer that requires a fuller account here.
Dates and Times are all part of the master plan in Excel. Once you “get” the fundamentals, the rest is just icing on the cake. A Date value in Excel looks like this: 40519 A Time value in Excel looks like this: 0.58333
Cell formatting changes how you see these numbers. The Date: 7 Dec, 2010 The Time: 2:00 PM
Dates in Excel
When you type a Date into Excel, you may never see the underlying number, like 40519, but it’s there nonetheless. This a date serial number and it makes Date calculations easy. You ask, “Why is this such a weird-looking number?” Well the Excel folks started a numbering system with Dates. In Excel for Windows they gave 1 Jan, 1900 the serial date number of 1, then continued numbering until this day and beyond. So serial number 40519 represents 7 Dec, 2010.
In Excel for Mac they started numbering Dates beginning with 2 Jan, 1904. (don’t ask) So the serial date 40519 represents 8 Dec, 2014. (Actually it’s known as the 1904 date system. To be clear, Macs can change Excel settings to use the 1900 date system.)
Time in Excel
When you type 2:00 PM into a cell in Excel the underlying value is a fraction, but Excel interprets this as a time serial number and formats the cell accordingly. Try typing 0.25 into a blank cell, then change the cell formatting to a TIME format, and you’ll get something like 6:00 AM. As an aside, you can calculate this fraction for any time value during the day by taking the total number of seconds that have passed from midnight until your time value and dividing by 86,400 seconds in a day. Time Fraction Calculation
Dates and Times Together
In Excel the unit of time is “the Day,” a key fact to know. You’ll notice that Dates are integers, and Time is a fractional number. You can add the two together to get a Date/Time format.
So adding a Date serial number, like 40518, to a Time serial number, like 0.25, gives us 40518.25. Formatting the cell holding this value using “d mmm, yyyy h:mm AM/PM” will show 6 Dec, 2010 6:00 AM.
You can also enter something like 7 Dec, 2010 2:00 PM into a cell and Excel will recognize this as a Date/Time format. However, if you change the cell formatting to General, the underlying number is 40519.05833. So hopefully by now you can see that subtracting two Date/Time formatted numbers can be done mathematically. Subtracting 6 Dec, 2010 6:00 AM from 7 Dec, 2010 2:00 PM is done by Excel “underneath the hood” as 40519.05833 – 40518.25 and the result is 1.3333.
Calculating Hours Between 2 Dates and Times
If we recall that the unit of time is “the Day,” this value represents 1-1/3 days of time. Since there are 24 hours in a day, converting to hours is a simple multiplication 24 * 1.3333 = or 32 hours. (24 * 4/3 to be more precise) Finding the number of hours between two date/times is simple, just subtract the start date/time from the end date/time and multiply the result by 24 hours. If you want to enter the dates and times separately (which is loads easier than typing in a date/time in one cell) then add the date/times together. Hours = ((End_Date+End_Time)-(Start_Date+Start_Time))*24
Here’s a look at a typical worksheet designed to calculate the hours between two dates. Calculate Hours Between Two Dates and Times As you can see, the formula for Hours, in cell F2, shows in the formula bar. And row 3 contains General formatting so you can view the date/time serial numbers for row 2. Change the formatting for cells B2:E2 to match what you normally use for Date and Time data entry.
Extract Time with the MOD Function in Excel by GREGORY on NOVEMBER 1, 2012
I had a reader comment on my last post about how to extract time from a date-time number using the MOD function. Simple really.
The syntax is MOD(number,divisor). The MOD function returns the remainder after number is divided by divisor. A simple example is MOD(5,2), which equals one (1). It works like this: five (5) divided by two (2) equals two (2), with one (1) left over. All numbers are evenly divisible by one (1) so the MOD function returns any fractional part when the second argument is one (1).
In the screen shot below, cell C2 has the Date-Time number: 10/8/12 6:28 PM. It has an underlying serial number: 41990.7698, which you can see in cell C3 with General formatting.
MOD Function Time Extract Using the formula =MOD(C2,1) you can see the result in cells D2 and D3, with different cell formatting. Extracting the Time value from a Date-Time value is simple with the MOD function.
Excel Conundrums- Formula to produce a comma separated list
In this one we need a formula to generate a comma separated list but being a formula it has to upf=date to match the data
Using there macro is harder than the function but here's the reverse the macro is easier than the formula.
The absolute references are required when the formula is copied down.
Here's the code in plain text if you wish to copy and paste:
Function StringMeUp(HeaderRow As Range, DataRow As Range) As String
On Error Resume Next
'Loop through data cells.
For i = 1 To DataRow.Cells.Count
'Cell contains entry?
If Not IsEmpty(DataRow.Cells(i)) Then
'Build string.
StringMeUp = StringMeUp & HeaderRow.Cells(i) & ","
End If
Next
'Snip off the trailing comma.
StringMeUp = Left(StringMeUp, Len(StringMeUp) - 1)
End Function
These are tricky problems. The names have been changed to protect the innocent. No propriety value.
 |
| Generate a comma separated list of header values |
Using there macro is harder than the function but here's the reverse the macro is easier than the formula.
The absolute references are required when the formula is copied down.
 |
| StringMeUp User defined function |
Here's the code in plain text if you wish to copy and paste:
Function StringMeUp(HeaderRow As Range, DataRow As Range) As String
On Error Resume Next
'Loop through data cells.
For i = 1 To DataRow.Cells.Count
'Cell contains entry?
If Not IsEmpty(DataRow.Cells(i)) Then
'Build string.
StringMeUp = StringMeUp & HeaderRow.Cells(i) & ","
End If
Next
'Snip off the trailing comma.
StringMeUp = Left(StringMeUp, Len(StringMeUp) - 1)
End Function
Excel Conundrums
These are tricky problems. The names have been changed to protect the innocent. No propriety value.
Excel and Powerpoint Picture Charts
Visual presentation of data using icons, pictures, symbols, etc., in place of or in addition to common graph elements (bars, lines, points). Pictographs use relative sizes or repetitions of the same icon, picture, or symbol to show comparison. Also called pictogram, pictorial chart, pictorial graph, or picture graph.
A picture graph is a visual method of displaying information that uses images or symbols to represent data. It includes a key or scale that indicates the value of each picture. One benefit of a picture graph is that it makes it easier for people to understand information. Data presented verbally is usually clarified when a picture graph is utilized. Picture graphs, however, often do not display data as accurately as other types of graphs. For example, in a picture graph illustrating the number of cars a dealership sells each month, an image of one car represents 10 cars sold. If the dealership sells 12 cars, the picture graph shows an image of one car along with a small portion of another car. This makes it difficult for the person viewing the graph to know exactly how many cars the dealership sold.
Charts are often used to ease understanding of large quantities of data and the relationships between parts of the data. Charts can usually be read more quickly than the raw data that they are produced from. They are used in a wide variety of fields, and can be created by hand (often on graph paper) or by computer using a charting application. Certain types of charts are more useful for presenting a given data set than others. For example, data that presents percentages in different groups (such as "satisfied, not satisfied, unsure") are often displayed in a pie chart, but may be more easily understood when presented in a horizontal bar chart.[2] On the other hand, data that represents numbers that change over a period of time (such as "annual revenue from 1990 to 2000") might be best shown as a line chart.
Excel Chart provides a way to represent data visually, but by customizing chart we can convert it in to pictograph (graph with pictures). It is an ideogram which convey its meaning through depicting images which resembles real-world objects, eventually making chart self-explanatory. Enhancing the chart would be very useful, it makes your audience comprehend the table data, without even looking at it. In this post we will be creating a simple pictograph. Launch Excel 2010, and open datasheet for which you want to create pictograph. For instance, we have included Office Items datasheet containing, Products, and No. Of Products, as shown in the screenshot below. office items
To start off with, first we need to include a simple column graph for the table Office Items. For this, select the column for which you want to create a chart, navigate to Insert tab, and from Column, select the basic 2-D Chart, as shown in the screenshot below. chart Upon click, it will automatically create the chart for you, showing Products in x-axis, and No. Of Products in y-axis. Give it an appropriate name at the top of the chart. office items graph
Now we will create Pictograph by adding picture instead of bars in the graph. For this select the first bar in the chart, click the bar two times (not double-click), to select it exclusively, as shown in the screenshot below. books Now head over to Insert tab, and click ClipArt. clipart From ClipArt Pane, we will search for book clipart, right click the clip art you want to include, and click Copy. book
Make sure that book bar is selected in the chart, now paste this image by pressing Ctrl + V. You will see stretched book image instead of bar, as shown in the screen shot below. book clip We want to show the number of books in the graph, by depicting same number of book images.
Right-click the stretched image and click Format Data Point. format data Upon click, Format Data
Point dialog will appear, from Fill, enable Stack and Scale with option. Hit Close to apply. format options Number of book images as defined in table which will be shown in the chart. images Now repeat the procedure for the other bars to place suitable images. pencil 1 As you can see in the
screenshot below that we have replace bars with images. chart complete Now we will change the style and design to make the items more prominent. For this, select the chart, you we will notice three
new tabs will appear. Choose suitable style, color, design, layout, chart type, gridlines, etc by accessing different groups from these tabs.
A picture graph is a visual method of displaying information that uses images or symbols to represent data. It includes a key or scale that indicates the value of each picture. One benefit of a picture graph is that it makes it easier for people to understand information. Data presented verbally is usually clarified when a picture graph is utilized. Picture graphs, however, often do not display data as accurately as other types of graphs. For example, in a picture graph illustrating the number of cars a dealership sells each month, an image of one car represents 10 cars sold. If the dealership sells 12 cars, the picture graph shows an image of one car along with a small portion of another car. This makes it difficult for the person viewing the graph to know exactly how many cars the dealership sold.
Charts are often used to ease understanding of large quantities of data and the relationships between parts of the data. Charts can usually be read more quickly than the raw data that they are produced from. They are used in a wide variety of fields, and can be created by hand (often on graph paper) or by computer using a charting application. Certain types of charts are more useful for presenting a given data set than others. For example, data that presents percentages in different groups (such as "satisfied, not satisfied, unsure") are often displayed in a pie chart, but may be more easily understood when presented in a horizontal bar chart.[2] On the other hand, data that represents numbers that change over a period of time (such as "annual revenue from 1990 to 2000") might be best shown as a line chart.
Excel Chart provides a way to represent data visually, but by customizing chart we can convert it in to pictograph (graph with pictures). It is an ideogram which convey its meaning through depicting images which resembles real-world objects, eventually making chart self-explanatory. Enhancing the chart would be very useful, it makes your audience comprehend the table data, without even looking at it. In this post we will be creating a simple pictograph. Launch Excel 2010, and open datasheet for which you want to create pictograph. For instance, we have included Office Items datasheet containing, Products, and No. Of Products, as shown in the screenshot below. office items
To start off with, first we need to include a simple column graph for the table Office Items. For this, select the column for which you want to create a chart, navigate to Insert tab, and from Column, select the basic 2-D Chart, as shown in the screenshot below. chart Upon click, it will automatically create the chart for you, showing Products in x-axis, and No. Of Products in y-axis. Give it an appropriate name at the top of the chart. office items graph
Now we will create Pictograph by adding picture instead of bars in the graph. For this select the first bar in the chart, click the bar two times (not double-click), to select it exclusively, as shown in the screenshot below. books Now head over to Insert tab, and click ClipArt. clipart From ClipArt Pane, we will search for book clipart, right click the clip art you want to include, and click Copy. book
Make sure that book bar is selected in the chart, now paste this image by pressing Ctrl + V. You will see stretched book image instead of bar, as shown in the screen shot below. book clip We want to show the number of books in the graph, by depicting same number of book images.
Right-click the stretched image and click Format Data Point. format data Upon click, Format Data
Point dialog will appear, from Fill, enable Stack and Scale with option. Hit Close to apply. format options Number of book images as defined in table which will be shown in the chart. images Now repeat the procedure for the other bars to place suitable images. pencil 1 As you can see in the
screenshot below that we have replace bars with images. chart complete Now we will change the style and design to make the items more prominent. For this, select the chart, you we will notice three
new tabs will appear. Choose suitable style, color, design, layout, chart type, gridlines, etc by accessing different groups from these tabs.
Blah what do those units actually means
Excel - Printing Worksheets
The Paperless Office
People have been banging on about the "paperless office" for years but predictions of an all-digital future where paper is but a memory are premature. The reality is that, despite all the changes we have seen over the past few decades, paper documents are still with us.
 |
| A Paperless Office? |
I would much rather send an Excel document as an email attachment than send it to a printer but sometimes you have to. You don't want to but you have to print out a hard copy... and it looks a right old mess.
It's not that difficult to get an Excel spreadsheet to look good on paper and I hope that this article will give you a few bright ideas and a happy printing life.
Plan Ahead, Use Page Layout View
I've never indulged in the practice myself but I've heard that some people actually plan ahead before they do something. Amazing. Excel worksheets don't always look good on paper because they're not designed to fit on a page—they're designed in the Normal View. This is everyone's favourite view for working in Excel, it's great for editing and viewing on screen but hopeless as a design view as you can't see the document on the page.
If you know that you will need to print your Excel document in the future then switch over to the Page Layout View to design it first. Set the page orientation and work on a document where you can see exactly what you are doing, see exactly how much data you can get on a page, see your headers and footers etc. When you come to print the worksheet there will be none of that business of trying to coax it onto sheets of A4 because it already fits. No surprises.
If you know that you will need to print your Excel document in the future then switch over to the Page Layout View to design it first. Set the page orientation and work on a document where you can see exactly what you are doing, see exactly how much data you can get on a page, see your headers and footers etc. When you come to print the worksheet there will be none of that business of trying to coax it onto sheets of A4 because it already fits. No surprises.
1. Preview your worksheet before you print
You can see exactly how your worksheet will look on the printed page by using the Print Preview feature. In terms of saving you time and paper, Print Preview is your most valuable printing tool. You can even make certain changes within it, like clicking on and dragging the print margins to make them wider or narrower. Check it as you change printing and layout options to make sure your spreadsheet looks the way you want it to look.
3. Scaling
You're limited by the dimensions of the paper you're printing on, but there are ways to make the most of this space. Try changing the page orientation. The default orientation is good for data with more rows than columns, but if your worksheet is wider than it is tall, change the page orientation to landscape. Still need more room? You can change the width of the margins on the edge of your paper. The smaller they are, the more room there is for your data. Finally, if your worksheet isn't huge, try playing with the Custom Scaling Options to fit all of your rows, columns, or entire worksheet on one sheet of paper.
2. Decide what you're going to print
If you only need to look at a certain segment of your data, don't bother printing your entire workbook—just print the specific data. You can print just the worksheet you're viewing by going to the print pane and selecting Print Active Sheets, or you can select Print Entire Workbook to print the entire file. You can also print a small segment of your data by selecting the data, then choosing Print Selection in the print options.
4. Use Print Titles
Once your Excel sheet is more than one page long, understanding what you're looking at can get tricky. The Print Titles command lets you include a title row or column on each page of your spreadsheet. The columns or rows you select will show up on every page of your printout, which makes reading your data a lot easier.
5. Use page breaks
If your worksheet takes up more than one sheet of paper, consider using page breaks to decide exactly which data should be on which page. When you insert a page break into your worksheet, everything below the break is moved to a different page than everything above it. This is useful because it lets you break up your data exactly the way you want.
Following these tips will go a long way toward making your printed worksheets easier to read. For more printing tips and detailed instructions for the tips listed above, review our Printing Workbooks lesson.
Word- Create Your Own Cover Page
Thinking back to when I began working I remember spending countless hours creating title pages for booklets, documents and other types of publications. I would spent time creating the perfect heading, adjusting its alignment and location on the page. I would include a subtitle, author details, an image and a nice page border using one of the Page border options available in Word.
Are you still doing this? Let me save you a considerable amount of time and show you how to utilise the Cover Page feature in Microsoft Word. You will be creating professional looking cover pages with your own customised text in under 60 seconds.
The cover page designs are pre-defined templates, displayed in a gallery, which provide areas for text, dates and images that you can customise to suit your own needs. You can also download more designs from Office.com as well as create your own custom design and save it for use in any future documents.
Many formal report documents require a cover page that includes not only the document’s title, but information about its author and when it was written. Although you can insert a cover page from the Building Blocks Organizer, the Insert tab also has a separate gallery just for cover pages.
1. open or create the document for which you want to add a cover page.
2. click the Insert tab, and then click cover page in the pages group. The Cover Page gallery opens.
3. Scroll down to view the available cover pages (figure 12.10), and then click the one to insert. Select a cover page to insert from the gallery.
4. fill in the placeholders on the cover page. For example, click the [Document title] placeholder and type a title. To remove an unneeded control placeholder, right-click it and click Remove Content Control.
To create a cover page, follow these steps:
Open Microsoft Word
Open a document you wish to add a cover page to or begin this exercise with a blank document
Select the Insert tab from the Ribbon
Now click the Cover Page button within the Pages group
The Cover Page gallery will be displayed
Use the scroll bar to scroll down and view all available designs
Once you have identified the one you like, click it once with the left mouse button
The cover page will be added to your document
You will now see prompts for where you can add text or content
Simply click into a text prompt and enter the information you wish to display
When you click within a prompt for [Year], you will be displayed with a calendar, simply click any date within the year you wish to display
You may see that Microsoft Word will fill in some information for you, based on information you entered when you installed Microsoft Word. You can change this information if you need by clicking inside the text area
If you wish to use a different image rather than the one provided, select the image
Click the Picture Tools > Format tab which is now visible
Select the Change Picture button from the Adjust group
You can now navigate your computer files and select an image to use instead, click Insert once you have selected the new image
The image will now be replaced
You have now created a professional looking cover page all with a few clicks of the mouse
Delete a cover page
If you have created a cover page and decide later that you wish to remove it, you do not have to delete the elements individually.
Select Insert > Cover Page
From the menu select Remove Current Cover Page
The cover page will automatically be removed leaving the rest of your document untouched
Cover pages created using some older versions of Microsoft Word may require you to manually delete each element however all new cover pages will support being deleted using this method.
Date formats
A common issue I see when using cover pages is that the automatic date fields are formatted in US formats. Because I am located in Australia and for my readers who also prefer UK formatting I thought I would show you how to change this setting so that you can choose the format the date is displayed. You can even change it from 19/11/2014 to 19-Nov-2014 if preferred.
Place your cursor in the date field to select it
Select the Developer tab from the Ribbon
If you do not have the Developer tab available go to File > Options then select the Customize Ribbon option
In the right hand pane place a tick in the option to display Developer then click OK
From the Developer tab click the Properties button within the Controls group
The Content Control Properties dialog box will appear:
Go to the Date Picker Properties and from the list select the date format you wish to use
Click OK
The date should now be changed to the format you have chosen
Enjoying creating fast and professional looking cover pages for your documents and publications.
Word is Driving Me Crazy!
This chapter details a number of annoying things that prevent many users from enjoying and using Word as the makers intended. Some of the settings and behaviours here are defaults; others get turned on by accident. Some require you to dig a little deeper to find out what makes Word tick. What the annoyances covered in this chapter have in common is that they can be tamed or turned off. If some things about Word are giving you a headache, this chapter is your aspirin.
Automatic Annoyances
Word makes certain text and formatting changes automatically by default. These automatic changes might work for you—or not. This section shows you how to reclaim your ability to create your text yourself.
Bullets, numbers, boxes, and borders
Word sometimes senses that you’re typing a bulleted or a numbered list and uses its AutoFormat As You Type feature to help you. For example, if the insertion point is at the beginning of a new paragraph and you press Tab, type an asterisk (Shift+8), and press Tab again, Word converts the asterisk to a bullet and applies the List Paragraph style to the paragraph. Similarly, if you press Enter after a paragraph, type a number of underline characters, and press Enter again, Word converts your underline characters into a border line beneath the paragraph.
You can tell Word what to AutoFormat and what not to.
1. click file d options d proofing.
2. click the autocorrect options button. The AutoCorrect Options dialog box
appears.
3. click the autoformat as you type tab. See Figure 4.13. This tab controls auto- matic corrections. The AutoFormat tab’s settings, on the other hand, apply when you display and use the AutoFormat command. For more about the differences between these two features, see “AutoFormat versus AutoFormat as You Type” and “Working with the AutoFormat Command” in Chapter 11, “Cleaning Up with AutoCorrect and AutoFormat.”
4. click to check and uncheck features as needed. For example, under Apply as you type, you can uncheck the Automatic bulleted lists and Automatic numbered lists check boxes to have Word stop automating those types of lists.
5. click ok twice to close both dialog boxes.
You can enable or disable more than a dozen AutoFormat Actions.
Capitalization
Word makes some automatic corrections to capitalization that may not be what you want. For example, it assumes you want the first word in every sentence to be capitalized and caps it if you fail to do so. This might not be what you want in all circumstances, such as if you’re writing poetry or typing in a list of parts or products that you want to appear in lowercase. You also can turn off this behavior in Word:
1. click file d options d proofing.
2. click the autocorrect options button. The AutoCorrect Options dialog box
appears.
3. click the autocorrect tab if needed. See Figure 4.14.
4
Chapter 4: Zapping Word’s Top Annoyances
4. click to check and uncheck the second through sixth check boxes as needed. For example, clear the Capitalize first letter of sentences check box if you want to control sentence capitalization.
5. click ok twice to close both dialog boxes.
You can enable or disable automatic capitalization on the AutoCorrect tab.
Mouse selection
Have you ever tried to use the mouse to select everything in a paragraph except for the paragraph mark? Perhaps you want to replace what’s typed but want to keep the current paragraph formatting and style. But when you use the mouse to try to leave the paragraph mark unselected, Word jumps right past that last character and selects the paragraph mark as well.
You can make this behavior stop:
1. click file d options d advanced.
2. at the top under editing options, click the use smart paragraph selection
check box to clear it.
3. click ok.
With a more precise optical mouse, common today, it is possible to omit the paragraph mark even when smart paragraph selection is turned on. Another workaround for smart paragraph selection is to press Shift+left Arrow to nudge the selection one character to the left after selecting with the mouse. Smart paragraph selection applies only to selection using the mouse.
Cut and paste sentence and word behavior
Have you ever copied a sentence and then pasted it at the end of another sentence and ended up with no space between the period and the beginning of the pasted sentence, and too much space at the end? Smart cut and paste deals with this problem, by inserting a space between the period and the beginning of the pasted sentence automatically. However, if you’re accus- tomed to compensating for the missing space yourself, Word’s automatic behavior can be a nuisance, and you can turn it off.
1. click file d options d advanced.
2. Scroll down to display the cut, copy, and paste options.
3. Beside the use smart cut and paste check box, click the Settings button. The Settings dialog box shown in Figure 4.10 opens.
1
You can fine-tune smart cut and paste behavior in this dialog box.
4. click the adjust sentence and word spacing automatically check box to clear it.
5. click ok twice to close both the Settings and Word options dialog boxes.
Graphics bugbears:
Excel - Easy Nested IFs using the SWITCH and IFS functions
IFS
IFS is Microsoft’s stab at trying to shorten those incredibly long nested IF THEN ELSE statements where we need to perform a bunch of logical tests. Classic example would be coding out the logic to change a number value to a letter grades (90-100 = A, 80-89 = B, etc). Of course that can also be achieved via VLOOKUP or INDEX/MATCH, but the common way I see people do it is with nested IF logic.
Excel eliminates the mess of parenthesis and ELSE logic with the IFS function. Giving out grades would look something like:
=IFS(A2>89,“A”,A2>79,“B”,A2>69,“C”,A2>“59”,D, TRUE, “F”)
What would need adding is logic to capture the possibility of none of those conditions being true and that is what the TRUE captures
SWITCH
The new SWITCH function handles the situation where you are comparing several different values to the same expression. Microsoft has their own good explanation here. Similar results probably could also be achieved using a VLOOKUP or INDEX/MATCH – but hey, it’s nice to have options.
The IFS() function checks one or more conditions and returns a value that corresponds to the first TRUE condition—that only sounds confusing. This function uses the following syntax:
IFS(condition1, truevalue1, [condition2, truevalue2], ...)
The conditionx expression must evaluate to TRUE or FALSE and truevaluex is returned when conditionx is TRUE. If you enter a conditionx value, you must enter a truevaluex value. This format is straightforward and easy to interpret and update.
This function can eliminate nested IF() functions, which are handy and effective, but messy and difficult to maintain. The IFS() function shown in Figure C is much easier to write and update than its nested IF() counterpart.
SWITCH() is similar to IFS(), but with a subtle difference: Instead of specifying conditional expressions, you specify an expression and a series of values and results. You're looking for an exact match; when the first exact match is found, its corresponding result is returned. The function also accommodates a default to return when no match is found.
The syntax is simple:
SWITCH(expression, value1, result1, [default or value2, result2] ...)
where expression is the value being compared to valuex and resultx is the value returned when the first two match.
Figure D shows the following SWITCH() function returning a shortened code for each region:
=SWITCH(D2,"Southeast","SE","Northeast","NE","Central","C","Southwest","SW","Southwest","SW","No listing")
The options are straightforward and it's easy to see that there's no Northwest region. This is a good error clue for you. On close examination, you can see that I used Southwest; SW twice; simply change one to Northwest; NW to correct the expression. This type of error is easy to make. For this reason, I recommend always including a default argument, even if you think one isn't necessary.
Whadya mean, Excel's SWITCH function??? It doesn't exist, it does now. You hav'nt missed it you just didn't realise that you really needed it all these years.
<60 ail="" br="">
<60 ail="" br="">
<60 ail="" br="">
<60 ail="" br="">
Excel 2016 SWITCH function - the compact form of nested IF
If you ever spent far too much time, trying to get a nested IF formula, you'll like using the freshly released SWITCH function in Excel. It can be a real timesaver in situations where complex nested IF is needed. Earlier available only in VBA, SWITCH has been recently added as function in Excel 2016, Excel Online and Mobile, Excel for Android tablets and phones.
Excel SWITCH - syntax
The SWITCH function compares an expression against a list of values and returns the result according to the first matching value. If no match found, it's possible to return a default value which is optional.
The structure of the SWITCH function is as follows:
SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])
It has 4 arguments one of which is optional:
Expression is the required argument compared against value1…value126.
ValueN is a value compared against expression.
ResultN is the value returned when the corresponding valueN argument matches the expression. It must be specified for each valueN argument.
Default is the value returned if no matches have been found in the valueN expressions. This argument doesn't have a corresponding resultN expression and must be the final argument in the function.
Since functions are limited to 254 arguments, you can use up to 126 pairs of value and result arguments.
The SWITCH function vs. nested IF in Excel with use cases
The Excel SWITCH function, as well as IF, helps specify a series of conditions. However, with this function you define an expression and a sequence of values and results, not a number of conditional statements. What is good with the SWITCH function is that you don't need to repeat the expression again and again, which sometimes happens in nested IF formulas.
While everything is ok with nesting IFs, there are cases where the numbers of conditions for evaluation make building a nested IF irrational.
To demonstrate this point, let's have a look at the use cases below.
Say, you have several acronyms and you want to return the full names for them:
DR - Duplicate Remover
MTW - Merge Tables Wizard
CR - Combine Rows.v The SWITCH function in Excel 2016 will be quite straightforward for this task.
Use the Excel Switch function to return full names for acronyms
With the IF function you need to repeat the expression, so it takes more time to enter and looks longer.
Return full names for acronyms using nested If in Excel
The same can be seen in the following example with the rating system where the Excel SWITCH function looks more compact.
Return values for rating scores with the switch function
Return values for rating scores with the If function
Let's see how SWITCH works in combination with other functions. Suppose, we have a number of dates and want to see at a glance if they refer to today, tomorrow, or yesterday. For this we add the TODAY function that returns the serial number of the current date, and DAYS that returns the number of days between two dates.
You can see that SWITCH works perfectly for this task.
Return values for dates using Excel SWITCH
With the IF function, the conversion needs some nesting and gets complex. So the chances of making an error are high.
Return values for dates using nexted Ifs in Excel
Being underused and underestimated, Excel SWITCH is a really helpful function that lets you build conditional splitting logic.
IFS function
Applies To: Excel 2016 , Excel Online , Excel for Android tablets , Excel Mobile , More...
The IFS function checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition. IFS can take the place of multiple nested IF statements, and is much easier to read with multiple conditions.
NOTE: It's generally not advisable to use too many conditions with IF or IFS statements, as multiple conditions need to be entered in the correct order, and can be very difficult to build, test and update.
Remember: IFS is a new function that is only available in Excel 2016 including Excel Online, Mobile, Android phones and tables. If you open a workbook with the IFS function in an earlier version of Excel, all cells containing the IFS function will display a #NAME? error.
Simple syntax
IFS([Something is True1, Value if True1, [Something is True2, Value if True2],…[Something is True127, Value if True127])
NOTES: The IFS function allows you to test up to 127 different conditions.
For example:
=IFS(A1=1,1,A1=2,2,A1=3,3)
Which says IF(A1 equals 1, then display 1, IF A1 equals 2, then display 2, or else if A1 equals 3, then display 3).
Technical details Example 1
IFS function Grades example. Formula in cell B2 is =IFS(A2>89,"A",A2>79,"B",A2>69,"C",A2>59,"D",TRUE,"F") The formula for cells A2:A6 is:
=IFS(A2>89,"A",A2>79,"B",A2>69,"C",A2>59,"D",TRUE,"F")
Which says IF(A2 is Greater Than 89, then return a "A", IF A2 is Greater Than 79, then return a "B", and so on and for all other values less than 59, return an "F").
Example 2
IFS function - Days of the Week example - Formula in cell G2 is =IFS(F2=1,D2,F2=2,D3,F2=3,D4,F2=4,D5,F2=5,D6,F2=6,D7,F2=7,D8)
The formula in cell G7 is:
=IFS(F2=1,D2,F2=2,D3,F2=3,D4,F2=4,D5,F2=5,D6,F2=6,D7,F2=7,D8)
Which says IF(the value in cell F2 equals 1, then return the value in cell D2, IF the value in cell F2 equals 2, then return the value in cell D3, and so on, finally ending with the value in cell D8 if none of the other conditions are met).
Remarks
To specify a default result, enter a condition that will always be true for your final logical_test argument, such as TRUE or 1=1. If none of the other conditions are met the corresponding value will be returned. In Example 1, rows 6 and 7 (with the 58 grade) demonstrate this.
If a logical_test argument is supplied without a corresponding value_if_true, this function shows a "You've entered too few arguments for this function" error message.
If a logical_test argument is evaluated and resolves to a value other than TRUE or FALSE, this function returns a #VALUE! error.
If no TRUE conditions are found, this function returns #N/A error.
IFS is Microsoft’s stab at trying to shorten those incredibly long nested IF THEN ELSE statements where we need to perform a bunch of logical tests. Classic example would be coding out the logic to change a number value to a letter grades (90-100 = A, 80-89 = B, etc). Of course that can also be achieved via VLOOKUP or INDEX/MATCH, but the common way I see people do it is with nested IF logic.
Excel eliminates the mess of parenthesis and ELSE logic with the IFS function. Giving out grades would look something like:
=IFS(A2>89,“A”,A2>79,“B”,A2>69,“C”,A2>“59”,D, TRUE, “F”)
What would need adding is logic to capture the possibility of none of those conditions being true and that is what the TRUE captures
SWITCH
The new SWITCH function handles the situation where you are comparing several different values to the same expression. Microsoft has their own good explanation here. Similar results probably could also be achieved using a VLOOKUP or INDEX/MATCH – but hey, it’s nice to have options.
The IFS() function checks one or more conditions and returns a value that corresponds to the first TRUE condition—that only sounds confusing. This function uses the following syntax:
IFS(condition1, truevalue1, [condition2, truevalue2], ...)
The conditionx expression must evaluate to TRUE or FALSE and truevaluex is returned when conditionx is TRUE. If you enter a conditionx value, you must enter a truevaluex value. This format is straightforward and easy to interpret and update.
This function can eliminate nested IF() functions, which are handy and effective, but messy and difficult to maintain. The IFS() function shown in Figure C is much easier to write and update than its nested IF() counterpart.
SWITCH() is similar to IFS(), but with a subtle difference: Instead of specifying conditional expressions, you specify an expression and a series of values and results. You're looking for an exact match; when the first exact match is found, its corresponding result is returned. The function also accommodates a default to return when no match is found.
The syntax is simple:
SWITCH(expression, value1, result1, [default or value2, result2] ...)
where expression is the value being compared to valuex and resultx is the value returned when the first two match.
Figure D shows the following SWITCH() function returning a shortened code for each region:
=SWITCH(D2,"Southeast","SE","Northeast","NE","Central","C","Southwest","SW","Southwest","SW","No listing")
The options are straightforward and it's easy to see that there's no Northwest region. This is a good error clue for you. On close examination, you can see that I used Southwest; SW twice; simply change one to Northwest; NW to correct the expression. This type of error is easy to make. For this reason, I recommend always including a default argument, even if you think one isn't necessary.
Whadya mean, Excel's SWITCH function??? It doesn't exist, it does now. You hav'nt missed it you just didn't realise that you really needed it all these years.
IFS and SWITCH functions help specify a series of conditions
The new IFS and SWITCH functions give you an alternative to using a series of nested IF functions, like “IF(IF(IF()))”, when you have more than one condition that you want to test to find a corresponding result. The IF function is one of the most commonly used functions in Excel, and using IF inside IF (nested IF functions) has been a common practice in Excel, but it can be challenging or confusing at times.
The advantage of using the new IFS functions is that you can specify a series of conditions in a single function. Each condition is followed by the result that will be used if the condition is true—making it very straightforward to create and read the formula afterward. For example, let’s say you want to get the grade letter for a given score on a test. Using the IFS function, it might be something like this:
=IFS(C1>=90, “A”, C1>=80, “B”, C1>= 70, “C”, C1>=60, “D”, C1,"Fail")
<60 ail="" br=""><60 ail="" br="">
This can be read as, if the grade in C1 is greater than or equal to 90, it’s an A. Otherwise, if it’s greater than or equal to 80, it’s a B. Otherwise, if it’s greater than or equal to 70, it’s a C and so on. It’s pretty easy to write it this way and it’s also straightforward to read and understand what’s going on.
The SWITCH function also handles multiple conditions. What makes it different is that rather than specifying a series of conditional statements, you specify an expression and a series of values and results. The values are compared to the expression, and when the first exact match is found, the corresponding result is applied to the cell. You can also specify a “default” result that will be returned if none of the values are an exact match for the expression. The advantage of the SWITCH function is that you can avoid repeating the expression over and over, which sometimes happens in nested IF formulas.
In the example below, the first part of the formula extracts the size code (i.e. XS, M and G) from the middle of the item in column B. It’s rather long, so it’s nice that SWITCH only needs it to be written once and it can be compared to a list of values.
The example below can be explained as:
Extract the size code from the item in column B. If it equals “XS”, the result is “Extra Small.” Otherwise, if it equals “S”, the result is “Small” and so on. If there’s no match, the result is “Not Specified.”
<60 ail="" br="">
The same result could be calculated using nested IF functions, but it would be significantly longer, as shown below.
Excel 2016 SWITCH function - the compact form of nested IF
If you ever spent far too much time, trying to get a nested IF formula, you'll like using the freshly released SWITCH function in Excel. It can be a real timesaver in situations where complex nested IF is needed. Earlier available only in VBA, SWITCH has been recently added as function in Excel 2016, Excel Online and Mobile, Excel for Android tablets and phones.
Excel SWITCH - syntax
The SWITCH function compares an expression against a list of values and returns the result according to the first matching value. If no match found, it's possible to return a default value which is optional.
The structure of the SWITCH function is as follows:
SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])
It has 4 arguments one of which is optional:
Expression is the required argument compared against value1…value126.
ValueN is a value compared against expression.
ResultN is the value returned when the corresponding valueN argument matches the expression. It must be specified for each valueN argument.
Default is the value returned if no matches have been found in the valueN expressions. This argument doesn't have a corresponding resultN expression and must be the final argument in the function.
Since functions are limited to 254 arguments, you can use up to 126 pairs of value and result arguments.
The SWITCH function vs. nested IF in Excel with use cases
The Excel SWITCH function, as well as IF, helps specify a series of conditions. However, with this function you define an expression and a sequence of values and results, not a number of conditional statements. What is good with the SWITCH function is that you don't need to repeat the expression again and again, which sometimes happens in nested IF formulas.
While everything is ok with nesting IFs, there are cases where the numbers of conditions for evaluation make building a nested IF irrational.
To demonstrate this point, let's have a look at the use cases below.
Say, you have several acronyms and you want to return the full names for them:
DR - Duplicate Remover
MTW - Merge Tables Wizard
CR - Combine Rows.v The SWITCH function in Excel 2016 will be quite straightforward for this task.
Use the Excel Switch function to return full names for acronyms
With the IF function you need to repeat the expression, so it takes more time to enter and looks longer.
Return full names for acronyms using nested If in Excel
The same can be seen in the following example with the rating system where the Excel SWITCH function looks more compact.
Return values for rating scores with the switch function
Return values for rating scores with the If function
Let's see how SWITCH works in combination with other functions. Suppose, we have a number of dates and want to see at a glance if they refer to today, tomorrow, or yesterday. For this we add the TODAY function that returns the serial number of the current date, and DAYS that returns the number of days between two dates.
You can see that SWITCH works perfectly for this task.
Return values for dates using Excel SWITCH
With the IF function, the conversion needs some nesting and gets complex. So the chances of making an error are high.
Return values for dates using nexted Ifs in Excel
Being underused and underestimated, Excel SWITCH is a really helpful function that lets you build conditional splitting logic.
IFS function
Applies To: Excel 2016 , Excel Online , Excel for Android tablets , Excel Mobile , More...
The IFS function checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition. IFS can take the place of multiple nested IF statements, and is much easier to read with multiple conditions.
NOTE: It's generally not advisable to use too many conditions with IF or IFS statements, as multiple conditions need to be entered in the correct order, and can be very difficult to build, test and update.
Remember: IFS is a new function that is only available in Excel 2016 including Excel Online, Mobile, Android phones and tables. If you open a workbook with the IFS function in an earlier version of Excel, all cells containing the IFS function will display a #NAME? error.
Simple syntax
IFS([Something is True1, Value if True1, [Something is True2, Value if True2],…[Something is True127, Value if True127])
NOTES: The IFS function allows you to test up to 127 different conditions.
For example:
=IFS(A1=1,1,A1=2,2,A1=3,3)
Which says IF(A1 equals 1, then display 1, IF A1 equals 2, then display 2, or else if A1 equals 3, then display 3).
Technical details Example 1
IFS function Grades example. Formula in cell B2 is =IFS(A2>89,"A",A2>79,"B",A2>69,"C",A2>59,"D",TRUE,"F") The formula for cells A2:A6 is:
=IFS(A2>89,"A",A2>79,"B",A2>69,"C",A2>59,"D",TRUE,"F")
Which says IF(A2 is Greater Than 89, then return a "A", IF A2 is Greater Than 79, then return a "B", and so on and for all other values less than 59, return an "F").
Example 2
IFS function - Days of the Week example - Formula in cell G2 is =IFS(F2=1,D2,F2=2,D3,F2=3,D4,F2=4,D5,F2=5,D6,F2=6,D7,F2=7,D8)
The formula in cell G7 is:
=IFS(F2=1,D2,F2=2,D3,F2=3,D4,F2=4,D5,F2=5,D6,F2=6,D7,F2=7,D8)
Which says IF(the value in cell F2 equals 1, then return the value in cell D2, IF the value in cell F2 equals 2, then return the value in cell D3, and so on, finally ending with the value in cell D8 if none of the other conditions are met).
Remarks
To specify a default result, enter a condition that will always be true for your final logical_test argument, such as TRUE or 1=1. If none of the other conditions are met the corresponding value will be returned. In Example 1, rows 6 and 7 (with the 58 grade) demonstrate this.
If a logical_test argument is supplied without a corresponding value_if_true, this function shows a "You've entered too few arguments for this function" error message.
If a logical_test argument is evaluated and resolves to a value other than TRUE or FALSE, this function returns a #VALUE! error.
If no TRUE conditions are found, this function returns #N/A error.
Excel - Easy concatenation using the TEXTJOIN and CONCAT functions
Nothing gets me more excited than some useful new Excel functions. Remember the game change of IFNA and IFERROR?
Microsoft is back with some new functions for Office 365 subscribers including CONCAT, TEXTJOIN, IFS, SWITCH, MAXIFS, and MINIFS. They’ve also included a new type of chart the “funnel chart”. At least two of these calculation functions are vast improvements over existing functionality.
CONCAT
My clear favorite so far. Ever need to CONCATENATE more than one cell? The old syntax required a daisy chain of commas and cell ranges – now CONCAT can do ranges:
=CONCATENATE(A1,A2,A3,A4,…,A10) is the same as =CONCAT(A1:A10)
My carpal tunnel syndrome thanks you Microsoft.
TEXTJOIN
I will probably be using this quite a bit besides CONCAT. As you know CONCATENATE tends to smash things together without any formatting like spaces or commas. TEXTJOIN makes your life simple. The syntax for the function is =TEXTJOIN(delimiter, ignore_empty, text1…). You can specify that you want to separate each value with either a space or a comma and a space. Microsoft can fill you in more here.
The TEXTJOIN() function combines text from multiple ranges with the added flexibility of a specified delimiter. This function uses the following syntax:
TEXTJOIN(delimiter, ignoreempty, text1, [text2], ...)
where delimiter is a text string or a reference to a text string that represents the character(s) you want to insert between each of the strings you're combining. In addition, ignoreempty is TRUE or FALSE with TRUE (the default) ignoring empty cells. The two text arguments are literal string values or references to ranges that contain the text you want to concatenate.
The biggest advantage is the ability to set the delimiter once. Figure A illustrates this point. Columns F, G, and H contain the following formulas, respectively:
=C2& " for " & D2
=CONCATENATE(C2, " for ", D2)
=TEXTJOIN(" for ",,C2, D2)
In such a simple example, none of the functions appears to be superior because you specify the delimiter only once. But when combining several strings, TEXTJOIN() is more efficient. It also makes it easy to create a string array from a single field—functionality that's been a long time coming. Figure B shows the following function combining all the text values in the Personnel column:
=TEXTJOIN(", ",,C2:C33)
Use CONCAT() to combine multiple strings or ranges similar to TEXTJOIN(), but without the flexibility of a delimiter or empty argument. This function replaces CONCATENATE() (see #1), which remains available for backward compatibility. CONCAT() uses the following syntax:
CONCAT(text1, [text2], ...)
where both arguments are a string or an array of strings, such as a range. You can add a delimiter as an argument, but a better choice would be to use TEXTJOIN(). I'm not sure why Microsoft bothered with this one; I'm including it to be comprehensive and as a gentle warning to start using it instead of CONCATENATE().
added TEXTJOIN and CONCAT to combine a list or range of text strings, MAXIFS and MINIFS to find the maximum or minimum in a range given one or more conditions and IFS and SWITCH to avoid messy nested IF functions. Read on for details on each function.
Combine text strings using TEXTJOIN and CONCAT
A very common task for users in spreadsheets is to combine text strings, but until now, if you wanted to join text strings from a range of cells, you had to specify each cell individually. The new TEXTJOIN and CONCAT functions let you combine text strings from ranges of cells with or without using a delimiter, such as a comma separating each item. You can simply refer to the range and specify the delimiter once and let Excel do all the heavy lifting. Concantenation.
The old-fashioned way:
=CONCATENATE(A3, “, “, B3, “, “, C3,”, “, D3, “, “, E3)
The new way to join text strings using TEXTJOIN:
=TEXTJOIN(“, “, TRUE, A3:E3)
Let’s say you just want to join the parts of an address into a single text string. The old way would require you to specify each cell and repeat a comma that separates each part:
The new way is much simpler. You simply specify the comma (or whatever separator you want), choose whether to ignore empty cells and then specify the range.
If you want to know more, see the online help for TEXTJOIN and CONCAT.
For a long time CONCATENATE has been the first function we thought of when we needed to combine text in Excel. A couple of new players has entered the game recently and has shaken the things up a bit. Yes, I'm talking about TEXTJOIN and CONCAT designed to help you combine a range of strings. Use these simple functions whenever you need to merge parts of names, addresses, or phrases, combine numbers and words. Currently they are available in Excel 2016, Excel Online and Mobile, Excel for Android tablets and phones.
TEXTJOIN function in Excel
CONCAT function in Excel
Concatenate strings in Excel
The TEXTJOIN function - description and syntax
The TEXTJOIN function in Excel concatenates text from multiple ranges or strings. You can specify a delimiter to include between each text value and ignore empty cells. Even if the delimiter is an empty string, TEXTJOIN will successfully join text in Excel.
Here's how the function looks like:
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
It has 3 required and 1 optional arguments:
The delimiter lets you specify any character for separating your text. This may be a comma, space, ampersand, or anything you like. The characters must be enclosed by double quotes, or a reference to a valid text string. Any numbers will be regarded as text.
Ignore_empty is a required argument. If it's TRUE, empty cells will be ignored. You can include blank cells by setting this value to FALSE.
text1 lets you specify a range of cell values to concatenate rather than having to enter each one.
[text2, …] is an optional argument that allows specifying further ranges to include in if your range is not continuous.
There can be a maximum of 252 arguments for the text items. If the resulting string exceeds the cell limit of 32767 characters, the TEXTJOIN function returns the #VALUE! error.
The CONCAT function in Excel - description and syntax
Introduced as part of the update in February 2016, CONCAT replaces the CONCATENATE function in Excel. It works exactly the same way. Looks like it has been added to reduce the length of the function name. Also, CONCAT is the standard function used by Google Sheets. CONCATENATE continues to be supported to provide compatibility with the previous versions of Excel.
The structure of the function is as follows:
CONCAT(text1, [text2],…)
CONCAT has just two arguments one of which is optional:
text1 is the text entry to be joined. It can be a string, or a range of strings.
[text2, …] stands for additional text items to be combined.
There can be a maximum of 253 arguments for the text values.
Excel - concatenate strings using the TEXTJOIN and CONCAT functions
Joining text is one of the most common tasks in Excel. In the previous versions if you needed to concatenate text from several cells, it was necessary to specify each one individually. With the new TEXTJOIN and CONCAT functions, you can simply refer to a range and combine text strings from cell ranges with or without a delimiter.
The main limitation of the CONCATENATE() function that CONCAT() has addressed is that we can now select a range of cells to join while previously were limited to listing cells separately. Say, you want to combine parts of telephone numbers. With the Concatenate function, you will need to enter the cell addresses one by one.
=CONCATENATE(A1,B1,C1,D1,E1,)
Combine strings using the CONCATENATE function]
In this case, CONCAT looks much more compact. All you need to do is just pick the range with the Excel strings to concatenate.
=CONCAT(A1:E1)
Join numbers with the help of CONCAT]
At the same time, both CONCAT and CONCATENATE look the same when you need to join text using delimiters.
=CONCATENATE(A2," ",B2," ",C2," ",D2," ",E2)
How to use the Concatenate function in Excel]
=CONCAT(A2," ",B2," ",C2," ",D2," ",E2)
Use the CONCAT function to combine text in Excel]
As it can be easily seen, the functions don't process empty cells which results in extra spaces.
In this case, TEXTJOIN is destined to be a really popular choice for users working with large amounts of text data. You simply specify the space delimiter, choose to ignore empty cells and define the range. This is by far a smarter solution.
=TEXTJOIN(" ",TRUE,A2:E2)
Use the TEXTJOIN function to concatenate text in Excel]
If you have Excel 2016, do explore the TEXTJOIN and CONCAT functions to see how helpful they are and say bye-bye to concatenated and nested if statements when combining strings.
Microsoft is back with some new functions for Office 365 subscribers including CONCAT, TEXTJOIN, IFS, SWITCH, MAXIFS, and MINIFS. They’ve also included a new type of chart the “funnel chart”. At least two of these calculation functions are vast improvements over existing functionality.
CONCAT
My clear favorite so far. Ever need to CONCATENATE more than one cell? The old syntax required a daisy chain of commas and cell ranges – now CONCAT can do ranges:
=CONCATENATE(A1,A2,A3,A4,…,A10) is the same as =CONCAT(A1:A10)
My carpal tunnel syndrome thanks you Microsoft.
TEXTJOIN
I will probably be using this quite a bit besides CONCAT. As you know CONCATENATE tends to smash things together without any formatting like spaces or commas. TEXTJOIN makes your life simple. The syntax for the function is =TEXTJOIN(delimiter, ignore_empty, text1…). You can specify that you want to separate each value with either a space or a comma and a space. Microsoft can fill you in more here.
The TEXTJOIN() function combines text from multiple ranges with the added flexibility of a specified delimiter. This function uses the following syntax:
TEXTJOIN(delimiter, ignoreempty, text1, [text2], ...)
where delimiter is a text string or a reference to a text string that represents the character(s) you want to insert between each of the strings you're combining. In addition, ignoreempty is TRUE or FALSE with TRUE (the default) ignoring empty cells. The two text arguments are literal string values or references to ranges that contain the text you want to concatenate.
The biggest advantage is the ability to set the delimiter once. Figure A illustrates this point. Columns F, G, and H contain the following formulas, respectively:
=C2& " for " & D2
=CONCATENATE(C2, " for ", D2)
=TEXTJOIN(" for ",,C2, D2)
In such a simple example, none of the functions appears to be superior because you specify the delimiter only once. But when combining several strings, TEXTJOIN() is more efficient. It also makes it easy to create a string array from a single field—functionality that's been a long time coming. Figure B shows the following function combining all the text values in the Personnel column:
=TEXTJOIN(", ",,C2:C33)
Use CONCAT() to combine multiple strings or ranges similar to TEXTJOIN(), but without the flexibility of a delimiter or empty argument. This function replaces CONCATENATE() (see #1), which remains available for backward compatibility. CONCAT() uses the following syntax:
CONCAT(text1, [text2], ...)
where both arguments are a string or an array of strings, such as a range. You can add a delimiter as an argument, but a better choice would be to use TEXTJOIN(). I'm not sure why Microsoft bothered with this one; I'm including it to be comprehensive and as a gentle warning to start using it instead of CONCATENATE().
added TEXTJOIN and CONCAT to combine a list or range of text strings, MAXIFS and MINIFS to find the maximum or minimum in a range given one or more conditions and IFS and SWITCH to avoid messy nested IF functions. Read on for details on each function.
Combine text strings using TEXTJOIN and CONCAT
A very common task for users in spreadsheets is to combine text strings, but until now, if you wanted to join text strings from a range of cells, you had to specify each cell individually. The new TEXTJOIN and CONCAT functions let you combine text strings from ranges of cells with or without using a delimiter, such as a comma separating each item. You can simply refer to the range and specify the delimiter once and let Excel do all the heavy lifting. Concantenation.
The old-fashioned way:
=CONCATENATE(A3, “, “, B3, “, “, C3,”, “, D3, “, “, E3)
The new way to join text strings using TEXTJOIN:
=TEXTJOIN(“, “, TRUE, A3:E3)
Let’s say you just want to join the parts of an address into a single text string. The old way would require you to specify each cell and repeat a comma that separates each part:
The new way is much simpler. You simply specify the comma (or whatever separator you want), choose whether to ignore empty cells and then specify the range.
If you want to know more, see the online help for TEXTJOIN and CONCAT.
For a long time CONCATENATE has been the first function we thought of when we needed to combine text in Excel. A couple of new players has entered the game recently and has shaken the things up a bit. Yes, I'm talking about TEXTJOIN and CONCAT designed to help you combine a range of strings. Use these simple functions whenever you need to merge parts of names, addresses, or phrases, combine numbers and words. Currently they are available in Excel 2016, Excel Online and Mobile, Excel for Android tablets and phones.
TEXTJOIN function in Excel
CONCAT function in Excel
Concatenate strings in Excel
The TEXTJOIN function - description and syntax
The TEXTJOIN function in Excel concatenates text from multiple ranges or strings. You can specify a delimiter to include between each text value and ignore empty cells. Even if the delimiter is an empty string, TEXTJOIN will successfully join text in Excel.
Here's how the function looks like:
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
It has 3 required and 1 optional arguments:
The delimiter lets you specify any character for separating your text. This may be a comma, space, ampersand, or anything you like. The characters must be enclosed by double quotes, or a reference to a valid text string. Any numbers will be regarded as text.
Ignore_empty is a required argument. If it's TRUE, empty cells will be ignored. You can include blank cells by setting this value to FALSE.
text1 lets you specify a range of cell values to concatenate rather than having to enter each one.
[text2, …] is an optional argument that allows specifying further ranges to include in if your range is not continuous.
There can be a maximum of 252 arguments for the text items. If the resulting string exceeds the cell limit of 32767 characters, the TEXTJOIN function returns the #VALUE! error.
The CONCAT function in Excel - description and syntax
Introduced as part of the update in February 2016, CONCAT replaces the CONCATENATE function in Excel. It works exactly the same way. Looks like it has been added to reduce the length of the function name. Also, CONCAT is the standard function used by Google Sheets. CONCATENATE continues to be supported to provide compatibility with the previous versions of Excel.
The structure of the function is as follows:
CONCAT(text1, [text2],…)
CONCAT has just two arguments one of which is optional:
text1 is the text entry to be joined. It can be a string, or a range of strings.
[text2, …] stands for additional text items to be combined.
There can be a maximum of 253 arguments for the text values.
Excel - concatenate strings using the TEXTJOIN and CONCAT functions
Joining text is one of the most common tasks in Excel. In the previous versions if you needed to concatenate text from several cells, it was necessary to specify each one individually. With the new TEXTJOIN and CONCAT functions, you can simply refer to a range and combine text strings from cell ranges with or without a delimiter.
The main limitation of the CONCATENATE() function that CONCAT() has addressed is that we can now select a range of cells to join while previously were limited to listing cells separately. Say, you want to combine parts of telephone numbers. With the Concatenate function, you will need to enter the cell addresses one by one.
=CONCATENATE(A1,B1,C1,D1,E1,)
Combine strings using the CONCATENATE function]
In this case, CONCAT looks much more compact. All you need to do is just pick the range with the Excel strings to concatenate.
=CONCAT(A1:E1)
Join numbers with the help of CONCAT]
At the same time, both CONCAT and CONCATENATE look the same when you need to join text using delimiters.
=CONCATENATE(A2," ",B2," ",C2," ",D2," ",E2)
How to use the Concatenate function in Excel]
=CONCAT(A2," ",B2," ",C2," ",D2," ",E2)
Use the CONCAT function to combine text in Excel]
As it can be easily seen, the functions don't process empty cells which results in extra spaces.
In this case, TEXTJOIN is destined to be a really popular choice for users working with large amounts of text data. You simply specify the space delimiter, choose to ignore empty cells and define the range. This is by far a smarter solution.
=TEXTJOIN(" ",TRUE,A2:E2)
Use the TEXTJOIN function to concatenate text in Excel]
If you have Excel 2016, do explore the TEXTJOIN and CONCAT functions to see how helpful they are and say bye-bye to concatenated and nested if statements when combining strings.
Subscribe to:
Posts (Atom)