Removing all images in Word or Excel

Sometimes when copying information from a SharePoint page (or any website for that matter) to an Excel or Word document, you can end up with images that you don’t want. You could click each of the images and hit DELETE to get rid of them, but here is a faster way:

In Excel

Use the Go To command (Ctrl + G), then choose Special, then Objects.

The delete and backspace keys will remove the images from your worksheet.

In Word

Open the Find & Replace tool (on the PC, it’s Ctrl+H)

To remove all images at once, place ^g in the find field, leave the replace field blank, and then choose Replace All

Excel: COUNTA, COUNTBLANK

Whereas COUNT counts only the cells that have numbers in them, COUNTA and COUNTBLANK are a little more loose with the definition of what they count. They count all cells and report on the information.

COUNTA

COUNTA counts cells that are not blank.

You might use blank spaces to create a view that separates certain types of data so that it’s easier on the eye, such as the T-shirt example below.

However, if you want to know the total number of sizes and colors, you don’t want to count those spaces.

In that case, you would use =COUNTA(B2:B18), which would give you a number of 15

Excel: COUNTA example

COUNTBLANK

COUNTBLANK does exactly what you would expect it to do: it counts the number of blank cells.

=COUNTBLANK(A1:C18) gives me 6 because I have 6 blank cells.

Excel: COUNT, COUNTIF, COUNTIFS

Many times in Excel you will want to get an idea of how many records you might have, or you want to count certain things within your records. To do that you can use COUNT, COUNTIF, COUNTIFS, COUNTA, and COUNTBLANK.

I will cover COUNTA and COUNTBLANK in another post.

COUNT

The COUNT formula counts numbers and ignores empty cells, text fields, TRUE/FALSE logical values. It only counts numerical values.

=COUNT(1,3,”VT”,5,8,”Hokie”) would return 4, as there were 4 numbers within those values.

=COUNT(B1:B18) in the T-shirt example below would return 0 as there are no numbers in the B column.

=COUNT(C1:C18) in the T-shirt example would return 15 as there are 15 numbers in the column.

Excel: COUNTA example

COUNTIF and COUNTIFS

The COUNTIF formula gives you a number count for any criteria you set, text or numerical.

For example, Housing and Residence Life might collect data showing incident reports for their residence halls.

Excel: COUNTIF formula example

Using COUNTIF, we can take all the incident reports from all the buildings, and then pull out information by building, by day of the week, or by type of incident.

The way it works, is you first choose the range or values you want the formula to examine, and then select the criteria.

=COUNTIF(range, criteria)

The formula looking at the number of incidents in Lee would read as

=COUNTIF($B:$B,E3)

You could also type it as =COUNTIF($B:$B,”Lee”)

COUNTIFS

The COUNTIFS formula lets you set multiple criteria on any cell content, numerical or text.

The way it works, is you first choose the range or values you want the formula to examine, and then select the criteria, then the next range, then the next criteria and so on.

=COUNTIF(range, criteria, range, criteria)

So using the above example, a formula that reads

=COUNTIFS(B:B,”Lee”,C:C,”Alcohol”)

would return 2 because in the full list of data, two alcohol incidents occurred in the Lee building.

Excel: what does the dollar sign do?

Oftentimes when working with Excel, you’ll notice that dollar signs ($) appear in front of, or in the middle of cell references.

For example:
$A1
A$1
$A$1

What does Excel do with this information and why do you need it?

It means that the row or column which comes after the dollar sign is anchored or absolute, and therefore will stay the same as you copy and paste formulae to another cell.

Excel by default uses “relative” reference to a cell, which means that the formula changes as you copy it to another cell.

For example, a formula in column D that states =SUM(A1:A5) will become =SUM(B1:B5) when it is copied into a cell in column E.

By using the dollar sign, you can ensure that either the row stays the same, the column stays the same, or that you want a specific cell referenced.

So using the references from the above example:

$A1 means that the formula will always pull information from column A, regardless of which column it appears, but it can change the row depending on where the formula appears.

A$1 means that the formula will always pull from the first row, regardless of which row it appears, but it can change the column depending on where the formula appears.

$A$1 means that no matter where the formula is placed on the sheet, it will always reference the A1 cell.

Why do we care?

We generally care if we’re trying to reference information in a list. We don’t want the number of items changing in the list, so if you have information in rows 1 through 20, then you don’t want the first row to have a formula looking at rows 1:20, and the second row to have a formula looking at rows 2:21, etc. You would want to make sure your range at least has dollar signs in front of the row numbers, so that A$1:A$20 will always pull rows 1:20 from the column.

Excel formula: SUM vs SUMIF

Excel is a useful tool any time you want to analyze or manipulate data. I use it regularly and thought I would create a few posts about its usefulness.

Excel uses formulae to perform its calculations. You begin a formula with the equal sign (=) and then you can tell Excel to do a bunch of things, one of which is add numbers in a row or column.

In order to do that, you use the SUM formula.

The SUM formula is generally used on a range of cells.

For example the formula =SUM(A1:A5) will add the first five numbers column A.

If you had the numbers 10, 20, -15, 25, and 30 in the first column, using =SUM(A1:A5) would generate 60 as your total.

Now let’s imagine that you only want the positive numbers to be added. You can use the SUMIF formula to accomplish that. Taking the same numbers above: 10, 20, -15, 25, and 30, using =SUMIF(A1:A5,>0) would generate 85 as your total.

There is a third piece to the SUMIF function that lets you compare non-numerical information, and then add the numbers that match. For example if you had a list of T-shirts in different colors and sizes, and you wanted to grab your totals by size or by color, you would use SUMIF.

Excel: SUMIF function

To give an example, the formula in F4 would read:

=SUMIF(A1:A16,E4,C1:C16)

So let’s take the parts of that:

  • The first part is – look at the information in column A, which is the color column
  • The second part is – compare that information against E4, which is Maroon
  • The third part is – take the numbers from the quantity column to add up the total number of Maroon T-shirts

Email merges

We’ve heard of using Mail Merge for sending out letters. Have you ever had the need to use Mail Merge with emails?

The process for creating mail merges for outlook is almost the same as for letters. You start with a word document and an excel spreadsheet with contact information.

Preparing the recipients list

Create a spreadsheet containing at minimum, these three columns:

  • First Name
  • Last Name
  • Email

Make sure the email column contains no special characters (other than the @ sign, the hyphen (-), the underscore (_) and the period (.) of course) and to make things easier for yourself, include a header show with these column labels, so you can find your fields more easily.

Prepare the email message

  • Start Microsoft Word and begin a new blank document.
  • Switch to the Mailings ribbon.
  • Click on the Start Mail Merge menu and select the E-Mail Messages option.
  • Click on the Select Recipients menu and select the Use Existing List option.
  • Browse and select the Excel spreadsheet you created earlier, and then click on the Open button.
  • In the Select Table window, click on the name of the sheet that contains your recipient’s information.
  • If you have options for Sheet1, Sheet2, and Sheet3, the information is probably on Sheet1.
  • If you entered column headings in the first row of your spreadsheet, make sure the First row of data contains column headers option is checked.
  • Click on the OK button.

Compose the body of your message using Word

You can switch back to the Home ribbon to add formatting including bold, italics, font colors, and headings.

Not all formatting will visible to all recipients. Outlook users will see most of the formatting. Web mail users will see bold, italics, and lists but not font styles or colors.

To customize the contents of your message with information from your spreadsheet

  • Position the cursor where you want to insert the data.
  • Switch to the Mailings ribbon.
  • Click on the Insert Merge Field menu and select the field containing the data you want to insert.
  • Save the body of the email message the same way you would save any other Word document.

Send the email messages

  • Switch to the Mailings ribbon.
  • Click on the Finish & Merge menu and select the Send E-Mail Messages option.
  • From the To drop-down menu, select the field containing the email address of each recipient.
  • In the Subject text box, enter the subject line used for the email message.
  • From the Mail format drop-down menu, select the HTML option.
  • For the Send records radio button, select the All option.
  • Click on the OK button to send the messages.

More Tips

If you customize the message for each recipient, use the Preview Results button on the Mailings ribbon to see the data from your spreadsheet in the body of the message instead of the field names. Use the arrows to right of this button on the ribbon to preview different records from your spreadsheet.

Customizing the email message for each recipient is easiest when the information is split into several columns. For example keeping the first name and last name in separate columns is better than keeping them in a single column called name. You can always put two fields together in your message, but pulling fields apart is much more difficult.

If you send newsletters or flyers as PDF attachments, consider publishing the PDF on your SharePoint site and then using email merge to send a notice with a link. Later you can check your site’s statistics to determine how many people downloaded the PDF.

This last tip goes for other types of documents as well. One great reason for sending a link to a cloud version of the site rather than an actual file is that if you discover an error in your file, you can fix it without needing to resend the document to everyone.

You may want to add yourself as the first recipient in your spreadsheet so you can test the message delivery. Use the Preview Results button to view the message you will receive. Follow the sending instructions, but change the Send Records option from All to Current. This will send only the message currently being previewed through Outlook.

Copying and pasting from Excel into SharePoint’s datasheet view

SharePoint’s Quick Edit, or Data Sheet view can be a lifesaver when you need to copy and paste lots of information at once. Oftentimes data is originally created in Excel. Either you created it there because it can be easier to work in Excel sometimes, or some emailed you a spreadsheet with information that you want to import into SharePoint.

Just a note: copying and pasting from Excel to SharePoint ONLY WORKS IN INTERNET EXPLORER, so if you find yourself trying this trick and it doesn’t work, check the browser you’re using.

You need to make sure that the column order in Excel matches that in SharePoint, and that the column types match too. In order words, don’t try to copy text into a date field, or a date into a people & groups field. They need to match.

In your list view, there should be a field with a little check mark, which is your selector field. Within the datasheet view, that little check mark contains a tiny empty box to the left of your data. If you click on it, it will select the entire row. It’s best to paste when you have the row selected as shown here.

Datasheet - Paste

Sometimes if you have the cursor into the first field of the row, SharePoint will try paste all you information into that cell. By selecting the whole row, it will make sure the information will be pasted into each field.