Wednesday, August 20th, 2014

A Great Microsoft Office Tutorial Resource

Great find! A list of good quality Microsoft Office tutorial resources

I’ve recently discovered an enormous list of Microsoft Office tutorials that may be worth checking out. Each piece of Office software has several listed tutorials ranging from beginner to advanced difficulty and general to specific usage.

Best of all, each tutorial has a brief summary of its contents so you can quickly decide if its new and interesting information. Hopefully every Office user will find something of use. You can visit the page here.

Friday, July 19th, 2013

Excel Tip – Spin Buttons for Data Entry

Learn how to use a spin button (also called a spinner) form of control in Excel to aid in data entry. Using a spin button, a user can click the button to increase or decrease a value in a worksheet cell.

This video explains how to access the Developer tab, then, how to add the spin button form control and, finally, how to set up the control. It also explains how to scale the value that the control provides so it can provide percentages and very large or very small values. This feature is in Excel 97, 2000, 2002, Excel 2007, Excel 2010 and Excel 2013.

Transcript:
Hello, I’m Helen Bradley. Welcome to this video tutorial. In this tutorial we’re going to have a look at creating spin buttons in Excel to make data entry just about as much fun as data entry can be. Before we get started with spinner controls let’s have a look and see what we’re talking about.

Here is a copy of the worksheet that we’re going to be working on and this is the spin button control. It’s been set to work between 20 and 30 so it will take a value between 20 and 30 years and every time I click the down arrow it decrements this cell by one so that the overall payment per month on the mortgage is increased because the term is much shorter. When we increase the term on the mortgage by clicking on the up arrow the overall payment is reduced. And it stops at 30.

So let’s see how we would add the spinner control to this particular worksheet.
Well first of all you have to have the Developer tab available. It it’s not choose File and then Options and in Excel 2013 and 2010 you’ll go to the Customize Ribbon button here and you’ll check this Developer tab here so that turns it on. In Excel 2007 you’ll go here because there will be an option here for Show the Developer Toolbar in the Ribbon.

So once you’ve got the Developer toolbar available click it and then take this Insert option and you want the form controls. Now the Active X controls look pretty much the same thing but they work very, very differently. And what we want are the simple form controls because they’re the easiest to use. I’m going to click here on the spin button control and then I’m going to click and drag to create the control on my worksheet. Now I can resize it later on but I’m going to start by drawing this size.

I’m now going to right click it and choose Format Control. And this gives me my options for formatting the control. To start off with I can set its current value. So I can start it at for example 25 and then I can set its minimum value which I had previously set to 20 so all our mortgages are going to tested between 20 and 30 years in length. And then I’m going to make the maximum value the 30. So we’re going to move between 20 and 30. And the incremental changes how big a change do I want to happen with each click of the button. Now these have to be integers so one is the smallest value. And cell link is a pointer to the cell that I want the value to be inserted in so I’m going to click in here and then click in this cell because this is going to be my link cell and click Ok.

You can see that the cell value changed to 25. That’s because that was the starting value. Now if I click on the spin button right now nothing is going to happen because it’s still active. So I’m just going to click outside it and now I can test it. You can see I can click up but when I get to 30 I can’t click it any more and then it will click down. And when I get to 20 it won’t go any lower.

So that’s a spin button control that you can use to control this sort of value. But there are some limits to spin buttons that we’re going to have to get around. One of them is that they only return integer values and the maximum value is 30,000. So right now that would cause us some problems in trying to add the mortgage amount which is in the region of 200,000 or more when the maximum value is 30,000. And here the interest rate is 5 percent. That’s .05. That’s not even an integer. It’s not even a one. So we’re going to have problems controlling that. And we’re going to have to find a way around it which of course can be done easily.

To see how we would deal with the situation where the amount borrowed is a much larger figure than we can use in the spin button control let’s have a look at this worksheet. In this worksheet we’re looking at how we could possibly scale a range of 0 to 30,000 to actually work for us. Let’s say that the minimum amount that we want this mortgage calculator to work for is 100,000 and the maximum is one million. Well let’s look and see what 100,000 is. Well 100,000 is 10 times 10,000 and a million is 100 times 10,000. So we could have our spinner work between 10 and 100 if we could scale it up by 10,000 each time. I’m going to replace the formula with a value and it’s going to multiply the contents of cell D2 by 10,000. Now there’s nothing in cell D2 right now but we know that we can put a value in there using a spin button.

So again Developer, I’m going to Insert and I’m going to select the Spin Button Form Control. I’m going to drag to create it on the worksheet, right click it and choose Format Control. This time I want my minimum value to be 10 because I’m going to use a 10 to 100 scale here. The maximum value is going to be 100 and I’ll have it increment by one digit at a time which is going to be 10,000. I’m going to set my current value to, for argument sake, 20 so that we’ll get back our 200,000 in this cell. And the cell link cell is going to be the cell that has the value in it that this formula is using. When I click Ok let’s see how it’s working.

As you can see the spin button here is giving us this value here of 20 and in this cell we’re just taking the value of 20 and scaling it up. So now if I click on this button it’s going up by 10,000 every click of the button and it’s going to max out at one million. And if I were to come down then it would max out in the bottom direction at 100,000. And each time as I click on the button, well I’m sitting on the button right now, but as I click on the button the amount that we’re paying each month is being recalculated accordingly. So scaling up like this is a way of getting larger values than the 30,000 that we’re allowed in the spin button control. Of course that’s preempting the solution for this value here. All we need to do now is to scale this downwards. So let’s see how we do that.

To reduce a value using a spinner we’ll work the opposite direction. Let’s say that we want an interest value between 2 and 10 percent. That’s between .02 and .1.
.02 can be represented by 2 times .01 and 10 by 10 by .01. Now that’s only going to give us whole interest values so I don’t think it’s going to be quite big enough. Let’s go down a scale and let’s say it’s 20 multiplied by .001 and 100 multiplied by .001. That’s going to give us additional percentages in the range so instead of going 2 percent, 3 percent, 4 percent we can go 2.1, 2.2, 2.3 and so on. So let’s take this scaling back to our worksheet. Again we’re going to add a spin button here so from the Developer toolbar Insert make sure that you’re using this form control and drag a spinner into position. We’ll right click it and choose Format Control and we’re going to use cell D3 as our intermediate cell. The minimum value is going to be 20. The maximum value from our previous calculations on the other worksheet is 100. We’ll use an incremental change of one for now and the cell link will be this cell here D3 and press Ok.

Now nothing is actually happening because we haven’t put our formula in here yet. The formula multiplies this value here by .001. And here is our smallest value 2 percent. We’ll need to reformat this cell so I’m going to the Home tab of the Ribbon and I’m going to increase the decimal places here so that we can see exactly what values we’re getting and not rounded values. Here everything is now going up in .1 of a percent. And it will max out at 10 percent because that’s where we set it to max out at. It won’t be able to go any larger than that.

Now all we need to do to make this worksheet a little bit neater is just to hide the column that contains those intermediary values. And now somebody can use this calculator to calculate a loan and instead of having to put value in here manually they can just click on the spin button controls to do it automatically.

I’m Helen Bradley. Thank you for joining me for this video tutorial. Look out for more of my video tutorials on this YouTube channel and visit my website at projectwoman.com for more tips, tricks and tutorials on a range of Office programs including Excel, Word, Outlook, PowerPoint and Publisher.

Helen Bradley

Tuesday, January 29th, 2013

NO DVD play back or DVD Burner in Windows 8


Image credit Pawel 231 sxc.hu
Are you searching Windows 8 for its  DVD player and DVD burner? Well stop wasting time – they aren’t there

Yep, that’s right. Windows 8 ships without a built in DVD burner and there is no installed DVD player software either. If you don’t have a DVD drive and I guess Microsoft thinks they are so “yesterday’s technology” you’re OK. But I still have a DVD drive and so do millions of others. The worst part of this is that Microsoft doesn’t warn you there’s no DVD burner or player – you have to work that out yourself.

So, all across the world, every day, thousands of man and woman hours are being wasted looking for something which, let’s face it, should be there, but isn’t. Gee, thanks a bunch for that Microsoft!

So, here’s what to do if you have a brand new Windows 8 computer or if you’ve upgraded to Windows 8 and you have a DVD drive.

1 Stop looking. There is no DVD burner and no built in DVD player. If you have either of these on your Win 8 machine it didn’t come with Windows 8 – your PC manufacturer (or tech savvy daughter or son), put it there.

2 Get the software. The best solution if you’re using Windows 8 Pro, although I hate to suggest it is to shell out $9.99 to Microsoft for the Media Center software. This isn’t included in Windows 8 but can be downloaded and installed. You use this to play DVDs on your PC. To get it, launch Windows 8, tap Windows + Q (Search) and in the box type Add Features and tap Settings. Tap Add features to Windows 8 and then click I want to buy a product key online and you can go ahead and buy Media Center.

Now, there is one caveat. If this option doesn’t appear (and it may not because Microsoft left it out of some versions of Windows 8 such as the UK version and other users have reported it as disappearing once it is used the first time – yeah! well done Microsoft!) you can try this. Click Windows + Q, type System and tap Settings and click System.  Now click View Details in Windows Activation. Once there you may find a link to add features or click Buy Windows for another computer to go to the website to buy Media Center – search for it when you get there. Until Jan 31, 2013 if you use Windows Pro you can get a licence code that lets you download it free of charge, otherwise it costs $9.99 if you’re using Windows Pro – it will cost a huge chunk of change if you’re using the Home version though.

3 If this all sounds like too much trouble, download the free opensource VLC Media player which is compatible with Windows 8 from http://sourceforge.net/projects/vlc/ all the open source folk love it.

4 To burn DVDs you need a DVD burner and the Windows DVD program no longer exists in Windows 8, so, you’ll need some burning software. There are plenty of programs around from folks like Nero but they can be pretty costly and not all are compatible with Windows 8 . If you just need a very simple burner, try Ashampoo Burning Studio Free 2013 – the Ashampoo products are great and the company does a good line in simple to use CD and DVD burners that don’t need a degree in computing to operate. Get the free version from Softpedia here or from CNET here.

Bonus Tip – where to find a list of features missing from Windows 8

If you’re searching for features that you think should be in Windows 8 but you can’t find, check this Wikipedia article: Features removed from Windows 8 for a handy list of what is missing from Windows 8 – it might save you some wasted time.

 

 

 

Helen Bradley

Thursday, January 10th, 2013

Quickly Create New Folders in Windows

Photo Credit: Kriss Szkurlatowski

Creating multiple new folders in windows can be a pain, since it forces you to click through multiple context windows every time. It can, however, be bypassed with a nifty set of keyboard commands that navigate those windows for you.

While in the Windows Explorer view, simply hold Alt and press F+W+F. This opens the File menu, selects New, and chooses Folder almost instantly. This is extremely useful because it allows you to create and rename multiple new folders without taking your hands off of the keyboard.

You can use these commands to open other menus as well. While holding Alt you will notice certain letters of menu selections become underlined. Pressing these letters on your keyboard will select that menu option. For example, Holding Alt and pressing F+W+S will create a new shortcut. This functionality extends to Microsoft Office as well. The next time you’re in Word, press Alt and see what you can start controlling with a few key strokes.

Helen Bradley

Tuesday, November 27th, 2012

Trevor’s Quick Word Tip – Undo – Redo

Made a mistake? Undo it with Ctrl + Z and, if you change your mind yet again, redo the undone command by pressing Ctrl + Y. In most programs you can press Ctrl + Z repeatedly to roll back your changes.

Helen Bradley

Tuesday, November 20th, 2012

Trevor’s Quick Excel Tip – Display Formulas

When you need view formulas instead of their results press Ctrl + ~ to display formulas in all cells in the worksheet. To return to viewing the worksheet as it was, press Ctrl + ~ again.

Helen Bradley

Tuesday, November 6th, 2012

Trevor’s Quick Excel Tip – Select Everything

To select an entire row or column in an Excel worksheet, click the column letter or the row number. To select multiple columns or multiple rows click and drag over the column letters or row numbers to select. To select the entire worksheet, click the empty box at the intersection of the Rows and Columns in the top left of the worksheet.

Helen Bradley

Wednesday, October 31st, 2012

Word 2013 – What’s new

Learn what is new in Word 2013 and how you can put it to work in your day. Features include new Design tab, start screen, Layout Options, PDF editing, Table border painting, styles and sampler, Read Mode options, Present Online, Save to skydrive, insert images and video from the web.

Transcript:

Hello, I’m Helen Bradley. Welcome to this video tutorial. Today we’re going to look at what’s new in the new Word 2013. This is the new start screen that you’ll see when you start Word 2013. And it’s similar to that in Excel and PowerPoint and Publisher. You’ll see the recent documents that you’ve had open down the side of the screen here, and you’ll be given the option to open other documents for example from SkyDrive or your local computer. There are templates here and these are current so at the moment it’s sort of October so we’re seeing Thanksgiving and Halloween templates. They will change with the seasons. If there is a template that you like you can click on it and you can pin it to your start screen. You can also click to have a look at it in more detail. If there are multiple images you can look through it here. If you want to use it click Create, otherwise just close it down, and you can go and have a look at another template. You can search online for templates and there are some suggested searches here. If you have your own templates you can click Personal and view the templates that you’ve created for yourself. Up here in the top corner are details of the account that you’re currently logged into. So this tells me I’m logged in to this particular SkyDrive account. Let’s open a document and we’ll start working inside the new 2013.

Here I have a document open inside the new Word 2013. You’ll see that the screen is very clean. It’s got that sort of metro style interface that is typical of the new Office 2013 suite. Everything is very flat and your documents sort of take center stage. So let’s have a look at a couple of the new features. The first one is the new read mode. I’m going to View and then Read mode because a lot of people actually use Word as a reader. So I’m just going to set this back to the layout that you will see by default. This is this sort of two column layout which allows you to work through the document and read it and particularly appropriate for using on a tablet. You can change this to the regular page layout if you want to by choosing Paper Layout. There are paper colors that you can choose. We’re obviously using sepia at the moment. And you can set it to the default which is two columns, narrow or wide as you choose.

There are some other features here. If you right click a word you can click Define and it will be defined on the screen for you. I don’t have a word that’s actually going to be definable to show you that, but it works just fine. The other thing in this read mode is that you can look at things more closely. For example we might look at this chart and double click it. And it will now open over the top of the document so that we can look at it in more detail. And that happens as well with smart art. Now this read mode is not looking at the document the way it would be presented so things may overlap if they’re complex. The important thing about this read mode is that it’s for reading the document and not sort of previewing it the way it’s actually going to appear when it’s printed.

I’m just going to escape out of here to go back to the regular Word. Now one of the other changes to Word is in the review area so I’m going to open a different document. I have one here that has track changes in it. The track changes feature has been overhauled a little bit. Let’s go into review and I’m just going to show the new mode which is simple markup. In simple markup we’re going to see the document as it would be if all the changes were incorporated. So that’s text that is inserted is inserted and text that has been marked for deletion is deleted. And we can switch between these two modes either by choosing simple markup or all markup here on the tab, or we can click these lines. These are lines indicating that there are changes here, but we’re seeing it in simple mode at the moment. So we’re not seeing the changes. We’re just being warned that there are changes. Click again and now we can see what the changes are.

There are some changes too to comment. So for example if somebody adds a comment to a document, if you want to reply to that comment you can click this Reply button that allows you to now reply to that comment. The changes here are quite significant because in the past if you wanted to reply to a comment all you could do was add another comment. So conversations could become quite long and complex as they sort of scrolled down the page. Here now all the comments or all the conversation about a particular point can be isolated inside a single comment. And I’m just going to delete that comment. Let’s just get rid of it in its entirety and let’s go back to viewing this document in the new simple markup.

Now I’m going to get rid of all the changes in this document. I’m going to accept everything because I want to show you some of the new design features in the new Design tab. The new Design tab has put together some of the features that were really difficult to find in earlier versions of Word, in particular the sort of document formatting styles. Now you can see what the document formatting styles are. And if you like one you can click on it to apply it to your document. And there are lots of different styles here to choose from. This is the one that we were using previously, but they’re easy to find and to use. If you see one that you like, for example we might like this sort of gray look, we could change the color scheme that’s applied to it. So we could make it blue for example. There’s also the ability to choose different font combinations for our document if we like the look of it but prefer a different combination of fonts for example.

Now if we create a look that we like and we want to use it every time we open Word then we can set this as default. This is a new option on the Design tab. If we click this, this is now going to be the default look for all our documents in future. So this new Design tab has lots of features on it that make things more accessible than they were. Of course if we want to format individual elements on this page we’d back to the Home tab and selecting styles. But these styles are linked to the design or the document format that we’re using here from the Design tab.

Still in this document I’m just going to change its rotation back to normal and we’re going to have a look at the new layout options that we have for this. One of them is this layout options icon here which makes it more accessible to find the text wrapping options. In the past these were really quite difficult for people to find. And now they’ve been attached to the image itself. You can also opt to move the image with the text or fix its position on the page. If you click See More you’ll get the more traditional layout dialog. There are also alignment guides so watch as I move this image around the page. It becomes aligned or we can see when it is aligned to various objects on the page because we get this green alignment guide appearing. So we can see when it’s aligned with paragraphs, with margins of the page, perhaps the edge of the page itself. So this allows you to align things up more easily than you’ve been able to do in the past. Now still in this document we’re going to have a look at the new navigation pane. I’m choosing View and then Navigation Pane. This document has been set up using styles. So because it’s been set up using styles I can navigate the document by selecting the headings in this navigation pane. It just makes it easier to move around and particularly a long document. In addition, there are these new collapse icons. Again, because this document has been formatted using heading styles these collapse and expand icons appear and I can click on them to collapse the document down to just the heading. So imagine this in a really long document. If you had really large headings that had lots of text underneath then you could collapse your document so that you only work on the headings that you’re interested in working on at the time and then you can expand the other headings as you go through it. Now this is a feature that used to be available in outline mode but it’s been brought into print layout. So it’s accessible to any document that you create using heading styles, not only the ability to collapse and expand paragraphs but also this slightly smarter and more useful navigation pane.

Now as well as collapsing parts of the document you can now collapse the ribbon. So you can click here to collapse the Ribbon and it collapses to just the headings. And then you can click to open it. You can pin it or unpin it depending on what you want it to appear like. I’m going to pin it because I like to see the ribbon particularly on large screens.

Before we start looking at tables let’s just go to headers and footers. I’m going to choose Insert and then I’m going to just insert a plain old header into this document. One of the changes here to the header option is that there is access to document info. You can get access to the author, file name, pathname and document title from the Header tab and that’s new in this version of Word. So let’s just close out of there and let’s switch to a document that has a table in it. Here we have a document that has a table in it, and with the table selected we now have options for formatting that table. From the Design tab you’ll see that there are additional table styles here. And these are theme aware so they’re going to look like the theme itself. So I’ve just applied one of them here. Now I can go ahead and format this table a little bit better if I want to. I’m thinking that the text would be better white. So let’s just go and get some white text, and let’s make it a bit bolder.

Now there is a new feature with the design of this table in the border. So we’re going to the table, let’s just grab the table. I’m going to the Design tab and here are our new border styles. This allows us to bypass the old border option that was available inside Word and to paint borders on. Now I can click here for border styles, and I’ll get borders that are theme aware. So these are borders that look like the theme. So I’ll just select one, but I might make a slight change to it. I think I’ll add a slightly wider border. So I’m choosing the color I want from the styles, but I’m making it a bit wider. Now I’m going to click on the border painter and I’m going to start Painting this border onto my table. And wherever I click and paint it’s going to be painted on. So this allows me to paint borders on where I want them to appear and to remove them where I don’t want them. For example, I could just set a border to none and they would be removed. Now let’s say that I make a mistake down here and I paint this border on by mistake. I want to go back to the regular border. Then all I need to do is from the border styles option here is choose my border sampler. I can sample a border. It’s now attached to the border painter. So having sampled it, I can now paint it back onto the table. So this gives me a lot more flexibility in working with tables than I’ve ever had particularly with borders and tables because that’s really been a nightmare issue for a lot of users.

Now the other thing that I can do in tables that I haven’t been able to do easily in the past is to add a row or a column. All I need do is to position my cursor just where I want the row or column to be inserted and I get this new indicator. And I can click to insert a row. I’m just going to undo that. But let’s go and see that the same thing appears between columns so we could add an extra column to the document. Let’s just undo that. We could also add one at this edge if we want to. So there are some new features inside Word not only new table styles but the ability to paint on borders, to sample borders and to access border styles.

The new features in Word 2013 extend to images and video. If you click the Insert tab you get access to online pictures. So you can view images that are in the office.com clipart collection. You can search Bing or you can go and search your own SkyDrive account. So let’s just type cupcake here. You can see that our cupcake image came from there, but you can add additional images by just clicking on them to insert them into your document. The same thing happens with video content. You can click Online Video and access online video. The benefit of these insert video and online picture options is that you can insert content direct from the web without needing to download it and save it to your computer first.

Let’s have a look at some of the screens that are available here. The open screen gives you access to recent documents, and if you click Computer you’ll see access to your recently open folders. And you can pin folders here as well as pin documents here. You can also get access to your SkyDrive account. One of the new features is in this share Option because you can now present online. If you click this you can go into a present online feature that is presented through the Office presentation service which is a new service. And this is similar to presenting a PowerPoint presentation online. Your presentation or your Word document is uploaded and you’re given a link that you can then share with others and they can watch as you present the document to them. You also have the ability to publish blog posts and invite people also to share your document. This would be done via a SkyDrive location. Here are the account options. Here you can see what account you’re currently logged into. You can also add services. For example we could add a LinkedIn or Twitter service here. We’re already connected to Facebook and SkyDrive, and you can see your update options.

The other feature that I want to talk about is the ability to open and edit PDFs. So I have the open screen available here, and there is a PDF that I just downloaded. So I’m just going to click it to open it. Now you’ve never been able to do this before inside Word and that is to open a PDF for editing. Now the application is not perfect. In fact it’s far from perfect, particularly with complex documents like this one that actually has images and pieces in it. But if we go to the select pane here we’re able to actually locate the picture and the elements that are part of this document so I can actually find these shapes. And I’m just deleting them. So I’m getting rid of these shapes that made the image and now I have something more attune to the original PDF and I could make changes to it. I would probably need to reformat this table as tables aren’t particularly friendly. But you do get access to the internal contents of a PDF file which you couldn’t in the past.

So there’s a roundup of the features that are new in Word 2013. I’m Helen Bradley. Thank you for joining me for this video. Please subscribe to my YouTube channel so that you’re advised of new videos. You can also find more blog posts and information on my website at projectwoman.com.

Helen Bradley

Tuesday, October 23rd, 2012

Trevor’s Quick Word Tip – Setting your Preferences

It’s generally a great idea to go through Word and set it up for your own personal working style.  Head to the preference area by clicking File > Options (Office button > Word Options in Word 2007) and click Advanced. Here you can set preferences for Editing, Printing, Display and so on to make Word work the way you want it to.

Helen Bradley

Tuesday, October 16th, 2012

Trevor’s Quick Word Tip – Remove unwanted formatting

If you are having second thoughts about the look of your document you can remove the formatting. Select the text that you want to strip of its formatting and and press Ctrl + Space Bar to remove its formatting. If you press Ctrl + Shift + N you’ll apply the default Normal style to the selected text.

Helen Bradley

Page 1 of 212