How to Add a Drop-Down List to a Cell in Excel

Drop-down lists are very useful data entry tools we see just about everywhere, and you can add custom drop-down lists to your own Excel worksheets. It’s easy and we’ll show you how.
Drop-down lists make it easier and more efficient to enter data into your spreadsheets. Simply click the arrow and select an option. You can add drop-down lists to cells in Excel containing options such as Yes and No, Male and Female, or any other custom list of options.

It’s easy to add a drop-down list to a cell in Excel, but the process is not intuitive. Drop-down lists are created using the Data Validation feature. We’re going to create a drop-down list with a selection of age ranges to show you how it’s done.
To begin, enter the list of age ranges into sequential cells down a column or across a row. We entered our age ranges into cells A9 through A13 on the same worksheet, as shown below. You can also add your list of options to a different worksheet in the same workbook.





Now, we’re going to name our range of cells to make it easier to add them to the drop-down list. To do this, select all the cells containing the drop-down list items and then enter a name for the cell range into the Name box above the grid. We named our cell range Age.





Now, select the cell into which you want to add a drop-down list and click the “Data” tab.





In the Data Tools section of the Data tab, click the “Data Validation” button.





The Data Validation dialog box displays. On the Settings tab, select “List” from the Allow drop-down list (see, drop-down lists are everywhere!).





Now, we’re going to use the name we assigned to the range of cells containing the options for our drop-down list. Enter =Age in the “Source” box (if you named your cell range something else, replace “Age” with that name). Make sure the “In-cell dropdown” box is checked.
The “Ignore blank” check box is checked by default. This means that the user can select the cell and then deselect the cell without selecting an item. If you want to require the user to select an option from the drop-down list, uncheck the Ignore blank check box.





You can add a popup message that displays when the cell containing the drop-down list is selected. To do this, click the “Input Message” tab on the Data Validation dialog box. Make sure the “Show input message when the cell is selected” box is checked. Enter a Title and an Input message and then click the “OK” button.





When the cell containing the drop-down list is selected, you’ll see a down arrow button to the right of the cell. If you added an input message, it displays below the cell. The down arrow button only displays when the cell is selected.





Click the down arrow button to drop down the list of options and select one.





If you decide you want to remove the drop-down list from the cell, open the Data Validation dialog box as described earlier and click the “Clear All” button, which is available no matter which tab is selected on the dialog box.





The options on the Data Validation dialog box are reset to their defaults. Click “OK” to remove the drop-down list and restore the cell to its default format.





If there was an option selected when you removed the drop-down list, the cell is populated with the value of that option.






Source: https://www.howtogeek.com/290104/how-to-add-a-drop-down-list-to-a-cell-in-excel/

How to Compare Two Columns in Excel: 7 Methods

 When working with large Excel spreadsheets, comparing the data from two columns can be time-consuming. Instead of analyzing the columns and writing “Match” or “Mismatch” into a separate column, you can use Excel’s functions to streamline the process.

We’ll take a look at how to use different Excel functions to compare two columns and identify matching or mismatching data.

1. How to Highlight Duplicate Data

If you want to compare two columns in Excel, but don’t want to add a third column showing if the data exists in both columns, you can use the Conditional formatting feature.

1. Select the data cells you want to compare.
2. Head to the Home tab.
3. From the Styles group, open the Conditional Formatting menu.
4. Click Highlight Cells Rules > Duplicate Values.

    

   

   5. In the Duplicate Values window, make sure the Format cells that contain option is set to Duplicate and choose the formatting option next to values with.
5. Click OK.

Excel will now highlight the names that are present in both columns.

2. How to Highlight Unique Data

You can use the same function if you want to identify data that isn’t part of both columns.

1. Select the data set.
2. Once again, head to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. For Format cells that contain, choose Unique.
4. Select how the mismatched data should be highlighted and click OK.

Excel will now highlight the names that can be found only in one of the two columns.

 

While these methods are quite easy to use, they might not be efficient for larger spreadsheets, so you’ll have to take think about an efficient way to organize your spreadsheet. So we’ll take a look at more complex solutions that show you which rows have the same data or use an additional column to display values indicating if the data matches or not.

3. Highlight Rows With Identical Data

If you need a better visual representation of identical data, you can make Excel find matching values in two columns and highlight the rows with matching data. As we did on the previous method, we’ll use the Conditional formatting feature, but will add a few extra steps.

This way, you will have a visual indicator that will help you identify matching data faster than reading through a separate column. Follow these steps to use Excel’s conditional formatting to compare two sets of data:

1. Select the data you want to compare (don’t include the headers) and open the Home tab.
2. Click Conditional Formatting and select New Rule.
3. From Select a Rule Type, click Use a formula to determine which cells to format.
4. Enter =$A2=$B2 into the field below Format values where this formula is true. Here, A and B correspond to the two columns we are comparing.

    

   

   5. To customize how Excel will highlight the rows, click Format, and in the Format cells window, select the Fill tab. You can choose the background color, pattern style, and pattern color. You will get a sample, so you can preview the design. Click OK once you’ve completed the customization process.
   
   
5. Click OK in the New Formatting Rule window, so Excel will highlight the rows with matching data instantly.

 

When comparing two columns in Excel using this method, you can also highlight the rows with different data. Go through the above steps, and at step 5, enter the =$A2<>$B2 formula within the Format values where this formula is true field.

4. Identify Matches With TRUE or FALSE

You can add a new column when comparing two Excel columns. Using this method, you will add a third column that will display TRUE if the data matches and FALSE if the data doesn’t match.

For the third column, use the =A2=B2 formula to compare the first two columns. If you think your spreadsheet will look too crowded with the TRUE and FALSE rows, you can set a filter in Excel, so it will only show the TRUE values.

5. Compare Two Columns With an IF Function

A different method to analyze Excel data from two columns is to use an IF function. This is similar to the above method, but it comes with the advantage that you can customize the displayed value.

Instead of having the TRUE or FALSE values, you can set the value for matching or different data. For this example, we’ll use the Data matches and Data doesn’t match values.

The formula we’ll use for the column showing the results is =IF(A2=B2,"Data Matches","Data Doesn’t Match").

6. Compare Two Columns With a VLOOKUP Function and Find Matching Data

Another way to have Excel find duplicates in two columns is to use a VLOOKUP function. Excel will compare each cell in the second column against the cells in the first column.

Use the =VLOOKUP(B2,$A$2:$A$14,1,0) for the column displaying the results. Just make sure you adjust the data range.

 

When using this formula, Excel will display the matching data or use a #N/A value. However, the #N/Avalue might be confusing, especially if you send the spreadsheet to someone else. If they are not experienced when it comes to Excel, they could believe there’s a mistake.


So to avoid any confusion, upgrade the VLOOKUP function to an IFERROR function. If you need to find data that is in column B and is also in column A, use the =IFERROR(VLOOKUP(B2,$A$2:$A$14,1,0),"Data Doesn't Match") formula.

This is just an example of a VLOOKUP function that you could use to search spreadsheets efficiently.

7. How to Compare Two Columns and Extract Data

Besides comparing two Excel columns for matches, you can also use the VLOOKUP function to extract matching data. This will save you time as you don’t have to manually go through the first column and search for relevant data.

Also, if data in the second column misses from the first column, Excel will display a #N/A value. For this, use the =VLOOKUP(D2,$A$2:$B$14,2,0) formula.

 

Note: If you want to protect your results from spelling mistakes, use the =VLOOKUP("*"&D2&"*",$A$2:$B$14,2,0) formula. Here, the asterisk (*) has the role of a wild card character, replacing any number of characters. This is just one of the ways you could use a wild card for better results in Excel

Compare Excel Columns With Ease

As we’ve discussed, there are plenty of ways and tricks you can use to compare two columns in an Excel spreadsheet and get the best results. You can even add a third column to display the results. You can even use Excel to compare physical documents, as you can quickly import data in Excel with its built-in tools.

Source: https://www.makeuseof.com/search/?q=How+to+Compare+Two+Columns+in+Excel%3A+7+Methods

How to Import Data From a PDF to Microsoft Excel

To import data from a PDF file to Excel, open Excel and go to the "Data" tab, then navigate to Get Data > From File > From PDF and select your PDF. Customize the import settings as necessary for your data, then click "OK."

If you have a PDF document such as a bank statement or financial report, you may want to pull this information into Microsoft Excel. With a built-in feature, you can easily import and automatically convert a PDF to Excel.

This convenient feature can save you from hunting down a PDF file converter. Not only that, but it allows you to pick and choose the data from the file you want to import. The entire process takes only a minute.

Note: Since the time this feature was implemented, August 2020, it has only been available to Office 365 subscribers.

Connect a PDF File to Excel

To get started, select the sheet you want to work with in Excel and go to the Data tab. Click the Get Data drop-down arrow on the left side of the ribbon. Move your cursor to From File and pick “From PDF.”

Locate your file in the browse window, select it, and click “Import.”

Next, you’ll see the Navigator pane. On the left are the tables and pages in your PDF file. You can search for one at the top or simply select an element and see a preview on the right side.

When you see the item you want to import, click “Load” at the bottom of the window.

Once your data is imported from the PDF, you should see it in your sheet along with the Queries & Connections sidebar. This handy spot lets you adjust the connected data if you wish, and we’ll explain these options below.

Adjust the Load Settings

If you prefer to load the data in a specific format such as a pivot table or to a new worksheet, select the Load drop-down arrow and pick “Load to” instead.

Then choose your options in the Import Data window and click “OK.”

RELATED: How to Use Pivot Tables to Analyze Excel Data

Transform the Data with Power Query

Another option for importing data from your PDF is to transform it using the Power Query Editor. In the Navigator window where you select the element to import, click “Transform Data” at the bottom instead of “Load.”

This will open the Power Query window where you can do things like choosing or adding more columns, transposing rows and columns, managing parameters, and formatting the data.

Adjust the Connected Data

If you want to adjust the data you imported, you can do so by opening the data from the Queries & Connections sidebar. You can then take actions such as editing it using the Power Query Editor, duplicating, merging, adding a reference, or deleting the connected data.

RELATED: How to Create Your Own Data Type in Microsoft Excel

As mentioned, you’ll see the Queries & Connections sidebar open when you import your PDF file. If you happen to close it, you can reopen it by going to the Data tab and clicking “Queries & Connections” in the ribbon.

Place your cursor over the connected data and you’ll see a window appear. At the bottom, you have actions for View in Worksheet, Edit, and Delete. If you click the three dots, you’ll see options like Duplicate, Reference, and Merge.

You can also manipulate the data within your sheet as you normally would. If you’re using a table, you can filter and sort. You can also select a different style, update the font, and apply additional formatting.

RELATED: How to Create and Use a Table in Microsoft Excel

For those times when the data you need to work with or analyze comes in the form of a PDF, remember that you can pull that PDF file right into Microsoft Excel.

If you’re looking to do the reverse, learn how easy it is to save an Excel sheet as a PDF file.

Source: https://www.howtogeek.com/770474/how-to-import-data-from-a-pdf-to-microsoft-excel/

How to export PDF files from Acrobat to Excel, and Excel to Acrobat

You have just a few choices when working with PDFs and Microsoft Excel compared to everything you can do with PDFs in Microsoft Word. You cannot import PDFs from Adobe Acrobat directly into Excel 2016. However, you can export PDFs from Excel to Acrobat, and you can export from Acrobat to Excel. We'll show you how.
1- Export from Excel to Acrobat
2- Export from Acrobat to Excel

Export from Excel to Acrobat

1. To export from Excel to Acrobat, open an Excel spreadsheet.
2. Then choose File > Export, and click the Create PDF/XPS button.

JD Sartain / IDG Worldwide

File export create pdf

3. The Publish As PDF screen appears and displays the Excel filename, followed by the Acrobat extension PDF in the File Name field box, followed by the PDF extension in the Save As Type field box (Products.pdf).
4. Click the Publish button and the system jumps to Adobe Acrobat. showing your Excel spreadsheet as an Acrobat PDF file.

JD Sartain / IDG Worldwide

Publish and jump to Acrobat

5. Choose File > Save As, then browse to the applicable folder (if necessary).
6. On the Save As screen, confirm the File Name (or change it), select Adobe PDF files (*.pdf) for the Save As Type, then click the Save button. Conversion complete!

JD Sartain / IDG Worldwide

Save As Adobe pdf

Export from Acrobat to Excel

1. To export the PDF from Acrobat to Excel, open the PDF file.

2. Select Export To > Spreadsheet > Microsoft Excel Workbook.
3. On the Save As screen, Acrobat enters the Export File Name for you followed by the Excel extension xlsx.
4. Ensure the Save As Type says: Excel Workbook (*.xlsx).
5. Click the Save button and the conversion is complete!

JD Sartain / IDG Worldwide

Convert the pdf back to an Excel worksheet

To comment on this article and other PCWorld content, visit our Facebook page or our Twitter feed.

Related:

• Software
• Microsoft
• Adobe Systems
• Business

JD Sartain is a technology journalist from Boston. She writes the Max Productivity column for PCWorld, a monthly column for CIO, and regular feature articles for Network World.


Source: https://www.pcworld.com/article/3314749/software/how-to-export-pdf-files-from-acrobat-to-excel-and-excel-to-acrobat.html?idg_eid=8458d72e614c68751de48f5c175520f4&email_SHA1_lc=f7e21d5e818ec725d3ee897e41e5054f614367ba&cid=pcw_nlt_pcw_powertips_html_2018-10-23&utm_source=Sailthru&utm_medium=email&utm_campaign=PCWorld%20Power%20Tips%202018-10-23&utm_term=pcw_powertips_html

Top 25 Excel 2016 Tips and Tricks

Top 15 Advanced Excel 2016 Tips and Tricks

How to create, edit, and format images in Excel

Imagine a spreadsheet that can perform like a graphics program.

How do we add geometric shapes in Excel, and can they be custom-designed? Excel provides over 16 dozen geometric shapes that you can size and manipulate to your specific needs, plus hundreds of special effects to customize those shapes. Additional shapes are available in the form of icons, pictures, or 3D Models, all of which can be sized, manipulated, and custom-designed.

Table of Contents

 

Insert Shapes, Icons, Pictures, & 3D Models

1. For Shapes, select INSERT > Illustrations > Shapes and choose one from the drop-down list.

2. For Icons, select INSERT > Illustrations > Icons and choose one from the huge drop-down submenu, which is organized by category.

3. Shapes and icons are also available as online pictures. Select INSERT > Illustrations > Online Pictures and type Icons or Shapes in the search box, then press Enter. Locate the shape or icon you need, click once to highlight, then click the Insert button.

4. The best shapes are under INSERT > Illustrations > 3D Models. The sizing and manipulation options for the 3D Models are amazing! You can change the X, Y, or Z Rotation, change the Camera View for the X, Y, and Z Position or Look-at-Point; or you can select a three-dimensional view from a list of preset options.


NOTE: When you select Shapes, as soon as you click the one you want, Excel inserts the drawing crosshair into your spreadsheet, which then requires you to hold down the left mouse button and drag down and over to “draw” the shape. With the other INSERT > Illustrations (Icons, Online Pictures, 3d Models, etc.), Excel provides a graphical submenu for you to select from the image library. Once you click the image you want, you must then click the Insert button for the image to appear on the spreadsheet.

JD Sartain / IDG Worldwide

Insert Shapes, Icons, Pictures, or 3D Models

 

Sizing the Illustrations

1. Sizing the illustrations is a familiar process that you have likely used in other programs. After you insert a shape, icon, picture, or 3D model, notice the four circles (called image “handles”) in the four corners, plus the circles located at the middle top and bottom, and both sides.
2. Place your cursor on one of the circles (e.g., bottom right), hold down the left mouse button and drag down and over to enlarge the image; or drag up and to the left to reduce it. Those are just suggestions. You can drag up or down, left or right, based on whatever feels comfortable to you and produces the desired effect.

NOTE: Use the corner circles/handles to size the image proportionally. Use the mid-top, mid bottom, mid-left, or mid-right circles to distort the image. For example, if you use the top or bottom middle circle, the image gets shorter and wider (or fatter). If you use the middle-side circles, the image stretches longer and thinner.

JD Sartain / IDG Worldwide

02 Size or distort the illustrations using the image handles

 

Editing the Illustrations

1. When you insert a Shape into an Excel spreadsheet, the DRAWING TOOLS tab appears as long as the shape is selected. Select Edit Shape (in the first box on the Ribbon Menu), then click Edit Points. Notice that Excel adds editing points all over the selected image.
2. Click the points (one at a time) to modify the image and see your results.
3. You can also edit the icons. Insert one from the library (e.g., a butterfly). In its current form, all you can do is size it, distort it, or make it all one color, one pattern, or one gradient, with or without an outline.

4. To edit an icon, select it. Notice that a new GRAPHIC TOOLS tab appears, with a FORMAT tab beneath it displaying the Format Ribbon menu. Click the Convert to Shape button. In the popup menu that asks: “. . . Do you want to convert it to a Microsoft Drawing object?” click the Yes button.
5. Looks like nothing changed, but it did. Hold down the Ctrl key and click one of the butterfly’s wings. Notice that all the pieces of the butterfly can be individually selected, modified (as in stretched, squeezed, rotated, etc.). You can also add different colors, patterns, gradients, lines, and special effects to each individual piece of the butterfly; that is, four wings and its body.

JD Sartain / IDG Worldwide

03 Editing the Illustrations

Custom Formatting/designing the Illustrations
1. In Excel, the term for custom-designing the attributes of an image is called formatting. When an image is selected, the FORMAT tab appears, showing all the formatting options and features for that particular image.
2. Formatting in Excel includes Color, Artistic Effects, Transparency, Styles, Borders, seven Picture Effects (with lots of custom or preset features in each Effect), and Picture Layouts.
3. You can also do Color Corrections and Remove the Background of an image that contains more than just the “floating” object.
4. As mentioned above, images can be flipped, rotated, scaled, distorted, grouped, aligned, cropped, or sent forward or back.

JD Sartain / IDG Worldwide

04 Custom Formatting the Illustrations

5. The FORMAT tab includes three different sets of tools: Drawing Tools, Picture Tools, and Graphics Tools. These sub-tabs (even though they appear above the FORMAT tab) are displayed based on the object selected—Illustrations, Pictures, or Graphics.
6. Load some images and experiment with the formatting options until you feel comfortable with the different menus.

JD Sartain / IDG Worldwide

05 Drawing Tools, Picture Tools, Graphics Tools menus

Source: https://www.pcworld.com/article/3389141/how-to-create-edit-and-format-images-in-excel.html#tk.rss_howto