Microsoft_Excel_2013_logo.svg

How to Password Protect Excel Workbooks and Secure Your Data

Password Protect Excel Workbooks In 3 Clicks

Many times you want to restrict who can access your spreadsheets. Luckily, Microsoft makes it easy to password protect Excel workbooks. Using the method above, you can password protect Excel workbooks and secure your data against unauthorized access. When you encrypt the workbook, only those with the password will be able to open or view the file, which allows you to control access to your data and also prevent someone from making unauthorized changes. However, it is also important to remember your password, because if you lose it, it is very difficult to recover. Watch the above video to learn how you can password protect Excel workbooks when you need to secure the file and how to remove passwords when encryption is no longer necessary.

Password Protect Excel WorkbooksIf the data in your Excel file is extremely sensitive, it is important to use secure passwords. Don’t use easy-to-guess passwords (like your birthday or even “password”) to password protect Excel files. You can use free tools on the internet to generate random, secure passwords. Norton (the company that produces Symantec anti-virus software) has a free password generator on their website. Use a resource such as this (or any other number of free resources on the internet) to password protect Excel files with secure, difficult-to-crack passwords. You can also layer several different passwords within your Excel file to further restrict access to sensitive data and provide additional security if your initial password is compromised. While beyond the scope of this video, you can learn more about how to secure your Excel files by attending one of our classes or webinars.

To learn about other ways of securing your Excel files, sign up for our free email newsletter, where you’ll get valuable Microsoft Office tips and tricks right in your inbox. You’ll also get exclusive offers for our upcoming training classes and webinars!

2000px-Microsoft_Office_2013_logo.svg

The Microsoft Certification: Proving Your Knowledge

Obtaining a Microsoft certification is one of the easiest and most recognized ways of demonstrating your mastery of Excel, Word, PowerPoint, or any other Microsoft Office product. The Microsoft certification program is administered by Microsoft and uses a standardized curriculum and test that is recognized all over the world. With an official Microsoft certification in hand, you can confidently showcase your mastery of any Microsoft Office product to your employers and co-workers.

How Do You Obtain a Microsoft Certification?

On its website, Microsoft lists all of the topics that are covered in each of the different exams they offer. The page for the Microsoft Excel Specialist test is a good example. While there are multiple ways to prepare for each certification test, the best way to prepare for and pass the test is to take classes from a reputable training provider who has designed a training program around the skills tested on the certification exam. Once you have completed training and studying for the test, you simply register to take the test through Certiport and schedule an exam for a time that best fits your schedule.

What Microsoft Certifications Are Available?

Most Microsoft Office products have only one certification: the Microsoft Office Specialist (MOS) Certification. However, Excel and Word are different. There are actually two Microsoft certifications you can earn for these two products: the regular Microsoft Office Specialist Certification and a special Microsoft Office Specialist Expert Certification. The Expert Microsoft certification demonstrates a high-level (or power-user level) familiarity with the program. Users with an Expert Microsoft certification know all of the ins and outs of the program and are able to accomplish very complicated tasks with the programs. In order to obtain this certification, it is usually necessary to take several additional classes to obtain the appropriate mastery and knowledge of the program.

Finally, if you’re able to demonstrate your proficiency in several Microsoft Office products by obtaining the appropriate Microsoft certifications in several different programs (Word, Excel, SharePoint, Outlook, etc.), you can earn the Microsoft Office Specialist Master Certification. With this Microsoft certification in hand, you have demonstrated that you are fully functional in most Microsoft Office products and are able to complete complex and integrated tasks using multiple programs in the Office Suite. This is one of the most difficult, but also most impressive Microsoft certifications you can earn.

Microsoft Office Master Specialist Microsoft Certification

How Do You Get Started?

Earning a Microsoft certification provides you with valuable job skills and also allows you to easily showcase those job skills to current or future employers. The best way to get started is to sign up for our free email newsletter, where you’ll get valuable Microsoft Office tips and tricks right in your inbox. You’ll also get exclusive offers for our upcoming training classes and webinars!

Microsoft_Excel_2013_logo.svg

4 Advanced Excel Formulas Everyone Should Know

Whether you are applying for a new job or looking to get ahead in your current one, there are four key advanced Excel formulas that you should know in order to be successful. The following four formulas are extremely flexible and extremely powerful. Used by themselves or with others, these four advanced Excel formulas will enable you to amplify your data analysis.

1. VLOOKUP Formula

The VLOOKUP formula is one of Excel’s most useful functions. Consequently, knowing how to design and use VLOOKUPs is an Excel skill frequently requested by hiring managers. If you are confronted with a large set of data, a VLOOKUP allows you to pull select values out of the data set by defining a look-up value (such as a name or other unique key). The look-up value tells Excel which row in your data set to pull data from. Once you’ve specified the row you need via your look-up value, you simply need to tell Excel which column to pull data from. The VLOOKUP formula will pull the data at the intersection of the row and column you have specified. So, if your data set is very large, the VLOOKUP formula makes it very easy to quickly find the specific data you need. This makes the VLOOKUP one of the most important advanced Excel formulas you can learn.4 key advanced excel formulas

2. IF Formula

The IF formula allows you to insert logic into your data analysis. It functions as a simple “If…, then…” statement does in regular speech. Using the IF formula, you can create a logical test (such as A1>0), have Excel evaluate your statement/test, and then create two outcomes (based on whether the statement/test you have created evaluates as true or false). The real power in this particular advanced Excel formula lies in nesting the functions, which enables you to create logic trees. You can create an initial IF formula and then build additional IF formulas to serve as the outcomes of the initial formula. So, if A1>0 evaluates as true, you can tell Excel to evaluate the IF formula A2>10, and so on. Using the IF formula, it is very easy to design very complicated decision-making sequences.

3. CONCATENATE Formula

The CONCATENATE formula is a text formula, and its use is tied to the most important advanced Excel formula (the VLOOKUP). CONCATENATE enables you to combine two or more strings of text (or numbers) together. For example, if cell A1 contains “First” and cell A2 contains “Last”, then you can use CONCATENATE to create the value “FirstLast” in cell A3. This is especially helpful if you need to create unique look-up values (or keys) for a VLOOKUP formula. And if you have trouble remembering how to spell “Concatenate”, you can simply use the “& “symbol between your cell references (i.e. use A1&A2 rather than CONCATENATE(A1,A2)).

4. AND/OR Formulas

AND and OR formulas are two very similar types of logical formulas (much like the IF formula). The reason they are included on our list of advanced Excel formulas is because when they are used in conjunction with the IF formula, the flexibility of the IF formula increases exponentially. AND works just like you would expect it to. If everything inside of the formula evaluates as true, then the entire formula evaluates as true. If even one function inside the formula evaluates as false, the entire formula evaluates as false. OR works very similarly, except as long as at least one function inside the formula evaluates as true, the formula will evaluate as true. Only if every function inside OR evaluates as false will the entire formula evaluate as false. So, if you nest an AND or OR formula inside an IF formula, you can create extremely powerful advanced Excel formulas.

Learn Even More Advanced Excel Formulas

While these four formulas are some of the most important formulas to learn, there are many other advanced Excel formulas that will enable you to unlock the full potential of Microsoft Excel. Signup for one of our classes or upcoming webinars to learn about even more formulas and how you can use them in your everyday work.

You can also join our mailing list to receive free Excel tips and exclusive discounts on our training classes right in your inbox!

Microsoft_Excel_2013_logo.svg

Excel Drop Down Lists: 3 Ways to Create and Use Them

Excel drop down lists are extremely useful if you are sending out a spreadsheet (such as in a survey or form) and you need to restrict the type of answers your users can put in your spreadsheet. Watch the above video to learn how to create an Excel drop down list. When you’re done watching the video, read our tips below to learn how you can use Excel drop down lists in your spreadsheets.

1. You Need to Limit Your Users’ Options

If you’re sharing an Excel workbook and using it to collect data from other people, you may want to limit the number of options your users can provide. For example, if you were conducting a survey among your co-workers to find out everyone’s favorite type of cake, you would want to specify some options for your users to select. Otherwise, you may end up with a situation where two people who both like chocolate cake may give you two different answers. One may simply write “chocolate”; the other may write “chocolate cake”. Before you could perform any analysis on your data, you would need to clean it up and standardize the responses (i.e. change all the “chocolate cake” responses to “chocolate”) and correct any spelling/punctuation errors. One way to avoid this situation is to simply use an Excel drop down list, which limits the range of answers your users can provide in the first place and makes analyzing the data much easier.

2. You Need to Limit Your Own Options

Similar to the logic above, if you are working through a long list of data and have some sort of response column (inputting a date or marking every line “yes” or “no”), you can use an Excel drop down list to limit your own response options. This will help you avoid typos, spelling errors, etc. So, when you go to analyze your data (perhaps by creating a Pivot Table), you don’t have to spend time going back through your list to find that one typo that is skewing your data.

3. Excel Drop Down Lists Can Feed Into Formulas

Excel drop down lists can also be useful if you need a particular response to trigger other activities in your worksheet (as in a dashboard). For example, if you’ve designed a dashboard (or some other report), you could use an Excel drop down list to provide a menu of options that will refresh the data every time you change your selection. So, if you have all of your formulas feeding off the Excel drop down list, your users can quickly see different views of the data by selecting a different option from the list. The advantage here is in the uniformity of options in the list. When your users have an Excel drop down list to use, they don’t have to worry about misspelling or mistyping their inputs.

The Excel drop down list function is a very useful and very flexible resource if you know how to use it. You can learn more about how to use Excel drop down lists and other Excel functions by registering for one of our upcoming classes.

Join our mailing list today to receive free Excel tips and exclusive discounts on our training classes right in your inbox!

Microsoft_Excel_2013_logo.svg

Five Advanced Excel Skills Every Job Applicant Should Know

Find a new job with advanced Excel skillsAdvanced Excel skills are among the most commonly requested job skills hiring managers request of new employees. Many times, a job posting will simply ask for “Advanced Excel Skills” among the list of job requirements. What are these skills? What does it take to become an advanced Excel user? What follows is a list of key Excel skills that every advanced user should know.

1. Pivot Tables

Pivot Tables are among Excel’s most mysterious functions. Yet, learning how to use them is fairly straight-forward. Pivot Tables simply summarize data, and they provide you with additional functionality to present your data in almost any way you can imagine. Want to quickly get a count of how many employees work in each department in your office? Create a Pivot Table. Want to figure out what the average performance score is across several different work units? Create a Pivot Table. Need to analyze a complex report with dozens (or hundreds) of data columns? Create a Pivot Table. Pivot Tables are flexible and dynamic, which makes them extremely useful in the business world. Knowing how to create Pivot Tables is one of the most crucial advanced Excel skills you can learn. Become a master of Pivots, and you’ll be sure to impress any hiring manager.

2. VLOOKUP Function

If Pivot Tables are used more frequently than any other Excel feature in business offices every day, VLOOKUPs are a close second. Whether you’re validating financial reports or simply summarizing and analyzing data, you’ll probably use the VLOOKUP function several times in the course of your daily work. The real power of the VLOOKUP function lies in its simplicity, enabling you to quickly find and pull the exact data you need out of large data sets. Once you learn how the VLOOKUP works, you’ll find an increasing number of opportunities for its application in your everyday work. Along with Pivot Tables, VLOOKUPs are a key advanced Excel skill. If you know nothing else, knowing how to create Pivot Tables and use VLOOKUPs will put you ahead of most applicants in the job market.

3. Paste Special

Paste Special is another key advanced Excel skill. Any job that involves Excel (which will be most jobs) will involve moving data around. Paste Special allows you to unlock the power of the Cut/Paste functions. Maybe you only need to copy the formatting of a cell and not its content, or maybe you need to copy the output value of a formula without copying the formula itself. Paste Special allows you to do all of this and more! Everyone knows how to cut and paste, very few know how to use Paste Special.

4. Macros

Knowing how to automate tasks with code will set you far and above the competition when you apply for jobs. In Excel, you can do this with Macros, and you don’t even need to know anything about code to get started! Learning how to use Macros will enable you to write code that will automatically complete repetitive tasks for you. So, if you need to run reports every week (or every day), you can write some Macros to automate things such as the formatting and even some of the analysis. This will enable you to complete your work faster and more consistently. Learn how to use Excel to write a few simple Macros, and you will be able to impress hiring managers with your ability to fly through work assignments using your advanced Excel skills.Creating powerful charts is an advanced Excel skill

5. Charts

One of the final advanced Excel skills that will set you apart from the competition is your ability to design useful and informative charts. Anyone can create a chart (or graph). It is much more difficult to create a chart that provides a useful insight into some data. Knowing how to stack charts on top of each other and how to use both the primary and secondary axis are advanced Excel skills that make a new employee very valuable. If you intend to do any sort of data analysis, knowing how to create dynamic and informative charts is crucial.

Advance Your Job Search with Advanced Excel Skills

Knowing how to use Excel is an important part of most jobs you will apply to. Having a set of advanced Excel skills, like those discussed above, will set you apart from the legions of applicants you are competing against. If you master these skills, you will stand apart and above most of your competition.

Join our mailing list to learn how to use these and other advanced Excel skills. You’ll get free tips directly in your inbox that will help you become an Excel master, and you’ll also receive exclusive discounts to our training classes.

2000px-Microsoft_Office_2013_logo.svg

Microsoft Unveils New Version of Microsoft Office

Today, Microsoft unveiled a preview of its upcoming Office 2016 product suite. The new Word, Excel, and PowerPoint editions are available for preview for those running the Windows 10 Technical Preview. You can signup for the Technical Preview on Microsoft’s website.

The new Office suite will be pre-installed (for free) on phones and tablets running the new Windows 10 operating system. Everyone else will have to download the software. However, the added functionality of the updated programs does look promising. All the new “apps” are optimized for touch-screen devices. Many of the sharing features of Microsoft’s core programs have been re-vamped, and you will be able to see edits from other users on shared files in real-time. You will also be able to manage all your documents, emails, presentations, and even spreadsheets with the touch of a finger. Though, I would probably caution against discarding your mouse just yet. The new software is scheduled for launch towards the end of this year.

Stay up-to-date with the latest Microsoft news by signing up for our Desktop Secrets Newsletter. Signup today, and you’ll receive the latest Microsoft news right in your inbox. You’ll also gain access to exclusive tips and secrets and special offers for our training classes! Signup here.

Microsoft_Excel_2013_logo.svg

Finding Your Top Values with Conditional Formatting

Have you ever faced a large data set like this and wanted to quickly pick out the values in the top 10% (or 20%, 30%, etc.)?

largelist

The easiest way to accomplish this is to use Conditional Formatting. With Conditional Formatting, you can quickly pick out the top range of values (however you wish to define it). The advantage to using conditional formatting (especially in a case like this) is that it allows you to identify the values you need quickly without resorting your list. For example, if you wanted to sort the above list alphabetically by name but still wanted to identify the top scores on the two tests, you would use Conditional Formatting.

To start, simply select the data that you want to apply the formatting to and navigate to the menu shown below:

condform10

Then in the next pop-up, simply tell Excel which values you would like to highlight (and what color you would like to highlight them with):

condform10pop

In this example, I’ve set the range at 10%, but you can set it to any number you’d like (between 0%-100%) depending on what you need highlighted.

As you can see below, I’ve successfully highlighted the values in the top 10% in light red.

condform10final

Conditional Formatting can be an extremely useful and powerful tool if you know how to use it. To learn how you can unlock and use the full range of Conditional Formatting options, sign up for Excel 341, part of our Becoming an Excel Power-User Course Track.

Microsoft_Excel_2013_logo.svg

How to Use the VLOOKUP Function

Check out our latest video, which shows you how to use a VLOOKUP function to ensure the contents of two different lists match. The VLOOKUP is one of Excel’s most useful functions. Watch this video to learn how to use it in your everyday work.