How to Create a Dropdown List in Microsoft Excel

Source: https://www.maketecheasier.com/create-dr…8Make+Tech+Easier%29
Capture Date: 16.09.2018 22:41:30

Creating a dropdown list in your Excel spreadsheet can help to increase the efficiency of your work. This might come in handy, especially when you want your coworkers to provide certain information that may be relevant to the company. By using an Excel dropdown list, you can control exactly what can be entered into a cell by giving the users an option to select from a pre-defined list.

When you add a dropdown list to a cell, an arrow will be displayed next to it. Clicking on this arrow will open up the list and give the user the option to choose one of the items on the list. This will not only save you space on your spreadsheet but also make you look like like a superuser and impress your co-workers and boss. Here’s a step-by-step guide on how to create a dropdown list in Excel.

Step 1: Define the Contents of Your List

1. Open up a new Excel worksheet and put down the contents you want to appear on your list. Make sure each entry occupies one cell, and all entries are vertically aligned in the same column. Also, ensure there are no blank cells between the entries. In our case our dropdown menu will open a list of cities to choose from.

excel-drop-down-list-creation

2. Once you’re done assembling the list, highlight all the entries, right-click on them, and select “Define Name” from the menu that will show up.

create-excel-drop-down-list-define-name

3. This will open a new window with the title “New Name.” Choose a name for your list and enter the same in the “Name” text box.

excel-drop-down-list-spreadsheet-name

4. Click “OK.”

Step 2: Adding Your Dropdown List to a Spreadsheet

The next step is to add your dropdown list to a spreadsheet. Here’s how to do it.

1. Open a new or existing worksheet where you want to place your dropdown list.

2. Highlight the cell where you want to place the dropdown list. Click the “Data” tab, then locate the “Data validation” icon in the data tools section and click on it.

create-excel-drop-down-lists-data-validation

3. A data validation box will appear that has three tabs: Settings, Input Message, and Error Alert. In the Settings tab, select “List” from the “Arrow” dropdown list. A new option titled “Source” will now appear at the bottom of the window. Click the text box and then enter a “=” sign, followed by the name of your dropdown list. In our case it should read =cities.

 

The “Ignore blank” and “In-cell dropdown” boxes are checked by default. With the Ignore blank cell checked, it means it’s okay for people to leave the cell empty. But if you want every user to select an option from the cell, uncheck the box.

4. Click OK. That’s it. You have added a dropdown list to your spreadsheet.

With that completed, you can now proceed to the Input message tab.

Step 3: Set Input Message for Data Validation (Optional)

At times you may want a message (with description) to pop up when the cell containing the dropdown list is clicked. In that case you’ll need to click the “show input message” box in the Input message tab. You’ll also need to fill out the title and the input message in their respective boxes. Your dropdown list should now look something like the following image.

The last tab is for the error alert. Once defined, this one sends an error message if someone enters invalid data – data that is not on the list.

Wrapping Up

Dropdown lists are very common on websites and are very intuitive for the user. Their versatile nature renders them useful in virtually all industries. Be it in surveys, the business world or even in schools, you’ll always find the need to use Excel dropdown lists.

Was this article useful? Feel free to comment and share.

5 Best Excel Tutorials and Courses for Beginners Who Find It Intimidating

Source: https://www.makeuseof.com/tag/best-excel…s-courses-beginners/
Capture Date: 08.04.2018 00:58:46

Advertisement

Microsoft Excel is the best spreadsheet program around. All those features can also be intimidating. These free tutorials and courses are for those who find the software overwhelming but want to still learn it.

We won’t go over the more famous Excel teachers who offer free courses, even though several of them are good for beginners. We are looking at even simpler tutorials here, which will help you learn the software in bite-sized lessons at your own pace. Need to Learn Excel? 10 Experts Will Teach You for Free! Need to Learn Excel? 10 Experts Will Teach You for Free! Learning how to use Excel’s more advanced features can be tough. To make it a little easier, we have tracked down the best Excel gurus who can help you master Microsoft Excel. Read More

1. Microsoft’s Official Excel Video Training

best excel tutorials and online courses

Microsoft isn’t running away after it takes your cash. The Office suite is expensive, so if you’re putting down all that money, you better learn how to use it well. And the company is ready to teach you. Office 365 vs. Office 2016: Which Is Cheaper in the Long Run? Office 365 vs. Office 2016: Which Is Cheaper in the Long Run? Contrary to popular belief, Office 365 is not a greed-driven move to make more money. Office 2016 and Office 365 serve different audiences. We’ll show you which one is better value for you. Read More

The new Office Basics training videos are the official tutorials for newcomers, covering everything you would need to know. I especially like how Microsoft has broken down each segment, like Quick Start, Intro to Excel, Add and Format Charts etc. All videos are free to stream as well as download, in case you want offline backups or to train teams.

Go through one video at a time, practice it, and only then move on to the next. And take your time with it, don’t rush through.

2. Step-by-Step Learning Videos From GCF LearnFree

hqdefault.jpg

The Goodwill Community Foundation’s LearnFree online academy is an outstanding accompaniment to Microsoft’s official course. It is broken down into similar sections and videos, all of which are free on YouTube.

Again, it’s the careful break-up of learning Excel that is key here. GCF LearnFree turns it into a step-by-step process, with a total of 29 sections. Each section has a video, a long article, as well as recommended exercises.

Apart from Excel, there are many other such excellent software tutorials to check out on GCF LearnFree. In fact, it’s our recommended resource to learn Microsoft Access. How to Learn Microsoft Access: 5 Free Online Resources How to Learn Microsoft Access: 5 Free Online Resources Do you have to manage a large amount of data? You should look into Microsoft Access. Our free study resources can help you get started and learn the skills for more complex projects. Read More

3. Zapier’s Guide to Mastering Excel Online

best excel tutorials and online courses

Microsoft has a free version of Excel that anyone can use through a browser. Excel Online (or Office Online, as the official name goes) only requires a free Microsoft account and an active internet connection. While it’s not as robust as Excel 2016, it’s still pretty good.

Our friends at Zapier, the premier if-this-then-that automation service, have an excellent guide to Excel online to get you started. Through this, you will learn every part of what you can and can’t do with Excel on the web. There’s a surprising amount of abilities, so go through it carefully. Create Awesome Life Automations with Multi-Step Zaps Create Awesome Life Automations with Multi-Step Zaps Zapier, the single largest competitor to IFTTT, announced that it now offers users the ability to create multiple action automations called multi-step zaps. Read More

At times, Zapier tries to push you to use its service to create automation with Excel Online. But you can ignore that and focus on the web app alone.

4. Spreadsheeto’s Daily 10-Minute Email Course

best excel tutorials and online courses

Anyone who tells you that you need several days of dedicated time to learn Excel is flat out lying, according to the creators of Spreadsheeto. Instead, all you need are 10 minutes daily to watch a video, and another five minutes to practice what you learned.

Spreadsheeto works on the principle of micro-learning, sending small lessons to your inbox daily. Watch the video, and then use the accompanying Excel file to practice it. The file actually includes a sheet to replicate what you just saw, so you know whether you’re doing it right or not. Reap the Benefits of Microlearning with Bite-Size Lessons Every Day Reap the Benefits of Microlearning with Bite-Size Lessons Every Day A little learning is always better than no learning. That’s how the idea of using little snatches of time for bite-sized learning every day becomes a good habit to start. Read More

The free version of Spreadsheeto is a great introduction to the basics and how you will learn in the full-version paid course that costs $200. The daily emails will take you from basics to intermediate to advanced uses for Excel.

But it never gets overwhelming because of the staggered approach. And you can’t skip ahead either, since tomorrow’s lesson has not yet been delivered to you. Spreadsheeto forces you to learn at a slower pace, absorbing more. With time, you’ll even master VLOOKUP, the most important function in Excel.

5. The Start-to-Finish Giant Tutorial by Excel Easy

best excel tutorials and online courses

It makes no sense that Excel Easy is available for free on the internet. When you see the number of paid courses to learn Excel, it’s flabbergasting that such a great tutorial is available for free.

You start with the introduction, move on to the basics, learn how functions work, start analyzing data with Excel, and finally learn Excel VBA at the advanced stage. Each of those has the simplest explanation possible in the form of a single page, with screenshots and easy language.

As if that wasn’t enough, Excel Easy then gives you 300 examples of common tasks you can automate in Excel. Get ready to become an Excel macros ninja! 5 Resources for Excel Macros to Automate Your Spreadsheets 5 Resources for Excel Macros to Automate Your Spreadsheets Searching for Excel macros? Here are five sites that have got what you’re looking for. Read More

Why Is Excel Overwhelming?

Once you find out how many features exist in Microsoft Excel, it can feel overwhelming and daunting. I’ve felt that in the past, and especially when I saw others doing things that were way beyond the simple sums I was able to execute. The gap between what I could do and what they could do was too large, and that in itself made it seem too much effort to ever learn.

Enjoyed this article? Stay informed by joining our newsletter!

How to Create Automatic Backups of Your Outlook Data

Source: https://www.makeuseof.com/tag/create-aut…ackups-outlook-data/
Capture Date: 11.03.2018 19:37:12

Advertisement

If you’re a fan of desktop email, chances are that you use Microsoft Outlook to manage your inbox. Outlook stores your email data in a file with the PST extension. This contains all of your mail, calendar, and contact info.

You can hunt down this PST file by hand if you need it — perhaps you’re migrating to a new Windows account because of unfixable issues. However, if you want to regularly back up this file, or need to move it but don’t want to do so manually, there’s a tool that makes it fast and easy.

Safe PST Backup is a free utility that backs up your Outlook data to any folder you choose. The site asks you to enter your email address before downloading, but you can follow this direct download link to skip this. Once downloaded, install it as normal and double-click the new icon running in your system tray to open Safe PST Backup.

hqdefault.jpg

Without many options, the tool is simple to use. Click inside the Backup destination folder box to choose a place for your backups. You should pick a different hard drive if you’re using this for backup purposes. Then, the Start Backup button will begin the process. Once complete, the program will continue to back up your Outlook data once every four hours.

Click Options to change a few ways that Safe PST Backup runs. You can change the automatic backup schedule, or turn it off entirely. Most of the other settings won’t be useful for you, as they’re intended for enterprise use.

Have a broken inbox? Check out how to fix Outlook with its repair tool.

Do you back up your Outlook data? Let us know if you use this or a similar tool in the comments!

Image Credit: Redshinestudio via Shutterstock

Enjoyed this article? Stay informed by joining our newsletter!

Before Sharing an Excel Spreadsheet, Make Sure You Do These Things First

Source: https://www.makeuseof.com/tag/before-sha…e-sure-things-first/
Capture Date: 11.03.2018 15:11:23

Advertisement

Sharing Excel workbooks makes it easy to collaborate on data collection. But before you put your Excel files in the hands of other people, here are a few tips to get your worksheets ready for sharing.

Allow Multiple Simultaneous Edits

If you want multiple users to be able to edit a shared Excel sheet at the same time, do the following:

  1. Go to the Review tab and under Changes, click Share Workbook.
  2. In the dialog that opens up, make sure that Allow changes by more than one user at the same time is checked.
  3. Save your Excel file in a shared location where other users can access it.

Before Sharing an Excel Spreadsheet, Make Sure You Do These Things First MultipleChanges

One thing to note about allowing this: you could get conflicting changes if two users make edits to the same cells. The owner of the file will be alerted and can choose which changes to keep or discard.

Protect Worksheets or Cells

If there’s specific data you don’t want anyone to modify or delete, you can protect an entire worksheet, protect a workbook, or protect specific cells.

If you want to protect an entire worksheet or workbook:

  1. Go to the Review tab and click Protect Worksheet. (If you would prefer to protect the entire Workbook, click Protect Workbook.)
  2. Enter a password. This will allow anyone who has the password to unprotect it, but people without the password will still be able to view it.
  3. Under ‘Allow all users of this worksheet to’ you can select what other users can do on the worksheet including: format cells, add/delete rows or columns, and add hyperlinks.

If you want to protect a selection of cells:

  1. Select the cells you would like others to edit, right-click then, and click on Format Cells in the menu that pops up.
  2. Navigate to the Protection tab and make sure that Locked is unchecked.
  3. Go through the same three steps above.

Now all cells, aside from those that set you as unlocked, should be protected.

hqdefault.jpg

Add Drop-Down Menus

If you want other users only to add from a selection of data to specific cells, you can create a drop-down menu for a specific range of cells. You can give users the choice of selecting only from that list or allowing them to enter other information.

To add a drop-down menu to a range of cells, do the following:

  1. In a separate sheet in your workbook, create a list of the items you wish to include in your drop-down menu. These items should be in one row or column.
  2. Select the entire list, right-click, and select Define name.
  3. A dialog box will pop up where you can enter a name for your list. It can be anything that works for you — just don’t include any spaces.
  4. In the sheet where you will be entering data, select the cell or cells where you want the drop-down to appear. Navigate to the Data tab and click Data Validation.
  5. A dialog box will open up to a Settings tab. In the Allow field, select List.
  6. In the Source field, type =ListName.
  7. Make sure that In-cell dropdown box is checked. If you don’t want users to be able to leave the cell blank, make sure that Ignore blank box is unchecked.

The following steps are optional:

  1. If you want a message to appear when a cell is clicked, navigate to the Input Message tab. Here you can enter a message up to 225 characters that will pop up.
  2. If you want an Error Alert to appear, navigate to the Error Alert tab and make sure that Show error alert after invalid data is entered is checked. You can enter a specific message for your error alert.

hqdefault.jpg

What tips do you have for Excel users who want to share their workbooks? Let us know in the comments.

Enjoyed this article? Stay informed by joining our newsletter!

The 10 Best OneNote Macros You Should Try

Source: https://www.makeuseof.com/tag/best-onenote-macros/
Capture Date: 11.03.2018 15:16:59

Advertisement

Microsoft Office apps are great right out of the box, but there are plenty of ways to hack and optimize them for even greater productivity. One way for serious enthusiasts to bend Office to their will is using macros — sets of instructions you can execute with one click. 10 Simple Microsoft Word Hacks Everyone Can Do 10 Simple Microsoft Word Hacks Everyone Can Do Our knowledge about Microsoft Word is tested every time we get a document to work on. Every little tip learnt along the way helps. How about ten more tricks for your daily productivity? Read More

You might think that macros are only for Excel, but you’d be wrong. You can take your OneNote experience up a notch with some of the best macros, and we’re here to guide you through them.

Onetastic, the Macro Tool

We’re going to achieve macro ninja status using Onetastic, an add-in for OneNote with several functions. You’ll have to choose which version to download based on whether you have the 32-bit or 64-bit version of OneNote installed. This is separate from a 32- or 64-bit version of Windows, so you should confirm which you’re using.

hqdefault.jpg

Open OneNote and visit File > Account, then click the About OneNote button on the right side. At the top of this window, you’ll see purple text that says either 32-bit or 64-bit. Head to the Onetastic download page and choose the right version for your installation. Unzip the file, open the executable, and Onetastic installs in an instant.

OneNote 32-bit 64-bit

Once you’ve got it installed, pop open OneNote and you’ll see new options for Onetastic on the right side of the Home Ribbon tab. Since we’re interested in the macros on the right side of the list, you can click Settings then Show in a separate tab to send these buttons to a new Macros tab on the Ribbon.

You can click New Macro to start making your own, but the service already has hundreds available for download. Browse them via the Download Macros tab or visit the Macroland webpage. Let’s take a look at the best macros you can download now. How to Make Your Own Excel Keyboard Shortcuts the Easy Way How to Make Your Own Excel Keyboard Shortcuts the Easy Way Looking to make Excel your own? Here are the best ways to create custom keyboard shortcuts. Read More

A Note on Pricing

Onetastic is free to start using, and includes its basic features like OneCalendar, custom styles, and pinning pages and sections to the desktop. Macros are only available for a free trial period, but it’s based on usage, not time. Thus, if you only use a few macros from time to time, you can likely use the service for free. Once you approach the end of the trial period, you’ll see a warning that you need to upgrade to the Pro license for $15.

With a Pro subscription, you gain access to all the macros that were available at the time of your purchase as well as any that debut in the next 365 days. So, if you buy Pro today, you can download and use any macros that are released in the next year forever. Any macros released after one year will require another Pro license to use.

Have a look at the pricing page for more details.

The Best Macros

You don’t have to write a line of code to start boosting your OneNote productivity with macros. Here are the best ones available from Macroland at the time of writing. To install one, just download and open it to add it to your Macros tab.

1. Sort Pages

A simple macro, but super useful. Sometimes you have a ton of pages that are out of order and need to quickly sort them. This macro sorts all pages in your current section by ascending or descending alphabetical order. It also keeps sub-pages under their parents and sorts them as well.

OneNote Macro Sort Pages

DownloadSort Pages Macro

2. TOC in Current Section

If you’re using OneNote to write a wiki or guide, a table of contents (TOC) is probably something you need. You can easily generate one using this macro, which adds a new page with the contents of your current section. The Fast mode is quick but won’t work if you rename pages, while the Resilient mode will properly link to renamed pages at the cost of speed.

For a bigger scope, try the similar TOC in Current Notebook.

OneNote Macro Table of Contents

DownloadTOC in Current Section Macro

3. Insert Monthly Calendar

Those who like to map out their months will love this one. The Insert Monthly Calendar macro does just what you’d expect: pick a year, month, and first day of the week, and you’ll have an instant calendar on your page.

DownloadInsert Monthly Calendar Macro

4. Insert Horizontal Line

Though you can add a line in other Office programs by just typing several hyphens, there’s no easy way to do so in OneNote. This macro fixes that by giving you a quick shortcut to a long horizontal line. It’s not too exciting, but it beats holding down the underscore key for several seconds!

DownloadInsert Horizontal Line Macro

5. Search & Replace

It’s hard to believe that OneNote doesn’t have a Find and Replace functionality like so many other apps. Microsoft’s help page on this topic suggests that you use the Ctrl + F shortcut and paste over every single occurrence of a word to replace it. This would be a complete waste of time if the word appeared more than once, so you’ll definitely want this macro instead.

OneNote Macro Find and Replace

Just supply a word you want to replace and the text you want to replace it with. Then choose the scope of the search and whether it should match case, and you’re all done. Much better than manually replacing it.

DownloadSearch & Replace Macro

6. Sort Pages by Date

Depending on what you’re using OneNote for, sorting by date might be more useful than sorting by name. Maybe you want to find pages that are old and in need of an update, for example. There’s a macro to help with just that. You can sort in a section or an entire notebook, and sort by created or last modified date. 12 Ideas to Run Your Life Like a Boss With OneNote 12 Ideas to Run Your Life Like a Boss With OneNote OneNote can be your one-stop-shop for everything from organizing information, via managing projects, to keeping a daily journal. Here we propose a dozen ways in which you can use OneNote. Read More

DownloadSort Pages By Date Macro

7. Recent Edits

Collaborating with OneNote means that you probably have multiple people who edit the same notebook. When this is the case, it’s useful to see which pages have recently changed. Whether you want to make sure that someone is making changes where they’re supposed to or just want to remember what you were editing recently, give this macro a try. How You Can Use Microsoft OneNote for Project Management How You Can Use Microsoft OneNote for Project Management OneNote is the ideal collaboration tool for managing simple projects in small teams. And it’s completely free. See how OneNote can help your projects take off! Read More

DownloadRecent Edits Macro

8. Word Count

Microsoft Word has a built-in word counter, but OneNote does not. You could always copy and paste your page into a free word counting tool, but this macro makes it faster. Seeing how much content you have in OneNote might mean you take notes like a famous scientist!

hqdefault.jpg

DownloadWord Count Macro

9. Lower Case

Caps Lock is a huge pain. You almost never need to type in all caps, and only ever trigger it by mistake. If you haven’t remapped that key to something more useful, you might find yourself typing entire sentences in all caps before you realize it’s on.

Instead of pasting the text into an online converter, this macro lets you quickly convert selected text to all lower case. This is a quick way to fix an annoying mistake.

DownloadLower Case Macro

10. Where Am I

After you use OneNote for a while, you might get bogged down in all the notebooks, section groups, and pages. You certainly don’t want to get lost in your own notebooks, so using this macro lets you quickly find your bearings. Launch it, and you’ll get a nice breadcrumb trail of your current location.

OneNote Macro Breadcrumb

DownloadWhere Am I Macro

Ready to Pimp OneNote With Macros?

OneNote is already capable of so much, but these macros make it even better. If these aren’t enough for you, take a look at the hundreds of other options on Macroland, make your own, or even edit someone else’s macro to fit your own needs. Chances are if you want to do it, it’s possible with a macro. Start automating these slow tasks via macros and you’ll save yourself time! 12 Tips to Take Better Notes with Microsoft OneNote 12 Tips to Take Better Notes with Microsoft OneNote These 12 tips show you how to take better notes in MIcrosoft OneNote. Today, digital note-taking is as much about skill, as it is about the tool. Read More

To protect yourself from the dark side of macros, check out how they’re used to create Word malware.

Do you use Macros to enhance your OneNote experience? Please share your favorites with us by leaving a comment!

Enjoyed this article? Stay informed by joining our newsletter!