Tips for Working With Text and Text Functions in Excel

Tips for Working With Text and Text Functions in Excel


When you think of Excel (our most important Excel tips


8 Tips for How to Learn Excel Quickly




8 Tips for How to Learn Excel Quickly

Not as comfortable with Excel as you would like? Start with simple tips for adding formulas and managing data. Follow this guide, and you’ll be up to speed in no time.
Read More

), you probably think of numbers, calculations, and formulas. But, you also add text to spreadsheets, like headings, descriptions, or people’s names.

Today we’ll cover various ways of working with text in Excel spreadsheets. We discuss several different functions for working with text. Whenever you use a function, always start it with an equals sign (=).

Wrap Text in a Cell

When you enter text in a cell that’s wider than the cell, the text goes past the right border of the cell by default. But it’s easy to have the text wrap to fit the column width. The text wrap adjusts automatically when you change the width of the cell.

To make text wrap in a cell, select the cell and click the Wrap Text button in the Alignment section of the Home tab.

excel text functions - Click the Wrap Text button

The text is wrapped in the cell and the row height automatically adjusts to fit the text.

excel text functions - Text wrapped automatically in a cell

If the row height does not automatically adjust, the row may be set to a specific height.

To automatically fit the row height to the text, select the cell. Then, click Format in the Cells section on the Home tab and select AutoFit Row Height.

If you want to keep the row at a specific height, you can change it to make sure the wrapped text fits. Select Row Height from the Format menu. Then, enter a height for the row on the Row Height dialog box and click OK.

excel text functions - AutoFit Row Height

You can also drag the bottom border of the row down until all the lines of text fit in the cell.

Enter a Line Break in a Cell

If the text in a cell goes past the right border, you can also insert a line break to manually make the text wrap.

Double-click on the cell to edit the text or press F2. Click the mouse at the point in the text where you want to insert a line break. Then, press Alt + Enter.

excel text functions - Enter a line break in a cell

The row height adjusts to fit the text if the Format is set to AutoFit Row Height in the Cells section of the Home tab.

excel text functions - Result of manual line break

Count Cells Containing Any Text

If you want to know how many cells in a range on your worksheet contains text (not numbers, errors, formulas, or blank cells), you can use the COUNTIF function


Mini Excel Tutorial: Using Advanced Counting and Adding Functions in Excel




Mini Excel Tutorial: Using Advanced Counting and Adding Functions in Excel

Counting and adding formulas may appear mundane compared to more advanced Excel formulas. But they can help you save a lot of time when you need to collect information about the data in your spreadsheet.
Read More

.

The generic form of the COUNTIF function for counting any number of text characters is:

=COUNTIF(cellrange,"*")

The cellrange represents any range of cells like B2:B9. The asterisk between the quotes is a wildcard character that represents any matching number of text characters. There are a few things to note about what are considered text characters:

  • The logical values TRUE and FALSE are not counted as text.
  • Numbers that are entered as text are counted in the wildcard character (*).
  • A blank cell that begins with an apostrophe (‘) is counted.

For example, to count the number of cells containing text in the cell range A2:G9 in the following worksheet, we enter “=COUNTIF(“. Then, to enter the cell range, we select the cells we want to include in the count.

The COUNTIF function is not case sensitive.

excel text functions - Select range for COUNTIF function

Then, we type a comma (,) and the wildcard character (*) surrounded by double quotes.

Press Enter to complete the function entry and view the result in the cell.

excel text functions - Count cells with any text

Count Cells Containing Specific Text

You can also use the COUNTIF function to count how many cells contain specific text characters.

The generic function for counting the occurrences of a specific string text characters is:

=COUNTIF(cellrange,"txt")

Just like in the previous section, the cellrange represents any range of cells like B2:B9. We put the string of text characters we want to find between double quotes.

For example, to count the number of cells containing “Pencil” in the cell range A2:G9 on following worksheet, we enter the following function:

=COUNTIF(A2:G9,"Pencil")

This finds all the cells containing just the word “Pencil” with no other text in the cell. Because the COUNTIF function is not case sensitive, it will find all cells containing “Pencil” or “pencil”.

excel text functions - Count cells with specific text

The COUNTIFS function allows you to count cells with text but exclude cells with specific text characters.

For example, we use COUNTIFS in the following way to find all cells containing any text except for “Pencil”.

=COUNTIFS(A2:G9,"*",A2:G9,"<>Pencil")

For the COUNTIFS function, you first give it the range and the text you want to find. Then, give it the same range again and the text you want excluded. The “<>” is used to exclude whatever text follows.

excel text functions - Count cells with text except for specific text

When using either the COUNTIF or COUNTIFS function, you can add an asterisk on one or both sides of the string to find cells that contain that string surrounded by any number of text characters (or none).

For example, to find all cells containing the letter “j”, we use the following function:

=COUNTIF(A2:G9,"*j*")

Again, because the COUNTIF function is not case sensitive, cells containing “j” or “J” will be counted.

excel text functions - Count cells with specific text in any position

Convert Text to Numbers

If you have a lot of cells that contain numbers stored as text, there are a few ways you can convert the text to numbers.

You can tell if a number is formatted as text when it’s left-aligned in the cell instead of right-aligned. Also, if a number has been forcefully formatted as text using an apostrophe (‘) at the beginning, there will be a green triangle in the upper-left corner of the cell.

To convert text to numbers you can use the Convert to Number option, the Text to Columns feature, or Paste Special. We discuss each of these methods in our article about extracting numbers and text in Excel.

excel text functions - Select Convert to Number on column

Convert a Number to Text

There may be times when you want to store numbers as text. Maybe you’re performing an action on a range of cells and there are certain cells you don’t want to read as numbers, even though they are.

Typing an apostrophe (‘) at the beginning of a number converts it to text. But if you have a lot of cells with numbers you want to convert to text, you can use the TEXT function.

For example, we want to convert the numbers in the B column shown below to text. We type the following function into the cell to the right of the first number.

=TEXT(B2,"0")

You give the function the cell reference for the number to convert and then the number format you want. We’re just converting to a number with no special formatting (not currency or a date, for example). So we use “0” (zero).

Use the AutoFill feature


How to Use Excel’s Flash Fill and Auto Fill to Automate Data Entry




How to Use Excel’s Flash Fill and Auto Fill to Automate Data Entry

When you regularly create Excel spreadsheets and populate them with data, you have to know about Auto Fill and Flash Fill. Let us show you what these features do and how they can help you.
Read More

to copy the TEXT function to the rest of the cells. The numbers become text and are left-aligned.

You can copy and paste the converted values into the original column. Select the cells containing the TEXT function and press Ctrl + C to copy them. Select the first cell in the original column. On the Home tab, click the arrow on the Paste button and go to Paste Special > Values.

You can find examples of the different text formatting available for use in the TEXT function on Microsoft’s support site.

excel text functions - Using the TEXT function

Convert Text to a Date

Have you ever gotten a workbook from someone else in which they entered dates as text, as numbers, or in a format not recognizable as dates? You can convert text to dates using the DATE function.

Here’s the generic format of the DATE function:

=DATE(year,month,day)

For the year, month, and day, we’re going to use the LEFT, MID, and RIGHT string functions to extract the appropriate parts of the text or number we want to convert. We’ll explain the four examples in the image below.

To convert “20171024” in cell C2 to a date, we used the LEFT function to extract the first four characters for the year (2017). Then, we used the MID function to extract the two characters starting at the fifth position as the month (10). Finally, we used the RIGHT function to extract the last two characters as the day (24).

=DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2))

The next example, “2102018” in cell C3, is in a different order. We still use the string functions but in a different order. We used the RIGHT function to extract the last four characters for the year (2018). The month is only one digit in this case, so we used the LEFT function to extract the first character as the month (2). Finally, we used the MID function to extract the two characters starting at the second position as the day (10).

=DATE(RIGHT(C3,4),LEFT(C3,1),MID(C3,2,2))

The dates in cells C4 and C5 look like normal dates, but Excel doesn’t recognize them as dates. In cell C4, the format is day, month, year. So we use the RIGHT, MID, and LEFT functions in the following way:

=DATE(RIGHT(C4,4),MID(C4,4,2),LEFT(C4,2))

In cell C5, the format is month, day, and year, using two a zero in front of a single-digit month. So we use the RIGHT, LEFT, and MID functions in the following way:

=DATE(RIGHT(C5,4),LEFT(C5,2),MID(C5,4,2))

excel text functions - Convert text to dates

Using the DATE function may seem like just as much work as retyping the text as dates. But it’s a good likelihood that the same format was used throughout your workbook if one person worked on it.

In that case, you can copy and paste the function and the cell references will adjust to the correct cells. If they don’t, simply enter the correct cell references. You can highlight a cell reference in a function and then select the cell you want to enter that reference.

Combine Text From Multiple Cells

If you have a large amount of data on a worksheet and you need to combine text from multiple cells, there’s an easy way to do so. You don’t have to retype all that text.

For example, we have a worksheet containing names of employees and their contact information, as shown below. We want to separate the First Name and Last Name and then combine them into a Full Name column. We can also create an Email Address automatically by combining the first and last name.

To do this, we use the CONCATENATE function


16 Excel Formulas that Will Help You Solve Real Life Problems




16 Excel Formulas that Will Help You Solve Real Life Problems

The right tool is half the work. Excel can solve calculations and process data faster than you can find your calculator. We show you key Excel formulas and demonstrate how to use them.
Read More

. To “concatenate” simply means “to combine” or “to join together.” This function allows you to combine text from different cells into one cell. You can also add any other text to the text from other cells.

To combine the Last Name and First Name in one row into the Full Name column, we use the CONCATENATE function in the following way:

=CONCATENATE(B2," ",A2)

Give the CONCATENATE function the text to combine in the order you want it put together. So we gave the function the First Name (B2), a space in double quotes (” “), then the Last Name (A2).

We can also build the email address in the same way. We use the First Name (B2), the Last Name (A2), and then the rest of the email address (@email.com) in double quotes.

=CONCATENATE(B2,A2,"@email.com")

Always put any specific text in double quotes, but do not put quotes around cell references.

excel text functions - Concatenate text from multiple cells

Separate Text Into Multiple Cells

Do you have some cells with mixed format content that you want to separate? For example, if you have a cell containing “14 turkey sandwiches”, you can separate that into the number (14) and the text (turkey sandwiches). That way, you can use the number in functions and formulas.

To get the number out of “14 turkey sandwiches”, we use the LEFT string function.

=LEFT(B2,SEARCH(" ",B2, 1))

First, we give the function the cell reference for the text from which we want to extract the number (B2). Then, we use the SEARCH function to find the first space after the first character in the string.

To get the text out of “14 turkey sandwiches”, we use the RIGHT string function.

=RIGHT(B2,LEN(B2)-SEARCH(" ", B2, 1))

First, we give the RIGHT function the cell reference from which we want to extract the text (B2). Then, we use the LEN and SEARCH functions to determine how many characters in from the right we want to get. We’re subtracting the number of characters from the first space after the first character in the string to the end of the string from the total length of the string.

Get more details about separating text into multiple cells in our article about extracting text or numbers from mixed format cells.

excel text functions - Separate text into multiple cells

More on Working With Text Functions in Excel

Sometimes the spreadsheet you are working on will have too much of text. These will help you simplify it.

You can find more details about the functions we discussed here in our article about text operations


Saving Time with Text Operations in Excel




Saving Time with Text Operations in Excel

Excel can do magic with numbers and it can handle characters equally well. This manual demonstrates how to analyze, convert, replace, and edit text within spreadsheets. These basics will allow you to perform complex transformations.
Read More

, as well as information about some additional related functions we didn’t mention here.


How to Open and Extract RAR Files on MacFree Time Management Course: Proven Strategies to Save 10+ Hours Every Week





Source link

How to Set the Print Area on Multiple Excel Worksheets – Make Tech Easier

Make Tech Easier


At times you may want to print a specific area of a spreadsheet that highlights the salient features you want, rather than bringing an entire worksheet to a meeting. If you want to print a specific part on a worksheet that has the data you want, you can set a print area that includes that specific selection. However, it can be a challenging task if you want to print the same selection on every page of the workbook. In this tutorial you’ll learn:

  • What a print area is
  • How to set a print area in an Excel worksheet
  • How to modify a print area
  • How to set a print area on multiple Excel worksheets

Learning how to set a print area on multiple Excel worksheets will not only save you time but will also allow you to print only the information you want.

In Excel a print area allows you to select specific cells on a worksheet which can then be printed off separately from the rest of the page. It simply refers to a range of cells you designate to print when you don’t want to print off an entire workbook.

When you hit Ctrl + P on a worksheet that has a defined print area, only the print area will be printed. You can set multiple print areas in a single worksheet. In this case each print area will print as a separate page.

Setting a print area is simple and straightforward. Just open an Excel worksheet and highlight the cells you want to print. Click the “Print Area” option on the Page Layout tab, and in the “Page Setup” section select “Set Print Area.”

microsoft-excel-set-print-area

Keep in mind that the print area will be saved once you save the workbook. Now every time you want to print that worksheet, it will only print the data defined in the print area. To clear the print area, go to the “Page Layout tab -> print area -> clear print area.”

You can modify a print area by adding adjacent cells. Note that the option to add cells will only be visible if you have an existing print area. To add cells to an existing printing area:

1. Select cells that you want to add.

2. Navigate to the Page Layout tab, and on the Page Setup group click Print Area, then select Add to Print Area.

microsoft-excel-add-to-print-area

Note: only adjacent cells can be added to an existing print area. If the cells you want to add are not adjacent to the print area, the system will create an additional print area.

If you use Excel regularly, you have probably  created multiple individual sheets in a single Excel workbook. Often you’ll find that some of your workbooks have sheets that are identical in every way save for input data.

For example, a monthly sales report is likely to have around thirty sheets identical in every way except for the figures. Since all the cells are in the same range and alignment, it’s possible to define a print area that will apply to all the other sheets. Let’s look at how to do it.

1. Open your workbook and select the first sheet.

2. Highlight or select the range of cells you want to print.

3. While holding down the Ctrl key, click on each of the other individual sheets you want to print.

microsoft-excel-print-multiple-sheets

4. Click Ctrl + P and then select “Print Selection” in the Print settings. The system is set to print the active sheets by default which means it will print the entire worksheet. Changing that to “Print Selection” ensures you print only the cells that you have highlighted.

microsoft-excel-print-current-selection

5. Click “Preview” to make sure you got it right. Since each print area will print as a separate page, check the number of pages in the preview to ensure all sheets have been captured.

microsoft-excel-print-preview

6. Click Okay to print.

Defining a print area is a great way to print only the content you want for your presentation. If you want more customization over your printing options, you can try third-party applications such as Kutools for Excel. However, some of the third-party apps are not free and may require a monthly subscription.

Want to become a spreadsheets pro? Check out our article on the 9 add-ons for Excel to make spreadsheets easier.

Image credit: Business charts with keyboad by DepositPhotos



Source link

How to Combine Two or More Excel Spreadsheets – Make Tech Easier

Make Tech Easier


Microsoft Excel is a great application for you to keep your data organized and working for you, but all those numbers can sometimes get unwieldy when scattered over too many spreadsheets. Fortunately, you have a few built-in options that make it easy to combine Excel spreadsheets into single files or even single pages. The tools below are powerful enough to meet most users’ needs – though if you find yourself trying to deal with hundreds of spreadsheets, you may want to look into using macros or Visual Basic.

This method sounds almost laughably simple, but don’t dismiss it too quickly. If you just need to move data between a few spreadsheets, this is the fastest and simplest way – though it is impractical for large-scale jobs. Excel’s copy-paste options don’t come in just one flavor, either! When you copy your data and go to paste it, right-click on where you want to paste and check out the “Paste Special” options. Here are a few of the most useful:

Values/Formulas: If you have a numeric cell that is calculated using a formula, you can choose to either copy only the value, or you can keep the formula. The default copy-paste option in Excel keeps the formula.

excel-special-paste-1

Transpose: Changes the rows to columns and the columns to rows. This can be very helpful if, for example, you’ve received spreadsheets from people with different ideas about where the labels should go.

excel-special-paste-2

Paste Link: This is a handy feature when combining data from multiple sources. This will link the pasted cell to the source cell, whether the source is in the same workbook or somewhere else. Whenever the source is updated, the pasted cell is updated as well, so if you’re looking to combine data from spreadsheets into a continuously-updated master sheet, you can use this paste function to easily link the cells.

If you’re working with more than a few workbooks/worksheets, copy-pasting will get old fast. Luckily, Excel has a built-in feature that will move worksheets between workbooks.

1. Open the workbook you want to move sheets into and the workbooks you want to move sheets from. Excel requires both the source and the destination workbook to be open to move worksheets. The example below shows them both on the same screen using the “View Side by Side” option.

2. Right-click on one of the sheets you want to move, and when the menu opens, click the “Move or Copy” button.

excel-move-and-copy-1

3. The menu should show you a dropdown list of all the open spreadsheets. Select one to be your destination. Choose if you want your sheet to be at the beginning, end, or somewhere in the middle of the destination workbook’s existing worksheets. Don’t ignore the “Create a copy” checkbox! If you don’t check it, your sheet will be deleted from your source workbook.

excel-move-and-copy-2

4. Check your destination workbook. The moved or copied worksheet should appear with the same name as in the source workbook, but may have a (2) or another number after it if there is a duplicate name in the destination.

excel-move-or-copy-3

The Consolidate feature is a very flexible way to push multiple worksheets into one. The best thing about it is that it automatically detects and organizes your row and column labels and merges identical cells from different sheets.

1. Create a new workbook or worksheet for your consolidated data, then open the source worksheets.

excel-consolidate-1-2

excel-consolidate-11

2. Open the new spreadsheet and go to the “Consolidate” button in the “Data” tab.

excel-consolidate-1

3. Notice that there are several functions listed here. Each function will combine cells with the same labels in different ways — sum, average, keep the minimum/maximum value, etc.

excel-consolidate-2

4. The boxes below the functions ask you where it should find your labels. If you have labels in your topmost row or leftmost column, check the appropriate boxes. Otherwise, your spreadsheets will just mash themselves together like a copy-paste.

5. The “Create links to source data” box will have the cells in your destination spreadsheet reference the ones in your source spreadsheets so that the data will automatically update.

excel-consolidate-3

6. Hitting the “Browse” button opens your file explorer. Select the spreadsheet you want to consolidate.

excel-consolidate-5

7. Click the “Reference” button and open the spreadsheet you just selected. Here you can highlight the data you want to merge.

excel-consolidate-6

excel-consolidate-7

8. Hit the Enter key and then the “Add” button. This should put the selected data into a merge list.

excel-consolidate-8

9. Repeat the above steps for as many worksheets/workbooks as you want to combine.

excel-consolidate-9

10. Click “OK” to combine the selected data into your new spreadsheet and check that it worked correctly.

excel11

These methods combine a user-friendly interface with a decent amount of power. There aren’t many jobs that these three tools, in some combination, won’t be able to tackle, and they don’t require any experimentation with VB code or macros. As with all things in Excel, though, it helps quite a bit if your data is well-organized before you start — name your workbooks and worksheets logically, check that your rows and columns are laid out the way you want, and make sure your references line up!

Image credit: Laptop on Desk office and Graph analysis spreadsheet by DepositPhotos



Source link

How to Create a Marathon Training Plan With Excel

How to Create a Marathon Training Plan With Excel


Gearing up for a big race? This can often take months of training, and the best way to make the most of it is to keep a close eye on your performance.

You can use Excel to create a spreadsheet where you can log all your runs for future reference. What’s more, if you set it up correctly, it will create graphs


How to Create Interactive Excel Charts With the INDEX Formula




How to Create Interactive Excel Charts With the INDEX Formula

We’ll show you how to use the INDEX formula and a basic drop-down menu to create interactive Excel charts. This simple process will turn static charts into dynamic information hubs.
Read More

and other readouts that are useful for planning out your runs, plotting your journey toward race day, and looking back on your performance over the entire training period.

Here’s how to use Excel to supercharge your marathon training.

Setting Up Your Document

First, you need to set up the basics of your spreadsheet. Open up Excel and create a new document. Then, add your column headers.

setting up your marathon training plan

I would recommend adding Date, Run, Distance, Time, and Done? at the very least. The other columns are optional, and in fact, you can swap them out for other things you might like to track if you so desire.

However, it might be beneficial to keep a record of things like how much you had to eat before you went out, or how much water you drank. We’re going to produce graphs


How to Make a Graph or Chart in Excel




How to Make a Graph or Chart in Excel

Excel has robust visualization features, making it easy to create powerful graphs and charts in Excel. You can even use Excel to create self-updating graphs!
Read More

that tie these metrics to your performance, so you might be able to discern things about the impact they have on your ability to run.

Next, fill out the columns marked Date, Run, and Distance with the appropriate information from your training plan. Since this spreadsheet is going to help you train for a marathon, we’re assuming that you have some kind of plan on how far you’re going to run each day on your way to 26.1 miles. If you don’t have that information to hand, there are various training plans available online.

setting up your marathon training plan

Remember, this is for your usage, so feel free to add more or less information as you see fit.

Adding Check Boxes

One of the main purposes of this document is to give a sense of progress toward the big race. To do that, we’re going to employ checkboxes


How to Create a Checklist in Excel




How to Create a Checklist in Excel

A checklist is a good way to keep track of tasks or processes. We’ll show you how to make a checklist in Excel, one simple step at a time.
Read More

.

Head to the Developer tab and select the Check Box.

adding checkboxes to your marathon training plan

Place this in the Done? column.

adding checkboxes in your marathon training plan

Get rid of the text label, then right-click the checkbox and click Format Control.

checkbox format control in excel

On the next screen, make a Cell link to the cell that it is placed in, which in my case is H2.

cell link in excel

Click the checkbox. You should see that TRUE appears in the cell behind it. I’ve made each row twice as high in order to make it easier to click these checkboxes accurately.

adding checkboxes in your marathon training plan

The TRUE or FALSE readout is a useful way to check that it’s working, but we don’t need it to actually display, so turn the font color in each cell white. Repeat this process for all the cells in that particular column. After changing the font color on one cell, you can use the format painter (paint bucket icon) or Ctrl + Y (repeat) to apply it to any other cells.

Add a Countdown

Each time you complete a run, you’re going to check it off. With that in mind, you can use your checkboxes to create a countdown to race day. For the purposes of this section, I’m going to add in a temporary race day row (adding every single run to my schedule would clutter the screenshots).

Next, we’re going to use a function to do a little behind-the-scenes work. Choose a cell that’s out of the way, and enter the following code:

=COUNTIF(H2:H11, FALSE)

I’m also adding a text cell that reads DAYS UNTIL THE RACE. You should end up with something like this.

add a countdown to your marathon training plan

This formula will count however many unticked boxes are in the specified area, thereby calculating how many days away our race is.

Recording Runs

Now, we’re going to add some data so that we can set up our self-updating charts


How to Create Self-Updating Excel Charts in Three Easy Steps




How to Create Self-Updating Excel Charts in Three Easy Steps

We’ll show you how to make your Excel charts self-update. Just add new data and watch how they automatically show up in your graph. It’s quick and easy.
Read More

. This is fairly straightforward, but there are a few things to keep in mind so that our times are recorded properly.

When you enter a time into Excel, make sure you use hh:mm:ss format. So, if your first run was logged at 28 minutes and 32 seconds, you would input:

00:28:32

Keeping this consistent will avoid any difficulties with your charts later on. I’m going to enter in some dummy data to use for the time being.

Once you’ve added some data, highlight the whole table and press Ctrl + T to make it into a table. You may need to recolor the TRUE and FALSE text cells to ensure they stay hidden.

recolor certain cells in excel

Now that your data has been properly formatted


9 Tips for Formatting an Excel Chart in Microsoft Office




9 Tips for Formatting an Excel Chart in Microsoft Office

First impressions matter. Don’t let an ugly Excel chart scare off your audience. Here’s everything you need to know about making your charts attractive and engaging in Excel 2016.
Read More

, you can set about adding some visualizations.

Making Charts

Next, highlight the Date and Calories columns, head to the Insert tab, and select a Line graph from the Charts section.

making charts for your marathon training plan

Go to the Chart Design tab and click Select Data.

chart design in excel

First, use the dropdown menu to select Connect data points with line.

connect chart data points in excel

Next, add a new series using the plus button. Copy and paste the text in the field Horizontal (Category) axis labels and add it to Series2, then do the same with the text in the field labeled Y values. Change the references in the latter to match the desired column. (I want to add my water consumption data to the same chart, so I’m going to change both E’s to F’s.)

making charts for your marathon training plan

We set up our chart to source data from cells we haven’t filled yet, which means that when we add new data, it will be recorded on the graph.

making charts for your marathon training plan

Obviously, these lines are pretty inconsistent since they take into account various different run lengths. You might want to limit them to either a regular run of a set distance or the steadily increasing runs taking you closer to 26.1 miles.

Implementing Conditional Formatting

Finally, we’re going to add a quick way to assess our individual performances. Throughout my marathon training plan, I’m going to be going on 5-mile runs. I’m going to set up a way to check whether these runs are better or worse than my average


How to Calculate Basic Statistics in Excel: A Beginner’s Guide




How to Calculate Basic Statistics in Excel: A Beginner’s Guide

Microsoft Excel can do statistics! You can calculate percentages, averages, standard deviation, standard error, and student’s T-tests.
Read More

at a glance.

This is going to require some more behind-the-scenes work. Choose a cell that’s out of the way, and enter the following formula:

=AVERAGEIF(C2:C11, "5 miles", D2:D11)

Here’s what’s going on above. Excel checks the first range for the specified string, which is 5 miles. It then averages the corresponding cells in the second range, which are the times for those runs. Amend the specific cell references if your spreadsheet is set up differently, or has more rows.

using conditional formatting in your marathon training plan

Next, highlight all the rows that will eventually have times for a 5-mile distance recorded in them, even if they’re blank at present. Head to Home > Conditional Formatting > Highlight Cells Rules > Greater Than.

using conditional formatting in your marathon training plan

Change the cell reference to wherever you put the formula.

conditional formatting rules in excel

Now, do the same but this time choose Less Than from the Highlight Cells Rules menu and make sure to choose a different color for the highlight. You can either color the average time in white to hide it or make it a part of your readout section.

using conditional formatting in your marathon training plan

Feel Free to Customize

The techniques discussed above are just a starting point. Everyone’s preparations for race day will be different, so don’t be afraid to experiment, and tweak things to your liking.

customize your marathon training plan in excel

Above is my final version. It’s not full of bells and whistles — in fact, it’s pretty spartan. But it gets the job done, and it’s easy to read. If you wanted to add more information, that’s certainly an option. I’ve got a couple of readouts to the right-hand side that introduce some basic dashboard-like functionality


3 Excel Dashboard Tips You Have to Try




3 Excel Dashboard Tips You Have to Try

Use our Excel dashboard tips to organize and show off data from a complex spreadsheet. With your neat dashboard, you’ll see key data points at a glance.
Read More

.

It wouldn’t be too difficult to add similar readouts for more data of this kind if you were logging it. Heart rate would be one such addition. Think of this as a basic version that you can add to. What are you going to focus on as you work toward your race? By modifying this structure, you should be able to track that, too.

Do you have tips on how to use an Excel spreadsheet to prepare for a marathon or half-marathon? Are you having trouble with any of the steps in this guide?


3 Microsoft Word Formatting Tricks You Should Really Know5 Handy Tips for Organizing Your iPhone and iPad Apps More Effectively





Source link