Microsoft Office Tips, Tricks, and How-to's to help you use MS Office like a pro!

Carol's Corner Office

Category Archives: Subscribers Office 365

Subscribers: How to Create A Drop-Down List in Excel 365

Posted on by Posted in Subscribers Office 365 Leave a comment

It just makes sense to work as efficiently in your Excel worksheet as you can. One way to do that is using drop-down lists.  Using a drop-down list will allow users to select an item from a list that you have created.

Follow the steps below to learn how:

  1. Open a new worksheet and key in the entries that you would like to appear in your drop-down list.  You should have these items in an Excel  table. If you do not, then you can convert your list to a table. Simply select any item in your list and pressing CTRL + T. The reason I say you should have your data in a table is that because then, as you add or remove data, any drop-downs you had based on that table will be automatically updated. So go head now and sort the data in your drop-down list.
  2. Select the cell in your worksheet where you would like to have your drop-down list.
  3. Click the Data tab on your Ribbon and Data Validation.
  4. Click Settings | Allow box | List.
  5. Click Source  and select your list range.
  6. If you intend to allow your users to leave a cell empty, check Ignore blank.

Next up, I will tell you how to apply data validation to your cells. Be on the lookout!

 

Subscribers: How to Add Protection to Word Forms

Posted on by Posted in Subscribers Word 365 Leave a comment

So now that you have your form just about finished it is time to think about protection for it right? Protection means that you can limit how much or how little users can edit for format your form. We do this by using the Restrict Editing command.

Follow the steps below to learn how:

  1. Open the form that you would like to lock or protect.
  2. Select Developer | Restrict Editing.

restrict-editing

  1. Once you have selected your restrictions, select Yes, Start Enforcing Protection.

enforcing Editing

Should you only want to protect certain parts of your document, simply separate the document into sections and just protect the sections you like.

Subscribers: How to Use Data Picker In Word Controls

Posted on by Posted in Subscribers Word 365 Leave a comment

Ok, so now we have covered a couple of topics regarding forms and controls in Word 365 so today we will learn how to use a Data Picker in Word Controls!

The Date Picker control works the same as any date picker you may have used previously, which basically means that when you click on it, a calendar will appear and you can click on a specific date to select it.

Follow the steps below to learn how:

  1. Click where you would like to insert your date picker control.
  2. Select Developer | Date Picker Content Control.

Here is an extra bonus:

You can also add a check box control to your form. Follow the steps below to learn how:

  1. Click where you would like to insert your check box control.
  2. Select Developer | Check Box Content Control.

I know, crazy easy right?

 

Subscribers: How To Use Combo Boxes in Word Controls

Posted on by Posted in Subscribers Word 365 Leave a comment

Ok, I think we left off in a previous post (Forms and More in Word) with Building Block controls. So now we are going to build on that and learn how to use Combo Boxes in our Controls. Using Combo Boxes allows your user to choose from a list of selections provided by you or they can simply key in their own data/information.  Conversely, in a drop-down list, your user can only choose from the list of selections.

Follow the steps below to learn how:

  1. Click on Developer | Combo Box Content Control or Drop-Down List Content Control.
  2. Select the content control you wish and then select Properties.
  3. If you wish to create a list of selections, under Drop-Down List Properties, choose Add.
  4. In Display Name, key in a choice (i.e., yes, no, maybe),
  5. Continue to key in any other properties that you would like.

Also, you should know that if you choose Contents cannot be edited, your users will not be able to click a selection.

Next up, How To Use the Date Picker  Control!

Subscribers: Forms and More in Word 365

Posted on by Posted in Subscribers Word 365 Leave a comment

You can take your documents from good to spectacular very easily in Word 365.

Let’s start with forms. Suppose you want to create a form that your users/readers can inset information into. You would start with a template or document and then add something called content controls.  (i.e., check boxes, text boxes, date pickers, drop-down lists, etc.) If you use databases, the controls above can also be linked to your data.

Follow the steps below to learn how:

Show your Developer tab. If it is not displayed on your Ribbon, you can add it.

  1. On your File tab, go to Options | Customize Ribbon.
  2. Under Customize the Ribbon and Main Tabs, select the Developer check box.
  3. Click OK.

After you show the tab, the Developer tab will stay visible, unless you clear the check box.

Start with a form template or start from scratch if you prefer, with a blank template.

Now you want to start adding your content to your form:

Go to Developer and select the controls you would like to add to your document or form. Should you want to remove a content control you have added, simply select the control and press Delete. You can set the properties on your controls once they are inserted. I should add that you can print a form that you have created using content controls but the boxes around those content controls will not print.

In a rich text content control, your users may format text as bold or italic and  key in multiple paragraphs. Should you want to limit what your users add, insert the plain content control instead.

  1. Click where you would like to insert your control.
  2. Select Developer | Rich Text Content Control or Plain Text Content Control.

You can use building block controls when you would like users to select a specific block or text. Building block controls can be very helpful when you want to add different boilerplate content, depending on different requirements. You can create rich text content controls for each version of your boilerplate content and use a building block control as the container for your rich text content controls.

  1. Click where you would like to insert your control.
  2. Go to DeveloperBuilding Block Gallery Content Control or Building Block Content Control.
  3. Select Developer and content controls for your building block.

developer

  1. Click where you would like to insert the control.

Next up, Assigning specific properties to your control. Keep an eye out for it!

Subscribers: How To Manually Modify An Existing Style in Word 365

Posted on by Posted in Subscribers Word 365

In an earlier post I told you how to automatically modify a style. Today I will show you how to modify an existing style.  With this method, you do not even need to have text in a document to modify a style!  Why? Because I think it is good to know how to do it both ways.  They may be times (i.e., at work where you are not allowed to change styles automatically, etc.)  I just think it is good practice to know how to do something more than one way. So let’s get to it shall we?

Follow the steps below to learn how:

  1. On your Home tab, right-click any style in the Styles gallery and click Modify.

 

modify

  1. In the Formatting section, make the formatting changes of your choosing, (i.e.,  font style, size, or color, alignment, line spacing, or indentation).

modify-style-dialog

Select whether your style change will apply to the current document or to all future documents (see above).

As you can see, this method also is very easy to do.

 

Subscribers: How To Create a Notebook In OneNote 365

Posted on by Posted in Subscribers Office 365

This is really a learning experience for me as well as you my dear readers.  I have never really used this application before but now I want to immerse myself in it.  I have the app, so why not use it and see what it’s all about right?  You can embed Excel Worksheets in OneNote, create and share lists, take notes, etc.  I am told that it can same me lots of time and you all know that is something that is near and dear to my hear.

Follow the steps below to learn how:

  1. Open the OneNote app.
  2. Click Show Notebooks.
  3. Click +Notebook, which is at the bottom of the list that opens.
  4. Give your notebook a name in  Notebook Name.
  5. Click Create Notebook.

More to come!

Subscribers: How To Modify A Style in Word 365

Posted on by Posted in Subscribers Word 365

I have said it before and I will say it again, Styles in Word are a wonderful thing!  It’s a shame that most folks have no idea what they do or how to use them, modify them, etc. Nor do they know how they can make their Word life so much easier.

You can actually modify a Style by updating it to match the formatting in your document. And then it will always be there for you – all you have to do is select that style going forward.

Follow the steps below to learn how:

  1. Select text in your document that has a style applied, (i.e., Heading 1.)
  2. When you select text that has a style applied, that style is highlighted in the Styles gallery.

 

highlighted-style

  1. Format your selected text with the new attributes that you would like.
  2. You may, for example, wish to change the point size for the Heading 1 style from 15 points to 13 points.
  3. On your Home tab, in the Styles group, right-click the style that you would like to change.
  4. Now, click Update [Style Name] to Match Selection.

As a result, all text with the style that you changed will automatically reflect the changes you made.

I think that is pretty darned easy and you only had to do it once and it’s just one click away the next time you need it.

 

Subscribers: How To Clean Your Data Before

Posted on by Posted in Subscribers Office 365

In an earlier post, I told you how to move data from Excel to Access and vice versa. In that post I told you that you should, if you want a smooth process with glitches, clean your data. Now, I am going to tell you what that means and how to go about doing so.

Of course we do not  always have control over the format and type of data that we import from an external data source, (i.e.,  a database, text file, or  Web page. So, prior to analyzing the data, you need to clean it up. Luckily, Excel has myriad features to help you clean you data so that it is precisely the format that you would like. When you get luck, the task is straightforward and there is a specific feature that does the job for you. Like you can use Spell Checker to clean up any misspelled words in columns that contain comments or descriptions. Or, if you need to remove duplicate rows, you can quickly do this using the Remove Duplicates dialog.

And then there are times, when you may have a need to manipulate one or more columns by using a formula to convert the imported values into new values. (i.e.,  if you need to remove trailing spaces, you can create a new column to clean the data by using a formula, filling down the new column, converting that new column’s formulas to values, and then removing the original column. But let’s get on with it shall we?

Follow the steps below to learn how:

  1. Import your data from an external data source.
  2. Create a backup copy of your original data in a separate workbook.
  3. Be sure your data is in a tabular format of rows and columns with similar data in each column, all columns and rows visible, and no blank rows within the range. For best results, use an Excel table.
  4. Do tasks that don’t require column manipulation first, such as spell-checking or using the Find and Replace dialog box.
  5. Next, do tasks that do require column manipulation. The general steps for manipulating a column are:
  6. Insert a new column (B) next to the original column (A) that needs cleaning.
  7. Add a formula that will transform the data at the top of the new column (B).
  8. Fill down the formula in the new column (B). In an Excel table, a calculated column is automatically created with values filled down.
  9. Select the new column (B), copy it, and then paste as values into the new column (B).
  10. Remove the original column (A), which converts the new column from B to A.

I hope this has been helpful to you.

Subscribers: How To Access Data In Access 365

Posted on by Posted in Subscribers Office 365

Say you just subscribed to Office 365 and you have Access, which you have never had before and you are curious about how to use it?  You have lots and lots of data stored in Excel, but how to get it from Excel to Access? Well that is what this is all about. As in all things, there is a segment of people who think Access is useless and there are others who live and breathe it. Me, I am somewhere in the middle.  I love to learn new things and see if I can use them to my advantage so you will be seeing more Access posts in the future. Now, let’s get on to how to access your data in Access 365.

Follow the steps below to learn how:

  1. The easiest method is to simply copy and paste the Excel data into Access.
  2. Copy the data in your Excel document.
  3. Open your Access database and press CTRL + V to paste your data.
  4. If you copy from Excel Access will use your sheet name for your new table name.
  5. If you are copying your data from a Word table however, Access will prompt you for a table name. It will also offer you more options.

202111117

There is, of course, a caveat to all this. To make this super easy and without glitches, you should beforehand take care to prepare your data and make sure it is “clean.”

  • Convert cells that contain non-atomic data (that is, multiple values in one cell) to multiple columns. For example, a cell in a Skills column that contains multiple skill values, such as “C# programming,” “VBA programming,” and “Web design” should be broken out to separate columns that each contain only one skill value.
  • Use the TRIM command to remove leading, trailing, and multiple embedded spaces.
  • Remove non-printing characters.
  • Find and fix spelling and punctuation errors.
  • Remove duplicate rows or duplicate fields.
  • Ensure that columns of data do not contain mixed formats, especially numbers formatted as text or dates formatted as numbers.

Yes, I know you want more information in this regard and you shall have it – in another post.  Stay tuned!