Tutorial: Import Data into Excel, and Create a Data Model.Microsoft Excel Tutorial — Free and Online
Looking for:
- Excel Training Tutorial Free Online for Excel |
Download free tutorials and courses on microsoft excel tutorial - Documents PDF - Get Office apps for Mac
2013 microsoft excel tutorial free. Excel 2013 training
In addition, that field must correspond to a field in the Data Model. You can, however, create new columns by using calculated fields based on the existing data. By looking through the Hosts table, then looking at other Data Model tables, we find a good candidate for a unique field we could create in Hosts , and then associate with a table in the Data Model.
Both tables will require a new, calculated column in order to meet the requirements necessary to establish a relationship. In Hosts , we can create a unique calculated column by combining the Edition field the year of the Olympics event and the Season field Summer or Winter. In the Medals table there is also an Edition field and a Season field, so if we create a calculated column in each of those tables that combines the Edition and Season fields, we can establish a relationship between Hosts and Medals.
The following screen shows the Hosts table, with its Edition and Season fields selected. The goal is to create a calculated column in the Hosts table, and then in the Medals table, which can be used to establish a relationship between them. Select the Hosts table in Power Pivot. Adjacent to the existing columns is an empty column titled Add Column. Power Pivot provides that column as a placeholder. There are many ways to add a new column to a table in Power Pivot, one of which is to simply select the empty column that has the title Add Column.
In the formula bar, type the following DAX formula. As you type, AutoComplete helps you type the fully qualified names of columns and tables, and lists the functions that are available. Use tab to select AutoComplete suggestions. You can also just click the column while typing your formula, and Power Pivot inserts the column name into your formula. Values are populated for all the rows in the calculated column.
Such fields are called a primary key. You can rename any column by double-clicking it, or by right-clicking the column and choosing Rename Column. When completed, the Hosts table in Power Pivot looks like the following screen. The Hosts table is ready. Start by creating a new column in the Medals table, like we did for Hosts.
Notice that Add Column is selected. This has the same effect as simply selecting Add Column. The Edition column in Medals has a different format than the Edition column in Hosts. Before we combine, or concatenate, the Edition column with the Season column to create the EditionID column, we need to create an intermediary field that gets Edition into the right format.
In the formula bar above the table, type the following DAX formula. When you finish building the formula, press Enter. Values are populated for all the rows in the calculated column, based on the formula you entered.
Rename the column by right-clicking CalculatedColumn1 and selecting Rename Column. Type Year, and then press Enter. When you created a new column, Power Pivot added another placeholder column called Add Column. In the formula bar, type the following DAX formula and press Enter.
Sort the column in ascending order. The Medals table in Power Pivot now looks like the following screen. Notice many values are repeated in the Medals table EditionID field. What is unique in the Medals table is each awarded medal. The unique identifier for each record in the Medals table, and its designated primary key, is the MedalKey field.
The next step is to create a relationship between Hosts and Medals. Create a relationship using calculated columns. You can also switch between Grid view and Diagram view using the buttons at the bottom of the PowerView window, as shown in the following screen. Expand Hosts so you can view all of its fields. We created the EditionID column to act as the Hosts table primary key unique, non-repeated field , and created an EditionID column in the Medals table to enable establishment of a relationship between them.
We need to find them both, and create a relationship. You maybe interested: Barcode. No barcode Code font, Excel macro, formula, vba, to create, print Code images in Excel spreadsheet. All generated barcodes using this product are of high quality! Code Barcode Addin is designed by OnBarcode. This barcode generator is able to link barcodes to cell-contents. Start with a new excel workbook to activate the task panel. Choose "CODE " that enables the encodable characters automatically to be changed from one code set to another.
And type specified data. Then click "Generate" to create the corresponding Code barcode image. In the Alt Text pane, you can also select Generate a description for me to have Microsoft cloud-powered intelligent services create a description for you. You see the result in the alt text field.
Remember to delete any comments PowerPoint added there, for example, "Description automatically generated. Note: For audio and video content, in addition to alt text, include closed captioning for people who are deaf or have limited hearing.
People who use screen readers sometimes scan a list of links. Links should convey clear and accurate information about the destination. For example, avoid using link texts such as "Click here," "See this page," Go here," or "Learn more.
You can also add ScreenTips that appear when your cursor hovers over text or images that include a hyperlink. For example, this hyperlink text matches the title on the destination page: Create more with Microsoft templates.
For the step-by-step instructions on how to create hyperlinks and ScreenTips, go to Add a hyperlink to a slide. An accessible font doesn't exclude or slow down the reading speed of anyone reading a slide, including people with low vision or reading disability or people who are blind. The right font improves the legibility and readability of the text in the presentation.
For the step-by-step instructions on how to change fonts in PowerPoint go to Change the fonts in a presentation or Change the default font in PowerPoint. To reduce the reading load, select familiar sans serif fonts such as Arial or Calibri. Avoid using all capital letters and excessive italics or underlines. A person with a vision disability might miss out on the meaning conveyed by particular colors.
For headings, consider adding bold or using a larger font. The text in your presentation should be readable in a high contrast mode. For example, use bright colors or high-contrast color schemes on opposite ends of the color spectrum.
White and black schemes make it easier for people who are colorblind to distinguish text and shapes. Use the pre-designed Office Themes to make sure that your slide design is accessible. For instructions, go to Use an accessible presentation template or Use built-in slide designs for inclusive reading order, colors, and more. Use the Accessibility Checker to analyze the presentation and find insufficient color contrast. It finds insufficient color contrast in text with or without highlights or hyperlinks in shapes, tables, or SmartArt with solid opaque colors.
It does not find insufficient color contrast in other cases such as text in a transparent text box or placeholder on top of the slide background, or color contrast issues in non-textual content.
PowerPoint supports the playback of video with multiple audio tracks. It also supports closed captions and subtitles that are embedded in video files. Currently, only PowerPoint for Windows supports insertion and playback of closed captions or subtitles that are stored in files separate from the video. For all other editions of PowerPoint such as PowerPoint for macOS or the mobile editions , closed captions or subtitles must be encoded into the video before they are inserted into PowerPoint.
Supported video formats for captions and subtitles vary depending on the operating system that you're using. Each operating system has settings to adjust how the closed captions or subtitles are displayed.
For more information, see Closed Caption file types supported by PowerPoint. Closed captions, subtitles, and alternative audio tracks are not preserved when you use the Compress Media or Optimize Media Compatibility features. Also, when turning your presentation into a video , closed captions, subtitles, or alternative audio tracks in the embedded videos are not included in the video that is saved. When you use the Save Media as command on a selected video, closed captions, subtitles, and multiple audio tracks embedded in the video are preserved in the video file that is saved.
Videos include an audio track with video descriptions, if needed, for users who are blind or have low vision. Videos that include dialogue also include closed captions, in-band closed captions, open captions, or subtitles in a supported format for users that are deaf or hard-of-hearing. For more information, refer to Add closed captions or subtitles to media in PowerPoint.
You can save your presentation in a format that can be easily read by a screen reader or be ported to a Braille reader. Before converting a presentation into another format, make sure you run the Accessibility Checker and fix all reported issues. When your presentation is ready and you've run the Accessibility Checker to make sure it is inclusive, you can try navigating the slides using a screen reader, for example, Narrator. Narrator comes with Windows, so there's no need to install anything.
This is one additional way to spot issues in the navigation order, for example. Press the Tab key to navigate the elements within the slide and fix the navigation order if needed. To move the focus away from the slide content, press Esc or F6. Rules for the Accessibility Checker.
Everything you need to know to write effective alt text. Use the Accessibility Reminder to notify authors of accessibility issues. Make your Word documents accessible to people with disabilities. Make your Excel documents accessible to people with disabilities.
Make your Outlook email accessible to people with disabilities. Closed Caption file types supported by PowerPoint. If you must use an image with text in it, repeat that text in the presentation. In the alternative text, briefly describe the image and mention the existence of the text and its intent. Add alt text to visuals in Microsoft Add alt text to visuals in Office When someone who can see reads a slide, they usually read things, such as text or a picture, in the order the elements appear on the slide.
In contrast, a screen reader reads the elements of a slide in the order they were added to the slide, which might be very different from the order in which things appear.
To make sure everyone reads the contents in the order you intend, it's important to check the reading order. PowerPoint contains built-in slide layouts that you can apply to any slide.
When you use them with a new slide, these layouts automatically make sure that the reading order works for everyone. Use built-in slide designs for inclusive reading order. To determine whether hyperlink text makes sense as standalone information and whether it gives readers accurate information about the destination target, visually scan the slides in your presentation. For example, instead of linking to the text Click here , include the full title of the destination page.
You can even use the URL of the page if it's short and descriptive, for example, www. Add hyperlink text and ScreenTips. Visually scan the slides in your presentation. Use an accessible slide design. Use strong contrast between text and background, so people with low vision can see and use the content.
Use dark text on a white or off-white background, or reverse it and use white text on a dark background. White and black schemes also make it easier for people who are colorblind to distinguish text and shapes. Use unique slide titles.
They often see text merge or distort. For people who have dyslexia or have low vision, reduce the reading load. For example, they might benefit from familiar sans serif fonts, such as Arial or Calibri. Include ample white space between sentences and paragraphs. Format text for accessibility. Video description makes video more accessible to individuals who are blind or visually impaired. The following procedures describe how to add alt text to visuals in your PowerPoint presentations in Microsoft Add alt text to images.
Add alt text to shapes. Add alt text to SmartArt graphics. Add alt text to charts. Make visuals decorative. For audio and video content, in addition to alt text, include closed captioning for people who are deaf or have limited hearing.
To enable right-click on your Mac, make sure that the Secondary click option is selected in System Preferences. PowerPoint does not automatically generate alt texts for images. If you want to add an image that is an icon, screenshot, or other image that is not a photograph, you need to add the alt texts manually.
Tip: To spell check and correct a word you typed, just right-click the word and select from the suggested alternatives. Tip: You have to right-click somewhere inside the frame that surrounds the entire shape, not inside one of its parts. Tip: You have to right-click somewhere inside the frame that surrounds the entire SmartArt graphic, not inside one of its parts.
Select a SmartArt graphic. Type sentences to describe the SmartArt graphic and its context to someone who cannot see it. Tip: You have to right-click somewhere inside the frame that surrounds the entire chart, not inside one of its parts.
Select Edit Alt Text The Alt Text pane opens on the right side of the slide. If your presentation has visuals that are purely decorative, you can mark them as such without needing to write any alt text. When a screen reader finds such an image, it simply announces they are decorative, so the user knows they are not missing any information. To place the PivotTable report in a new worksheet starting at cell A1, click New worksheet. To verify or change connection properties, click Properties , make the necessary changes in the Connection Properties dialog box, and then click OK.
You can either use Power Query or the Data Connection wizard. In the Access SharePoint dialog box that appears next, select a credentials option:. In the Navigator dialog, select the Database and tables or queries you want to connect to, then press Load or Edit.
In the Active Directory Domain dialog box for your domain, click Use my current credentials , or Use alternate credentials.
For Use alternate credentials authentication, enter your Username and Password. After the connection succeeds, you can use the Navigator pane to browse all the domains available within your Active Directory, and drill down into Active Directory information including Users, Accounts, and Computers. See: Which version of Office am I using?
If you aren't signed in using the Microsoft Work or School account you use to access CDS for Apps, click Sign in and enter the account username and password. If the data is good to be imported as is, then select the Load option, otherwise choose the Edit option to open the Power Query Editor. Note: The Power Query Editor gives you multiple options to modify the data returned. For instance, you might want to import fewer columns than your source data contains. Note: If you need to retrieve your storage access key, browse to the Microsoft Azure Portal , select your storage account, and then click on the Manage Access Key icon on the bottom of the page.
Click on the copy icon to the right of the primary key, and then paste the value in the Account Key box. Note: If you need to retrieve your key, return to the Microsoft Azure Portal , select your storage account, and click on the Manage Access Key icon on the bottom of the page. Click on the copy icon to the right of the primary key and paste the value into the wizard.
Click Load to load the selected table, or click Edit to perform additional data filters and transformations before loading it. The following sections provide steps for using Power Query to connect to your data sources - web pages, text files, databases, online services, and Excel files, tables, and ranges. Make sure you have downloaded, installed, and activated the Power Query Add-In.
For Use alternate credenitals authentication, enter your Username and Password. Power Query is not available in Excel However, you can still connect to external data sources. Step 1: Create a connection with another workbook. Near the bottom of the Existing Connections dialog box, click Browse for More. In the Select Table dialog box, select a table worksheet , and click OK. You can rename a table by clicking on the Properties button.
You can also add a description. Click Existing Connections , choose the table, and click Open. In the Import Data dialog box, choose where to put the data in your workbook and whether to view the data as a Table , PivotTable , or PivotChart.
In the Select Data Source dialog box, browse to the Access database. In the Select Table dialog box, select the tables or queries you want to use, and click OK. You can click Finish , or click Next to change details for the connection. In the Import Data dialog box, choose where to put the data in your workbook and whether to view the data as a table, PivotTable report, or PivotChart. Click the Properties button to set advanced properties for the connection, such as options for refreshing the connected data.
Optionally, you can add the data to the Data Model so that you can combine your data with other tables or data from other sources, create relationships between tables, and do much more than you can with a basic PivotTable report.
Then, in the Import Text File dialog box, double-click the text file that you want to import, and the Text Import Wizard dialog will open.
Original data type If items in the text file are separated by tabs, colons, semicolons, spaces, or other characters, select Delimited. If all of the items in each column are the same length, select Fixed width. Start import at row Type or select a row number to specify the first row of the data that you want to import.
File origin Select the character set that is used in the text file. In most cases, you can leave this setting at its default. If you know that the text file was created by using a different character set than the character set that you are using on your computer, you should change this setting to match that character set. For example, if your computer is set to use character set Cyrillic, Windows , but you know that the file was produced by using character set Western European, Windows , you should set File Origin to Preview of file This box displays the text as it will appear when it is separated into columns on the worksheet.
Delimiters Select the character that separates values in your text file. If the character is not listed, select the Other check box, and then type the character in the box that contains the cursor. These options are not available if your data type is Fixed width. Treat consecutive delimiters as one Select this check box if your data contains a delimiter of more than one character between data fields or if your data contains multiple custom delimiters.
Text qualifier Select the character that encloses values in your text file. When Excel encounters the text qualifier character, all of the text that follows that character and precedes the next occurrence of that character is imported as one value, even if the text contains a delimiter character.
For example, if the delimiter is a comma , and the text qualifier is a quotation mark " , "Dallas, Texas" is imported into one cell as Dallas, Texas. If no character or the apostrophe ' is specified as the text qualifier, "Dallas, Texas" is imported into two adjacent cells as "Dallas and Texas". If the delimiter character occurs between text qualifiers, Excel omits the qualifiers in the imported value. If no delimiter character occurs between text qualifiers, Excel includes the qualifier character in the imported value.
Hence, "Dallas Texas" using the quotation mark text qualifier is imported into one cell as "Dallas Texas". Data preview Review the text in this box to verify that the text will be separated into columns on the worksheet as you want it.
Data preview Set field widths in this section. Click the preview window to set a column break, which is represented by a vertical line.
Double-click a column break to remove it, or drag a column break to move it. Specify the type of decimal and thousands separators that are used in the text file. When the data is imported into Excel, the separators will match those that are specified for your location in Regional and Language Options or Regional Settings Windows Control Panel.
Column data format Click the data format of the column that is selected in the Data preview section. If you do not want to import the selected column, click Do not import column skip. After you select a data format option for the selected column, the column heading under Data preview displays the format. If you select Date , select a date format in the Date box. Choose the data format that closely matches the preview data so that Excel can convert the imported data correctly.
To convert a column of all currency number characters to the Excel Currency format, select General. To convert a column of all number characters to the Excel Text format, select Text.
To convert a column of all date characters, each date in the order of year, month, and day, to the Excel Date format, select Date , and then select the date type of YMD in the Date box. Excel will import the column as General if the conversion could yield unintended results.
If the column contains a mix of formats, such as alphabetical and numeric characters, Excel converts the column to General. If, in a column of dates, each date is in the order of year, month, and date, and you select Date along with a date type of MDY , Excel converts the column to General format.
A column that contains date characters must closely match an Excel built-in date or custom date formats. If Excel does not convert a column to the format that you want, you can convert the data after you import it. Convert numbers stored as text to numbers.
Convert dates stored as text to dates. TEXT function. VALUE function. When you have selected the options you want, click Finish to open the Import Data dialog and choose where to place your data. Set these options to control how the data import process runs, including what data connection properties to use and what file and range to populate with the imported data.
The options under Select how you want to view this data in your workbook are only available if you have a Data Model prepared and select the option to add this import to that model see the third item in this list. If you choose Existing Worksheet , click a cell in the sheet to place the first cell of imported data, or click and drag to select a range. If you have a Data Model in place, click Add this data to the Data Model to include this import in the model.
For more information, see Create a Data Model in Excel. Note that selecting this option unlocks the options under Select how you want to view this data in your workbook. Click Properties to set any External Data Range properties you want. For more information, see Manage external data ranges and their properties.
In the New Web Query dialog box, enter the address of the web page you want to query in the Address box, and then click Go. In the web page, click the little yellow box with a red arrow next to each table you want to query.
None The web data will be imported as plain text. No formatting will be imported, and only link text will be imported from any hyperlinks. Rich text formatting only The web data will be imported as rich text, but only link text will be imported from any hyperlinks.
This option only applies if the preceding option is selected. If this option is selected, delimiters that don't have any text between them will be considered one delimiter during the import process. If not selected, the data is imported in blocks of contiguous rows so that header rows will be recognized as such. If selected, dates are imported as text. SQL Server is a full-featured, relational database program that is designed for enterprise-wide data solutions that require optimum performance, availability, scalability, and security.
Strong password: Y6dh! Weak password: house1. Passwords should be 8 or more characters in length. Under Select the database that contains the data you want , select a database. Under Connect to a specific table , select a specific table or view. Alternatively, you can clear the Connect to a specific table check box, so that other users who use this connection file will be prompted for the list of tables and views. Optionally, in the File Name box, revise the suggested file name.
Click Browse to change the default file location My Data Sources. Optionally, type a description of the file, a friendly name, and common search words in the Description , Friendly Name , and Search Keywords boxes. To ensure that the connection file is always used when the data is updated, click the Always attempt to use this file to refresh this data check box. This check box ensures that updates to the connection file will always be used by all workbooks that use that connection file.
To specify how the external data source of a PivotTable report is accessed if the workbook is saved to Excel Services and is opened by using Excel Services, click Authentication Settings , and then select one of the following options to log on to the data source:.
Windows Authentication Select this option to use the Windows user name and password of the current user. This is the most secure method, but it can affect performance when many users are connected to the server.
A site administrator can configure a Windows SharePoint Services site to use a Single Sign On database in which a user name and password can be stored. This method can be the most efficient when many users are connected to the server. None Select this option to save the user name and password in the connection file. Security Note: Avoid saving logon information when connecting to data sources.
Note: The authentication setting is used only by Excel Services, and not by Excel.
Comments
Post a Comment