Looking for:
2013 microsoft excel tutorial free |
Microsoft Excel Tutorial — Free and Online - The sections in this tutorial are the following:
For more information about advanced connector options, see XML. The Import Data dialog box appears. For more information about advanced connector options, see JSON.
NET Framework 4. You can download the latest. NET Framework from here. Select your PDF file, and then click Open. The Navigator dialog box opens your PDF and displays available tables. For more information about advanced connector options, see PDF. You can import data from several files having a similar schema and format from a folder.
Then, you can append the data into one table. In the Browse dialog box, locate the folder, and then select Open. For detailed steps, see Import data from a folder with multiple files. For more information about advanced connector options, see Folder. You can import data from several files having a similar schema and format from a SharePoint library.
In the SharePoint Folder dialog box, enter the root URL for the SharePoint site not including any reference to a library, and then navigate to the library. For more information about advanced connector options, see SharePoint folder. Optionally, you can specify a Database Name as well.
If you want to import data using a native database query, specify your query in the SQL Statement box. Windows This is the default selection.
Select this if you want to connect using Windows authentication. After you select this, specify a user name and password to connect to your SQL Server instance.
By default, the Encrypt connection check box is selected to signify that Power Query connects to your database using an encrypted connection. If you do not want to connect using an encrypted connection, clear this check box, and then click Connect. If a connection to your SQL Server is not established using an encrypted connection, Power Query prompts you to connect using an unencrypted connection.
Click OK in the message to connect using an unencrypted connection. For more information about advanced connector options, see SQL Server database.
In the Import Data dialog box, browse for and locate the Access database file. Select the file, and then select Open. The Navigator dialog box appears. If you have many tables and queries, use the Search box to locate an object or use the Display Options along with the Refresh button to filter the list. For more information about advanced connector options, see Access database. Note When you use a workbook connected to a SQL Server Analysis Services database, you may need additional information to answer specific product questions, such as reference information about multidimensional expressions MDX , or configuration procedures for an online analytical processing OLAP server.
The first page of the Data Connection Wizard appears. Its title is Connect to Database Server. Tip: If you know the name of the offline cube file that you want to connect to, you can type the complete file path, file name, and extension. Under Log on credentials , do one of the following, then click Next :. To use your current Windows user name and password, click Use Windows Authentication.
To enter a database user name and password, click Use the following User Name and Password , and then type your user name and password in the corresponding User Name and Password boxes. Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements.
For example, Y6dh! Passwords should contain 8 or more characters. A pass phrase that uses 14 or more characters is better.
It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect. Select Next to go to the second wizard screen. Its title is Select Database and Table. To connect to a specific cube file in the database, make sure that Connect to a specific cube or table is selected, and then select a cube from the list.
In the Select the database that contains the data you want box, select a database, and then click Next. Click Next to go to the third wizard screen. Click Browse to change the default file location of My Data Sources , or check for existing file names. In the Description , Friendly Name , and Search Keywords boxes, type a description of the file, a friendly name, and common search words all are optional.
To ensure that the connection file is used when the PivotTable is refreshed, click Always attempt to use this file to refresh this data.
Selecting this check box ensures that updates to the connection file will always be used by all workbooks that use that connection file. You can specify how a PivotTable is accessed if the workbook is saved to Excel Services and is opened by using Excel Services. If you want to ensure that the same data is accessed whether you open the workbook in Excel or Excel Services, make sure that the authentication setting in Excel is the same. Select Authentication Settings , and select one of the following options to log on to the data source:.
Windows Authentication Select this option to use the Windows username and password of the current user. This is the most secure method, but it can affect performance when there are many users. A site administrator can configure a SharePoint site to use a Single Sign On database where a username and password can be stored.
This method can be the most efficient when there are many users. None Select this option to save the username and password in the connection file. Important: Avoid saving logon information when connecting to data sources.
This information may be stored as plain text, and a malicious user could access the information to compromise the security of the data source. Select Finish to close the Data Connection Wizard. Decide how you want to import the data, and then select OK. For more information about using this dialog box, select the question mark? You can connect to a specific offline cube file if it has been created on the database server.
You can also import data into Excel as either a Table or a PivotTable report. In the Navigator pane select the database, and then select the cube or tables you want to connect. Click Load to load the selected table into a worksheet, or click Edit to perform additional data filters and transformations in the Power Query Editor before loading it. Note: Before you can connect to an Oracle database using Power Query , you need the Oracle client software v8. If you want to import data using native database query, specify your query in the SQL Statement box.
For more information, see Import data from database using Native Database Query. For more information about advanced connector options, see Oracle Database.
Select the driver that matches your Power Query installation bit or bit. For more information, see Import data from a database using Native Database Query.
For more information about advanced connector options, see MySQL database. Select the driver that matches your Office version bit or bit.
For more information, see Which version of Office am I using? Also make sure you have the provider registered in the machine configuration that matches the most recent. NET version on your device. For more information about advanced connector options, see PostgreSQL. Select the driver that matches your Excel installation bit or bit. By default, the Encrypt connection check box is selected so that Power Query connects to your database using a simple encrypted connection.
Note: Before you can connect to a Teradata database, you need the. This feature is only available in Excel for Windows if you have Office or later, or a Microsoft subscription. If you are a Microsoft subscriber, make sure you have the latest version of Office.
You will need an SAP account to login to the website and download the drivers. If you are unsure, contact the SAP administrator in your organization.
The server name should follow the format ServerName:Port. Optionally, if you want to import data using native database query, Select Advanced options and in the SQL Statement box enter the query.
Azure SQL Database is a high-performing, fully managed, scalable relational database built for the cloud and used for mission-critical applications. For more information about advanced connector options, see Azure SQL database.
Azure Synapse Analytics combines big data tools and relational queries by using Apache Spark to connect to Azure data services and the Power Platform. You can load millions of rows in no time. Then, you can work with tabular data by using familiar SQL syntax for queries.
For more information, see What is Azure Synapse Analytics. For more information about advanced connector options, see Azure Synapse Analytics. Azure HDInsight is used for big data analysis when you need to process large amounts of data. It supports data warehousing and machine learning; you can think of it as a data flow engine. Select your cluster in the Navigator dialog, and then find and select a content file. Select Load to load the selected table, or Edit to perform additional data filters and transformations before loading it.
If you are connecting to the Blob storage service for the first time, you will be prompted to enter and save the storage access key. Note: If you need to retrieve your storage access key, browse to the Microsoft Azure Portal , select your storage account, and then select the Manage Access Key icon on the bottom of the page. Select the copy icon to the right of the primary key, and then paste the value in the Account Key box. The Azure Storage provides storage services for a variety of data objects.
For more information, see Introduction to Table storage. Azure Data Lake Storage Gen 1 combines different data warehouses into a single, stored environment.
You can use a new generation of query tools to explore and analyze data, working with petabytes of data. For more information, see Azure Data Lake Storage.
Azure Data Explorer is a fast and highly scalable data exploration service for log and telemetry data. It can handle large volumes of diverse data from any data source, such as websites, applications, IoT devices, and more. For more information, see What is Azure Data Explorer. In the Azure Data Explorer Kusto dialog box, enter appropriate values.
Each prompt provides helpful examples to walk you though the process. You can import Datasets from your organization with appropriate permission by selecting them from the Power BI Datasets pane, and then creating a PivotTable in a new worksheet. The Power BI Datasets pane appears. If many Datasets are available, use the Search box. Select the arrow next to the box to display keyword filters for versions and environments to target your search. Select a Dataset and create a PivotTable in a new worksheet.
Select the 2. As an alternative to 2. For more information about advanced connector options, see SharePoint Online list. If you have many objects, use the Search box to locate an object or use he Display Options along with the Refresh button to filter the list. Select or clear the Skip files with errors checkbox at the bottom of the dialog box.
If you select the Advanced option, you can append certain additional parameters to the query to control what data is returned.
If you aren't signed in using the Microsoft Work or School account you use to access Dataverse for Apps, select Sign in and enter the account username and password. The Salesforce Objects dialog box appears. Select either Production or Custom. If you select Custom , enter the URL of a custom instance. For more information about advanced connector options, see Salesforce Objects. Because Salesforce Reports has API limits retrieving only the first 2, rows for each report, consider using the Salesforce Objects connector to work around this limitation if needed.
The Salesforce Reports dialog box appears. For more information about advanced connector options, see Salesforce Reports. Make sure you have the latest version of the Adobe Analytics connector.
Sign in with you Adobe Analytics Organizational account, and then select Connect. For more information about advanced connector options, see Adobe Analytics. Select Advanced , and then In the Access Web dialog box, enter your credentials.
For more information about advanced connector options, see Web. Microsoft Query has been around a long time and is still popular.
In many ways, it's a progenitor of Power Query. For more information, see Use Microsoft Query to retrieve external data. By default, the most general URL is selected.
Select Anonymous if the SharePoint Server does not require any credentials. Select Organizational account if the SharePoint Server requires organizational account credentials. For more information about advanced connector options, see SharePoint list. Select Marketplace key if the OData feed requires a Marketplace account key. Click Organizational account if the OData feed requires federated access credentials.
For Windows Live ID, log into your account. For more information about advanced connector options, see OData feed. HDFS connects computer nodes within clusters over which data files are distributed and you can access these data files as one seamless file stream.
Enter the name of the server in the Server box, and then select OK. In the Active Directory Domain dialog box for your domain, select Use my current credentials , or select Use alternate credentials and then enter your Username and Password.
After the connection succeeds, use the Navigator pane to browse all the domains available within your Active Directory, and then drill down into Active Directory information including Users, Accounts, and Computers. In the next dialog box, select from Default or Custom , Windows , or Database connection options, enter your credentials, and then select Connect.
In the Navigator pane, select the tables or queries that you want to connect to, then select Load or Edit. For more information about advanced connector options, see ODBC data source. In the Navigator dialog box, select the database, and tables or queries you want to connect to, and then select Load or Edit. Important: Retirement of Facebook data connector notice Import and refresh data from Facebook in Excel will stop working in April, Note: If this is the first time you've connected to Facebook, you will be asked to provide credentials.
Sign in using your Facebook account, and allow access to the Power Query application. You can turn off future prompts by clicking the Don't warn me again for this connector option. Note: Your Facebook username is different from your login email. Select a category to connect to from the Connection drop-down list. For example, select Friends to give you access to all information available in your Facebook Friends category.
If necessary, click Sign in from the Access Facebook dialog, then enter your Facebook email or phone number, and password. You can check the option to remain logged in. Once signed in, click Connect. After the connection succeeds, you will be able to preview a table containing information about the selected category.
For instance, if you select the Friends category, Power Query renders a table containing your Facebook friends by name. You can create a blank query. You might want to enter data to try out some commands, or you can select the source data from Power Query:.
For more information, see Manage data source settings and permissions. This command is similar to the Get Data command on the Data tab of the Excel ribbon. This command is similar to the Recent Sources command on the Data tab of the Excel ribbon.
When you merge two external data sources, you join two queries that create a relationship between two tables. When you append two or more queries, the data is added to a query based on the names of the column headers in both tables. The queries are appended in the order in which they're selected.
For more information, see Append queries Power Query. You can use the Power Query add-in to connect to external data sources and perform advanced data analyses. The following sections provide steps for connecting to your data sources - web pages, text files, databases, online services, and Excel files, tables, and ranges. Click the Power Query check box, then OK.
The Power Query ribbon should appear automatically, but if it doesn't, close and restart Excel. The following video shows the Query Editor window appearing after editing a query from an Excel workbook.
The following video shows one way to display the Query Editor. These automatic actions are equivalent to manually promoting a row and manually changing each column type. For example:. The following video shows the Query Editor window in Excel appearing after editing a query from an Excel workbook. If prompted, in the From Table dialog box, you can click the Range Selection button to select a specific range to use as a data source.
If the range of data has column headers, you can check My table has headers. The range header cells are used to set the column names for the query.
Note: If your data range has been defined as a named range, or is in an Excel table, then Power Query will automatically sense the entire range and load it into the Query Editor for you. Plain data will automatically be converted to a table when it is loaded into the Query Editor. You can use the Query Editor to write formulas for Power Query.
You can also use the Query Editor to write formulas for Power Query. Note: While trying to import data from a legacy Excel file or an Access database in certain setups, you may encounter an error that the Microsoft Access Database Engine Microsoft. The error occurs on systems with only Office installed. To resolve this error, download the following resources to ensure that you can proceed with the data sources you are trying to access.
Microsoft Access Database Engine Redistributable. Access Database Engine Service Pack 1. In the Access Web dialog box, click a credentials option, and provide authentication values.
Power Query will analyze the web page, and load the Navigator pane in Table View. If you know which table you want to connect to, then click it from the list.
For this example, we chose the Results table. Otherwise, you can switch to the Web View and pick the appropriate table manually. In this case, we've selected the Results table. Click Load , and Power Query will load the web data you selected into Excel. Windows : This is the default selection. In the next dialog box, select from Default or Custom , Windows , or Database connection options, enter your credentials, then press Connect. In the Navigator pane, select the tables or queries that you want to connect to, then press Load or Edit.
In the Browse dialog box, browse for or type a file URL to import or link to a file. Follow the steps in the Navigator dialog to connect to the table or query of your choice. After the connection succeeds, you will be able to use the Navigator pane to browse and preview the collections of items in the XML file in a tabular form.
Save Data Connection File and Finish. In the Select the database that contains the data you want pane, select a database, then click Next. To connect to a specific cube in the database, make sure that Connect to a specific cube or table is selected, and then select a cube from the list. In the Import Data dialog box, under Select how you want to view this data in your workbook , do one of the following:. To store the selected connection in the workbook for later use, click Only Create Connection.
This check box ensures that the connection is used by formulas that contain Cube functions that you create and that you don't want to create a PivotTable report. To place the PivotTable report in an existing worksheet, select Existing worksheet , and then type the cell reference of the first cell in the range of cells where you want to locate the PivotTable report. You can also click Collapse Dialog to temporarily hide the dialog box, select the beginning cell on the worksheet that you want to use, and then press Expand Dialog.
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. Feel free to explore by dragging fields into different areas of the PivotTable; the underlying data is not affected when you arrange fields in a PivotTable. In PivotTable Fields , expand the Medals table by clicking the arrow beside it. NOC stands for National Olympic Committees, which is the organizational unit for a country or region.
In the PivotTable Fields list, where the Disciplines table is expanded, hover over its Discipline field and a dropdown arrow appears to the right of the field. Click OK. Type 90 in the last field on the right. With little effort, you now have a basic PivotTable that includes fields from three different tables. What made this task so simple were the pre-existing relationships among the tables. Because table relationships existed in the source database, and because you imported all the tables in a single operation, Excel could recreate those table relationships in its Data Model.
But what if your data originates from different sources, or is imported at a later time? Typically, you can create relationships with new data based on matching columns. In the next step, you import additional tables, and learn how to create new relationships. Relationships let you analyze collections of data in Excel, and create interesting and immersive visualizations from the data you import. Browse to the folder that contains the downloaded sample data files, and open OlympicSports.
Select and copy the data in Sheet1. Close the OlympicSports. On the Sports worksheet, place your cursor in cell A1 and paste the data. Since the data has headers, select My table has headers in the Create Table window that appears, as shown here. Formatting the data as a table has many advantages. You can assign a name to a table, which makes it easy to identify.
You can also establish relationships between tables, enabling exploration and analysis in PivotTables, Power Pivot, and Power View. Name the table. The workbook looks like the following screen. In the following steps, you add the Olympic host cities from a table. In Excel, place your cursor in cell A1 of the Hosts worksheet and paste the data. Format the data as a table. Since the data has headers, select My table has headers in the Create Table window that appears.
Now that you have an Excel workbook with tables, you can create relationships between them. Creating relationships between tables lets you mash up the data from the two tables. You can immediately begin using fields in your PivotTable from the imported tables. In the following steps, you learn how to create a relationship between data you imported from different sources.
On Sheet1 , at the top of PivotTable Fields , click All to view the complete list of available tables, as shown in the following screen.
Expand Sports and select Sport to add it to the PivotTable. Notice that Excel prompts you to create a relationship, as seen in the following screen. To create the relationship, one of the tables must have a column of unique, non-repeated, values. In the sample data, the Disciplines table imported from the database contains a field with sports codes, called SportID.
Those same sports codes are present as a field in the Excel data we imported. The PivotTable changes to reflect the new relationship. The following screen shows this unwanted ordering. Table relationships are the basis of a Data Model, and what determine navigation and calculation paths. You also learn how to calculate columns in a table, and use that calculated column so that an otherwise unrelated table can be added to your Data Model. You now have an Excel workbook that includes a PivotTable accessing data in multiple tables, several of which you imported separately.
You learned to import from a database, from another Excel workbook, and from copying data and pasting it into Excel. To make the data work together, you had to create a table relationship that Excel used to correlate the rows. You also learned that having columns in one table that correlate to data in another table is essential for creating relationships, and for looking up related rows. Want to see how well you remember what you learned? The following quiz highlights features, capabilities, or requirements you learned about in this tutorial.
Good luck! Question 1: Why is it important to convert imported data into tables? B: If you convert imported data into tables, they will be excluded from the Data Model. Question 2: Which of the following data sources can you import into Excel, and include in the Data Model?
C: Anything you can copy and paste into Excel and format as a table, including data tables in websites, documents, or anything else that can be pasted into Excel. C: The PivotTable format is changed to reflect the layout, and all underlying data is permanently changed. Question 4: When creating a relationship between tables, what is required? Notes: Data and images in this tutorial series are based on the following:.
Import and analyze data. Data models. Flag images from CIA Factbook cia. Need more help?
2013 microsoft excel tutorial free.Download Microsoft Power Query for Excel from Official Microsoft Download Center
Free Excel Tutorial at GCFGlobal.
Check out the training for newer versions of Excel. Start using Excel. Create a chart. ,icrosoft numbers in Excel Basic math 2013 microsoft excel tutorial free Excel Top tips for working in Excel Online. Understand and use cell references. Use AutoFill 2013 microsoft excel tutorial free Flash Fill. Add or subtract time. Average a group of numbers. Insert headers and footers. Make the switch to Excel Как сообщается здесь and filter data.
Take conditional formatting to the next level. Use conditional formatting. Advanced IF functions. Array formulas. Create and manage drop-down lists. Create a PivotTable and analyze your data. Password protect workbooks and worksheets. Print worksheets and workbooks. Work with macros. Check out Microsoft Training Center.
Excel More Need more help? Expand согласен youtube tutorial microsoft project 2010 free согласен skills. Get new features first. Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell tjtorial the more we can help. Can you help us improve?
Resolved my issue. Clear instructions. Easy to follow. No jargon. Pictures helped. Didn't match my screen. Incorrect instructions. Too technical. Not enough information. Not enough pictures. Any additional feedback? Submit feedback. Thank you for your feedback!
Comments
Post a Comment