- CTRL + S —– SAVE
- CTRL + Z —– UNDO
- CTRL + C —– COPY
- CTRL + V —– PASTE
- CTRL + N —– NEW DOCUMENT
- CTRL + P —– PRINT
- CTRL + B —– BOLD

Almost all of the above shortcuts work not only in Word but in most application, Microsoft and others.

**Lesser Known Shortcuts and Tips**

Below are some of the lesser known shortcuts that Microsoft Word has. This shortcuts and tips will really boost your productivity and wow your co-workers.

- =Rand(PARAGRAPHS, SENTENCES) —— Generate placeholder text =Rand(5, 3)
- Increase/Decrease font size using the CTRL + SHIFT + > and CTRL + SHIFT + <
- Type 3 “-” hyphens and press ENTER to create a horizontal line
- Create a quick Microsoft Word table using the + and TAB key
- CTRL + A —– Select all text in the document

Download the exercise file here: ConstructionTimeline.xlsx

]]>**Microsoft Excel’s Get and Transform**

“*Excel 2016 includes a powerful new set of features called Get & Transform, which provides fast, easy data gathering and shaping capabilities. Get & Transform enables you to connect, combine, and refine data sources to meet your analysis needs. These features are also used in Power BI, and in the Power Query Add-In available for previous versions of Excel. *” –

**See Excel 2016 Get and Transform in ACTION!**

16 Minute Video

Download the exercise file to practice using Excel’s Get and Transform feature: CustomerOrders-GetAndTrans-01.xlsx

]]>Download the example file to try yourself: ExcelBalanceSheet-01

]]>Arrays, simply put, are a collection of data. For example you may have a column of data in an Excel document.

All those values, in range C2:C27, are a collection of data or an array of data. An array of data is great to have, but being able to retrieve data from that array is even more important. Now, imagine that collection of data is stored numerically by position.

If I accessed the array and asked for the value in the 16th position, I would get the value 110.

Now, how does this all apply to a formula in Excel? Good question!

I want to get the total order amount for multiple records.

I could create a formula for each record, or I could turn columns C and D into Arrays and have Excel go through the collections of data and multiple each pairing (Units and Price/Unit) based on the position in that array.

**=C2:C27*D2:D27**

With both arrays of data, C and D columns, Excel will go through the collections and multiple the corresponding values based on the numeric position.

C2 & D2 = 1

C3 & D3 = 2

and so on…

Now, the formula isn’t enough. Before typing out the above formula, Excel needs to now where the formula should go, start by selecting all of the E column, then type out the formula. Unlike a normal formula you finalize the formula by pressing ENTER. To convert the formula into an array, you press CTRL + SHIFT + ENTER.

Download and practice with the example file: ArrayFormulas-01

]]>I’ve been getting a lot of students in my Udemy courses sending me message expressing interest in taking one of the Microsoft Office Specialist exams (MOS). So, I’ve decided to run a contest to help and inspire those interested. Whether you are already planning on taking one of the exams or are currently sitting on the fence, keep reading.

**HOW TO PARTICPATE**

- Currently enrolled in my “Microsoft Excel – Excel from Beginner to Advanced” Udemy course
- Complete 10 lectures within the course (You choose which sections. Must be sections you haven’t completed yet.)
- Open the QA section and post a new discussion about what you learned from the sections you viewed
- Complete the above by
**APRIL 30, 2017** **THAT’S IT! SIMPLE RIGHT?**

**5 LUCKY WINNERS WILL RECIEVE**

- A
**single application license**on Microsoft Office Specialist Practice Exam for GMetrix, which may be used for one of the following versions of Office: 2010, 2013 or 2016; Word, Word Expert, Excel, Excel Expert, PowerPoint, Outlook, Access, SharePoint, or OneNote. (**VALUED AT $40 USD)****GMetrix**online practice tests for Microsoft Office Specialist (MOS) certification are designed to replicate the certification exam experience so test takers can better prepare themselves for test day. The practice tests use the same exam format as the certification exams and are mapped to the certification objectives so a test-taker is assured that he/she is learning and practicing the skills that will help him/her pass the certification exam. GMetrix has two test modes; Testing Mode and Training Mode.

The practice exam will help you further prepare for taking a MOS certification exam. On **May 1, 2017, I will choose 5 lucky winners** from those participating. Make sure that you complete the above requirements and post in the course QA section on what you have learned.

Good luck, and see you in the course!

]]>**QUESTION:**

**“Is it possible to make one validation column dependent upon choices from another validation column?”**

Here’s an example;

Imagine you have 2 columns in an Excel list, one column for the Make of car entries and another column for the Model of the car entry. If a user selects “Ford” as the make, when a model is selected they should only be able to pick from “Ford Models” (Mustang, Escort, Focus). If “Chevy” is chosen, then they should only be able to pick from “Chevy Models”.

**ANSWER:**

This is definitely possible and can be accomplish is a number of ways. My first thought was to write some VBA code that would identify which Make was selected and then populate the appropriate choices through the code. Then I thought this would be a place to create an Excel VBA UserForm to control the users experience with specific controls to interface with in order to do the data entry.

Both the above would work great, but takes some knowledge of VBA and maybe not entirely necessary. I finally decided on using Excel’s Data Validation feature, combined with Name Ranges and Excel’s INDIRECT Function.

**Here is how it goes…**

- Setup three columns, somewhere off to the side of the main list (maybe even on another worksheet).
- MAKES Column
- FORD Column
- CHEVY Column
- Toyota Column

- Use the NAME RANGE feature to name each of these 4 columns
- E2:E4 = “Makes”
- F2:F4 = “Ford”
- G2:G4 = “Chevy”
- H2:H4 = “Toyota”

- Do this for each of the four columns.
- Setup the Data Validation lists
- Select the “Make” column (A2:A…)
- Set the Data Validation to pull the lists of Makes

- Select the “Make” column (A2:A…)

- Select the “Model” Column (B2:B…)
- Set the Data Validation to the INDIRECT() Function
- =INDIRECT(A2)
- The INDIRECT() Function will take the value of whatever entry is chosen in A2 (Ford, Chevy, Toyota) and turn it into a range reference. In this case, the Name Ranges you created earlier.
- When you click OK you may get a warning, just click YES.

- Set the Data Validation to the INDIRECT() Function

There you have it. Click into the Make column, select a Make. Now click the Model column and you will get the appropriate Models to chose from.

Hope you’ve enjoyed this. For the working file, download here: ExcelDynamicValidation-01

]]>

QUESTION: “Is it possible to change the tweak the theme colors of my SharePoint site to match corporate colors?”

The default, out of the box, SharePoint site comes with a standard theme that specifies the colors used, among other default settings. Microsoft does give, a user with proper permissions, the ability to change the default theme to a variety of other out of the box themes. Each of these themes have a variety of color palettes that you can choose from in order to “customize” your site.

The ability to change a sites theme is found under the SITE SETTINGS –> CHANGE THE LOOK…

Here you will find a variety of themes to customize the look of your SharePoint site.

Each theme comes with a few settings that can be modified to customize your site, including a variety of color palettes.

Each of the color choices are predefined. If you would like to customize the colors beyond the preset palettes you’ll need to take a look at another option.

**ANSWER:**

Back to the original question, customizing the site colors based on corporate colors. The answer is YES!

Microsoft created a free tool called, **SharePoint Color Palette Tool**. You can visit the Microsoft download page here. This tool provides an intuitive interface to creating your own SharePoint color palettes called, “.spcolor” files. These files contain the color codes that dictate the colors found in the various elements that make up your SharePoint site.

The application provides a preview of a standard SharePoint site with options on the left to change the colors of the various elements of the SharePoint site. Once colors have been customized you can then export the changes to a “.spcolor” file that can then be uploaded into the theme settings of your SharePoint site to be used in your SharePoint theme.

There are other tools that can be used as, such as SharePoint Designer, which is also free, but takes a bit more web development knowledge to get what you are looking for.

]]>

**QUESTION:**

What are some good uses of Excel’s MOD() function?

**WHAT IS THE MOD() FUNCTION?**

Before I go into a use for Excel’s MOD() Function, let’s make sure we understand what the function does. The MOD() function is actual quite a simple function. The following comes from the Microsoft site here…

Returns the remainder after number is divided by divisor. The result has the same sign as divisor.

MOD(number, divisor)

The MOD function syntax has the following arguments:

**Number**Required. The number for which you want to find the remainder.**Divisor**Required. The number by which you want to divide number.

The MOD functions purpose if to take two numbers, divide the numbers, and return what is left over (the remainder). For example: if you took 5 and divided that by 2.

5/2

You would get 2, as 2 can go into 5 2 times, with a remainder of 1

The MOD() function would return the 1, the remainder.

if you divided 4 by 2, the MOD() function would return 0, as 2 goes into 4, 2 times with nothing left over.

**ANSWER:**

This is great and all but where would I use this. We can come up with many scenarios on using the MOD function, but I am going to show you one that I use it for. I use this in Excel as well as other applications such as Crystal Reports.

Imagine you have a list of records in Excel.

You would like to take that list of records and format every other rows background color. Something like this…

Have you ever done this manually? Select every other row and format it. This can become very tedious and time consuming, especially if you have 100s of rows of data. There are other tools, such as FORMAT AS TABLE, that can help automate this but sometimes those tools limit what you can now do with your data.

**USE MOD() AND ROW() FUNCTIONS TOGETHER**

=MOD(ROW(), 2) > 0

In the above formula I have taken the ROW() function and nested it within the MOD() Function. Remember, the MOD function takes two numbers and divides them, returning the remainder. Why the ROW() Function?

Ultimately I want to format every other row in my Excel list, the problem is how to automate this and identify which rows to format. The ROW() Function returns the row number of the row the function is used in. For example if I created the formula, =ROW(), and placed that in cell B10, the formula would return 10, because it was in row 10.

=MOD(ROW(), 2)

In the above formula the MOD Function takes the current row, ROW(), and divides that by 2. If there is a remainder then we know that the current row is an ODD number row.

If MOD() returns 0 then EVEN

If MOD() returns > 0 then ODD

In the following example I have taken the above formula and placed in a conditional format rules for the range of cells that make up my list.

That’s it. The next time you want to alternate row colors for 100s or 1000s of records, try this out.