Computing Services

The department behind IT services at the University of Bath

Posts By: John Baker

Skype for Business whiteboard, poll or Q and A page

  , , , ,

📥  IT Literacy

Hi again,

Since writing my last blog on using Skype for Business, and receiving a question on the subject on one of my training sessions, I’ve had a play with a few additional Skype for Business features that I’d like to share with you. These features are available if you’re in a video call, audio call or Instant Message conversation.

The three items are:

  1. Whiteboard
  2. Poll
  3. Q&A page

These items are all accessible through the Present button () – just click on the button and click on More to see the three options. Note: the Present button is greyed out/unavailable if you’re conversing with someone who’s not using Skype for Business.

If you select Whiteboard the whiteboard will appear on your screen and those of everyone who is included in the conversation:

Anyone can place items onto the whiteboard. By default, you can type text onto the whiteboard – just click where you want to place it and type (everyone will see your text as you type it). A tool bar to the right of the whiteboard enables you to do some other things, including:

  • Change the mouse pointer to a pen, so you can draw, try to write, etc. and change the ink colour (you could, for example, ask each contributor to use a different colour), if you wish,
  • Draw straight lines or a square/rectangle or circle/oval, again, setting a different colour before you draw it,
  • Select a highlighter to help you emphasise something,
  • Select a laser pointer to draw everyone else’s attention to what you’re talking about (can’t really see this working with an IM, but might be useful if you’re talking),
  • Save a copy of the whiteboard (click on the three dots, select Save As, then select where you want it and give it a name).

If you select Poll you will see this window:

Type in the question, type in each of the possible responses, or Choices.  Change the Poll name from Poll to something else, then click on Create to launch it into the conversation for everyone to make their selection. As choices are made, you will see percentages appear next to each option. You also get to close the poll when you want to.

If you select Q & A Instant Messaging gets switched off and everyone will see a window inviting them to type a question:

Anyone can ask a question, but only those who are presenters can answer questions (using an Answer option that appears under the question).

If you have had/do have a go at any of them, please add a comment to say how useful they are in the real world, any problems/limitations you encountered and any workarounds or fixes you’ve found.

Finally, if you work at the University of Bath and have not used Skype for Business (maybe you’re waiting for a web cam and headset), just have a go at sending an IM. If you're not sure how to do that, or want to know more, book onto a Skype course, it will be good to see you!!

Bye for now,

John

 

Using Skype for Business

  , , , ,

📥  Computing Services

Hi all,

You are probably aware, if you work at the University of Bath, that Skype for Business has just been rolled out.

“So what?” you may ask.

So, have a go at using it to communicate with someone, especially where there are benefits over using email or the phone.

“What benefits?”

Instant Messaging

Sometimes I’ll use an Instant Message to contact someone, or someone will use IM to contact me. One of the main benefits for me is that I can quickly see that they’ve read my message and are responding, as Skype for Business tells me ‘… is typing a message’ – with an email, you can’t really be sure that they’ve even opened the message (unless you assigned a Read Receipt tag AND they click to let you know they’ve opened it).

If you want to use Instant Messaging but the other person sent you an email, you can still respond with an Instant Message – just click on the IM button in the Respond group within the Message ribbon.

You can add attachments, flag the message as ‘high importance’ and even add a ‘smiley’ or two!!

If you prefer to use emails because they give you a record of your conversation, guess what? So do IMs – they are stored in an area called ‘Conversation History’ in your Outlook folder. Worried about them not seeing the IM? If you miss one it will appear in your Inbox the next time you look!!

Phone/video calls

And if you have a headset and/or webcam, it’s even more useful.  You can call someone (audio or, if you have a webcam and don’t mind people seeing you, a video call) or a whole group of people.

To call someone, just find them in the list, see if they’re available and hit the ‘call’ button.

Conference call/IM

There are a few ways to include more than 1 other person in your conversation (or even an Instant Message), including (there are probably others – let me know if you find a good one):

·         Add extra people, 1 at a time

·         Start with a call to a group of people

·         Arrange a Skype Meeting in your Outlook calendar

Show them what’s on your screen

Whether you’re calling someone or using an IM, you can present a file, program or your whole desktop – just click on the Present button and say what you want to present: Desktop, program (any application/file that is currently open) or PowerPoint file (will open the file and automatically start the slide show). You can even give the other person control, so they can do the work!!

So consider having a go at sending an Instant Message the next time you’re sending an email.

If you want to know more or have a go at audio/video calls and conference calls, join me on one of the Skype for Business training sessions.

Bye for now,

John

Using SUBTOTAL in Excel

  ,

📥  Computing Services, IT Literacy

Hi again,

This blog looks at the Excel function SUBTOTAL and how you might find it useful:

The SUBTOTAL function has two parts within the brackets: the second part is the easy bit - the range of cells you're using; the first part is what (out of 11 options) you want to do with the contents of that range of cells, e.g. add them together (number 9), average them (1) or count them (2 or 3) - the full list is below.

So, if I wanted to use it to sum the cells A3 to A40, I could create the formula =SUBTOTAL(9,A3:A40) instead of using AutoSum to create =SUM(A3:A40)

So, why (I hear you ask) would I want to create a longer formula to do the same thing as SUM? Because it's not quite the same - here's a couple of benefits of using SUBTOTAL:

  • If I have a single column of numbers intermingled with sub-totals and use =SUM to create  a grand total at the end, the grand total would add the numbers and the sub-totals together (for example, if I had the numbers 1 to 4 in B3 to B6 and again in B8 to B11 with sub-totals in B7 and B12, a total of the column would be 40 instead of the correct 20), whereas using the SUBTOTAL option would ignore the subtotals and only add the typed-in numbers together.
  • This second reason is more useful for me: when I filter a list with a SUM at the bottom, the sum still shows the sum of all numbers (hidden and visible) - SUBTOTAL just shows the sum (or any function I've chosen instead of sum) of the visible rows.

Here are the 11 function numbers (the first part within the brackets) for SUBTOTAL:

  • 1 - AVERAGE
  • 2 - COUNT (counts all cells with numbers in them)
  • 3 - COUNTA (counts all cells with contents - numbers or text)
  • 4 - MAX (largest number)
  • 5 - MIN (smallest number)
  • 6 - PRODUCT (multiplies all of the numbers by each other)
  • 7 - STDEV (estimates standard deviation)
  • 8 - STDEVP (calculates standard deviation based on the entire population)
  • 9 - SUM
  • 10 - VAR (estimates variance in the specified range)
  • 11 - VARP (estimates variance based on the entire population)

If you use the AutoSum button with a filtered list, it will automatically create a SUBTOTAL(9, function instead of a SUM - I find this a useful time saver.

One final thing: for staff and students at the University of Bath, we're offering Excel workshops, where you can book a place and then bring your workbook(s) along (or log into one of the training PCs with your normal Bath username) to see how we can help resolve any improvement requirements, issues, niggles, hassles or problems. For more information, click here then select Tutor Led Training, then Excel: Workshop to see the dates/book a place.

Bye for now,

John

 

Closing Office applications

  

📥  Computing Services

Hi again,

One of the differences I noticed with Office 2013 and 2016 compared with previous versions is that most applications (Outlook being the only exception, I think) have dropped the Exit option within File.

All we have is Close which is no different to clicking on the cross at the top-right of the file - it just closes the file, not the application (as does pressing Alt&F4). So, if you have 10 documents open in Word, all saved, you have to close each one individually (in Office 2010, File, Exit would close all 10 occurrences of Word, pausing on each, if necessary, to ask if you want to save the changes).

My solution to this is to add the Exit command to my Quick Access Toolbar (the small bar at the top-left of the window), alongside Save (yep, they still use a picture of a floppy disk - who remembers them??) and Undo:

  1. Click on File and select Options
  2. Select Quick Access Toolbar in the list on the left
  3. Change Popular Commands to All Commands (I guess that makes our command an unpopular one!!)
  4. Click in the list of commands and type an F and then, towards the end of commands beginning with E, locate/select Exit.
  5. Click on Add in the middle of the window and click on OK.

Now I just need to click on the small cross on my Quick Access Toolbar to close all of the files I've already saved and be prompted to save the others, and I can just click on Save All if I wish!

Also, to answer another question (probably one that most of you have never thought of!), if you have a single file open in Word or Excel and want to close the file, but keep the application open, hold down Ctrl and press F4 (Alt&F4 would close the window).

Bye for now,

John

 

Margins for double-sided printing

  ,

📥  Computing Services

Hello again,

We've recently been asked if it's possible in Microsoft Word to have a wider margin on the left on odd pages and the same sized margin on the right on even pages (e.g. for when they're printed double-sided and bound/stapled).

The answer is to mirror your margins - I've tested this in Word 2016 and 2013 and it works in both, and should work in other versions of the software.

To mirror your margins, click on the Margins button on the Layout ribbon and (if it's there) select Mirrored. Then drag the margins markers on the ruler bar to the size you want. If your margins were set before you selected Mirrored you may need to reset them (I found that I had to reset the left margin in Word 2013, as it had been reduced from 5cm to 3cm).

If Mirrored is not visible, select Custom Margins at the bottom of the list and change Multiple pages from Normal to Mirror Margins. Then use the Inside and Outside settings at the top of the window to set your margins before clicking on OK or click on OK and drag the markers along the ruler bar.

Another irritation when changing the left margin can be just getting the right spot to drag (between first line indent and hanging indent) - I'll sometimes move one of the indent markers along a bit, change the margin and then move the marker back, especially if using a mouse pad on a laptop.

Bye for now,

John

 

Editing pdf files

  ,

📥  Computing Services

A few people have asked me about editing a pdf file.  You can do this in Microsoft Word 2013 or 2016 as well as in Adobe Acrobat.

Editing a PDF using Microsoft Word

There are a couple of ways that I open the pdf file. Most of the time I locate the file in File Explorer and then notice it’s a pdf file. To open it from here, right-click on it, select ‘Open with’ and then select ‘Word…’. Alternatively, you can start Word and locate/open the file as you would a normal Word document.

When you open the file you will see a message about converting it to an editable Word document (and that this may take a while) – just click on “OK”.

Once it's open in Word, just treat it as a normal Word document – make your changes and save it. If you click on the “Save” button or press [Ctrl]&[S], you’ll see the 'Save As' window with the same file name as the pdf and the document type reset to 'Word document'. If you want to save the file as a pdf, just click on the drop-down arrow by ‘Word Document’ and select ‘PDF’.

Check over your document

In the past, I’ve noticed a few bits that have been ‘lost in translation’ when opening a PDF file, including contents pages (which I have removed and created again) and one or two headings that have lost their first letter to the previous page (resolved by a quick bit of deleting and retyping), so it's a good idea to proof read (or at least spell check).

Microsoft provides guidance on editing PDF content in Word.

Bye for now,

John

 

Sorting in Excel

  , ,

📥  Computing Services, IT Literacy

The other day, when I was showing the basics of creating a worksheet in Excel, I was asked  a really good question - one that made me think “I’m sure you must be able to!” and “Why have I not heard (or thought of) this question before?”

The question was about sorting, not sorting the rows, but sorting columns (for example into alphabetic order). I've written this blog to feature a workaround to achieve this and thought it might be useful to include some other aspects of sorting in Excel.

First, a pitfall to avoid: don’t sort when you have only a part of your table selected (unless you only want to sort those cells and keep the others where they are) as the data in your table will probably become meaningless. If you only have a single cell selected, Excel normally gets it right and sorts the whole table around that column (but sometimes I still check to make sure – you can always click on “Undo” if it gets it wrong).Selecting the whole table first is my normal belt & braces approach.

Basic Sort:

Just click within the column you want to sort/group by, click on “Sort & Filter” on the ‘HOME’ tab and select ‘Sort A to Z’ or ‘Sort Z to A’ if the column contains text, ‘Sort Smallest to Largest’ or ‘Sort Largest to Smallest’ if the column contains numbers or ‘Sort Oldest to Newest’ or ‘Sort Newest to Oldest’ if the column contains dates.

Custom Sort:

I often use ‘Custom Sort’ to group/sort by more than one field. Click on “Sort & Filter”, ‘Custom Sort’ to see the ‘Sort’ window. Click on the drop-down arrow by ‘Sort by’ and select the first field you want to group/sort by (if it doesn’t show the headings, place a tick by ‘My data has headers’) and the order (e.g. 'A to Z' or 'Z to A'). Then click on “Add Level” and repeat the process for the next field you want to group/sort by – repeat this for as many extra sorts/groups as you need. Click on “OK” to see everything sorted into your new sequence.

I have included a video of the next couple of items.

Sorting by day or month order:

As well as 'A to Z' and 'Z to A', you can also sort by a custom list. This is most useful if I want to sort records by month (as January, February, March, April, etc. not April, August, December, February, etc.). Just use the drop-down arrow under ‘Order’ in the custom sort window, select ‘Custom List’ and select the list you want. Anything not matching an item in the list will go to the bottom of the sort.

Sorting columns:

Finally, how would I sort column headings into (for example) alphabetic order? There are 2 ways: I could use the Transpose option within Paste Special or change the options in a custom search:

Using Paste Special (Transpose): Select the whole table (all headings and data) and copy it (I use [Ctrl]&[C]). Locate a clear part of the workbook (or create a new page), select the top-left cell where you want to paste and paste special (I normally right-click on the cell and select ‘Paste Special’). In the ‘Paste Special’ window, place a tick by ‘Transpose’ and click on “OK”. Sort the new table as normal, then copy it, right click on the top-right cell of your original table (or a new page, if you’re creating a new copy), select ‘Paste Special’ again, transpose again and click on “OK” again. Job done!

Changing the options: Select the data you want to sort, including the headings along the top, but not the labels down the left (otherwise they might be shifted to a different column). Select 'Custom Sort' as before, then click on "Options" along the top and click by 'Sort left to right' (then click on "OK"). Set the row you want to sort by and order and click on "OK". You'll need to remember to click on "Options" and switch it back the next time you want to sort rows.

Tutor led courses

We run a tutor led courses in using Excel.  Book onto Excel: Database techniques if you want to learn more about sorting your data.  The next dates available for booking are 27 July and 7 September 2016.

Bye for now,

John

 

Improve your Word documents with headers

  

📥  Computing Services

Yesterday, on a Word course, I was asked a brilliant question - one that led to an increase in my knowledge of the application and will lead to an improvement in the look of my larger documents.

The question was about what can be included in headers and footers – in this case displaying the current chapter in the header (or the new one, if the chapter starts at the top of the page).

This is simple to achieve and has just 2 parts to it:

1. Make your chapter headings obvious by setting them all to the same style – I normally use Heading 1 or Heading 2. Also make sure that nothing else in the document is set to the same style.

2. In your header bar (I normally just double click in the header area to see it), click on “Quick Parts” or “Document Info” (both are in the ‘DESIGN’ tab, ‘Insert’ group) and select ‘Field’.

In the ‘Field’ window that opens up, scroll down towards the bottom of the list and select ‘StyleRef’ and, in the selection in the middle of the window, select the style that your chapter titles are set to.

You can repeat the process to see more than 1 heading style, so it’s possible to display heading 1 and heading 2 in the header bar (for example, if you wanted to display main headings and sub headings).

There are many more fields that can be placed in a header or footer, including Author, Date, FileName, LastSavedBy, NumPages, Page, PrintDate, SaveDate, Time and Title. I’ve used Page and NumPages quite a lot, and FileName a bit (with ‘Add path to filename’ ticked). Do any of you use any of the other fields? If so, please add a comment to this blog telling us what the field displays and/or how you use it.

I will write a crib sheet on headers and footers in Word, which will include the above – if you want to read it, you’ll be able to find it on the University’s Moodle site.

Bye for now,

John

 

IT Training tips: Putting charts in Word documents

  , ,

📥  IT Literacy

A few people have asked me about the best way to put a chart into a Word document.

There are several ways to do this - here are 3 different ways that I use:

1. Copy an existing chart from Excel so it can be tweaked in Word:

Click on the chart in Excel to select it and copy it (I normally use [Ctrl]&[C]). Place the insertion point where you want to place the chart in your document and paste (I normally use [Ctrl]&[V]).

With this option, you can select the chart in Word and change any aspect of it, from the colour of the bars, to the chart type to the data, just as you would with a chart in Excel or PowerPoint. If you resize or reshape the chart, text will remain the same size and everything else will stretch or shrink.

2. Copy an existing chart from Excel so it cannot be tweaked in Word:

Copy the chart as above, but instead of just pasting it into Word, place the insertion point where you want the chart and click on the lower part of the 'Paste' button and select 'Paste Special...'. Select the format you want (I normally go for 'Picture (PNG)') and click on "OK". The benefits with this option include the text increasing or decreasing when you resize the chart and there being no way that any recipient of the document can change the chart in any way or access the data behind it.

3. Create the chart within Word:

Place the insertion point where you want the chart to appear in the Word document and click on ('INSERT' ribbon) 'Chart', select the type of chart you want and click on 'OK'. Type in your headings or labels and data into the data table and then click on the cross to close it.

If you want to change it, just click in the chart and click on 'Edit Data' or any of the other buttons on the Chart Tools 'DESIGN' or 'FORMAT' ribbons.

One extra bit of information: I've written some crib sheets and put them into the 'Self-Service Training Resources for Office 2013' area of the University's Moodle site. Current subjects are:

  • Conditional formatting in Excel
  • COUNTIF, SUMIF and AVERAGEIF functions in Excel
  • Creating drop-down lists in Excel
  • Mail merging an Excel file into Outlook emails

If you have a look at them and find them useful, please leave a comment below. Also let me know if you'd like a similar sheet written for another task.

All the best

John Baker (IT Trainer)

 

Merging dates from Excel into Word

  , , ,

📥  Computing Services, IT Literacy

If you mail merge Excel files into Word documents, you may find this blog very useful.  Someone recently raised the issue of the format of a date merged from an Excel file into a Word document. The problem is that Word displays the date in the American month/day/year format, even when it shows as day/month/year in Excel.

I have found two solutions to this issue.

Solution using Excel

The first is to split the date into its component parts in Excel and merge each part into the Word document.

To do this I create 3 new columns, with the headings Day, Month and Year (not very imaginative, I know!) and use the =DAY, =MONTH and =YEAR functions (e.g. if the date was in cell A2, write the 3 functions into separate columns: =DAY(A2), =MONTH(A2) and =YEAR(A2)).

Then in Word, to place a date, I simply insert each field and type in the separators, e.g. <Day>/<Month>/<Year>.

Solution using Word

Select the date field in the Word document, right click on it and select ‘Edit Field’ to see the ‘Field’ window. Click on “Field Codes” at the bottom-left of the window to see the advanced field properties.

Click at the end of the text in the ‘Field codes’ box (it probably says MERGEFIELD Date) and type \@ followed by the format you want within speechmarks, e.g.:

\@ “dd/MM/yyyy” to see a date appear like 01/02/2016

\@ “dddd d-MMM-yy” to see it appear like Monday 1-Feb-16

Use CAPITAL M for the month (m is used to show minutes). From the examples above you've probably already noticed/worked out that:

  • d or M would give numbers 1-9, then 10 upwards
  • dd or MM would give numbers 01, 02, etc.
  • ddd or MMM would give Sun to Sat or Jan to Dec
  • dddd or MMMM would give Sunday to Saturday or January to December
  • yy would show a 2-digit year and yyyy would show a 4-digit year.