- What To Know About Excel
- Everything About Excel
- Everything About Excel Pdf
- Everything To Know About Excel
In newer versions of Excel, each worksheet contains roughly a million rows and more than 16,000 columns, which necessitates an addressing scheme in order to keep track of where data is located. The horizontal rows are identified by numbers (1, 2, 3) and the vertical columns by letters of the alphabet (A, B, C). Excel Tables are containers for your data. Imagine a house without any closets or cupboards to store your things, it would be chaos! Excel tables are like closets and cupboards for your data, they help to contain and organize data in your spreadsheets. In your house, you might put all your plates into one kitchen cupboard. Excel is a powerful application—but it can also be very intimidating. That's why we've put together this beginner's guide to getting started with Excel. It will take you from the very beginning (opening a spreadsheet), through entering and working with data, and finish with saving and sharing.
In Dynamics 365, there are several ways to use Excel to analyze data. In this post, we will look at the various different options.
Let's start with a view of All Accounts. Note we're displaying this in the Sales Hub app. The logic applied to the web client as well.
In the toolbar, we have several Excel options, including: Seagate backup plus portable thunderbolt.
- Excel Templates
- Export to Excel
- Open in Excel Online
- Static Worksheet
- Static Worksheet (Page only)
- Dynamic Worksheet
- Dynamic PivotTable
Let's look at each of these.
Excel Templates
Excel Templates are used to export data to Excel in a particular pre-defined template. You build your own templates and upload them to Dynamics 365 so you can access the data in your desired format at any given time. Clicking on the down arrow shows the options to Upload Template, i.e. a template you have created and would like to use in Dynamics 365, and Download Template, which will download a blank template for you to use.
To make a basic template, select Download template and select the entity and view for your template:
Open the downloaded xlsx file. The file is basically what you see in your view, with the columns and data:
Now let's say each week I create and send an Excel report to my boss that lists all accounts. But, I need a header in the report that shows it is 'Carl's Weekly Accounts Report'. I can add that title to my template. We will then upload this to Dynamics 365:
Once saved, close the file and then click Upload Template: Audio mixing platform.
Select the file (if you still have it open it will show as blank, so be sure to close it before trying to upload) and press Upload:
You will then see the template under Personal Template Options:
Selecting it allows you to choose to Open in Excel Online or Download It:
Let's first open in Excel Online:
If we make a change to the worksheet, we may get the prompt 'Any changes in Excel will be lost. The changes you make in Excel will be lost unless you submit the changes to Dynamics 365':
Selecting to Download It will download the file in the template format with the latest data:
You can read more about creating templates here.
Export to Excel
This is a fast, one-click way to export data to Excel on your desktop.
As there is no template involved, it will be simply a dump of what is in the view:
Open in Excel Online
As with the option above, Open in Excel Online will open the data in your browser in Excel Online:
You may see the following:
This is probably the fastest way to view data in an Excel format if you wanted to write some quick formulas such as adding fields together, getting counts etc:
Note we can save changes back to Dynamics 365 by clicking on the Save Changes to Dynamics 365 button.Let's try this. Update an address and click Save changes to Dynamics 365:
You will be taken to the window:
To track progress, go to Settings->Data Management->Imports:
You will see the status change from Importing to Completed:
And finally, the data updated:
Static Worksheet
Selecting Static Worksheet will download a worksheet to your computer:
This worksheet will be 'static', meaning the data is not updated:
Static Worksheet (Page Only)
As above,
The difference here is the static worksheet (page only) will only display what is currently selected in the view. Below we have 50 records displayed:
These are downloaded in the Excel file:
Export to Excel Dynamic Worksheet
Let's select Dynamic Worksheet:
Select the columns to export:
The file will download. The first thing you will need to do is
And in IE:
If you continue to have issues, see here.
Open the downloaded file.
Excel will open. You may see the security warning. Click to Enable Content:
You will see in the Data tab, Refresh from Dynamics 365:
Let's make an update to a Dynamics 365 record, e.g. by updating the name of an account:
Now, click to refresh in Excel. You will see the data in the spreadsheet has been updated:
If you continue to have issues, see here.
Export to Excel Dynamic PivotTable
Select Dynamic PivotTable:
Select the columns:
Unblock the file:
Click Enable Editing:
You may see: 'Script is disabled. Click Submit to continue':
Click Refresh All in the Data tab. The data will be displayed:
Scriptis disabled. Click Submit to continue.
https://www.youtube.com/carldesouzaABOUT CARL DE SOUZA
Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, Power BI, Azure, and AI.
carldesouza.com | LinkedIn | Twitter | YouTubeRelated Posts:
- Power BI - Analyze in Excel
- Updating Dynamics 365 Records using Export and…
- Import Excel Data into a Model Driven PowerApp using…
The past 10 years have seen a wave of innovative big data software designed to analyze, manipulate, and visualize data. Yet for the regular knowledge worker, Microsoft Excel, 30 years on, remains the go-to product for people looking to make sense of data. Satya Nadella, Microsoft's CEO, maintains that Excel is still the one Microsoft product that stands above the rest — and 750 million knowledge workers worldwide support that claim every day.
We've been teaching and testing Microsoft Excel for a decade, and a survey of several hundred office staff we ran suggests we spend more than 10% of our working lives spreadsheeting, and for those working in research and development or finance, it's more like 30%, or 2.5 hours a day.
Imagine, then, if this substantial proportion of the global workforce were a little better at using the application. Time would be saved, and productivity would improve.
Insight Center
- Scaling Your Team's Data Skills Sponsored by Splunk
Last year we put together The Definitive 100 Most Useful Excel Tips, for which we consulted Excel experts and looked at tens of thousands of test results and course usage data. Though every Excel feature has a use case, no single person uses every Excel feature themselves. Cut through the 500+ functions, and you're left with 100 or so truly useful functions and features for the majority of modern knowledge workers. We've selected ten from that list which are especially easy to learn (approximately 2 hours total) and which can make a material difference to productivity. These ten are listed in decreasing order of utility from our top 100 list. If, like many, you're stuck on what to learn next in Excel, you might want to look at this 2×2 matrix, which factors in usefulness and time needed to learn a skill.
Paste Special (10 minutes to learn)
Copy and paste is one of the simplest and most used functions in Excel. But we often carry over a format we don't want, or we copy a formula over, when instead we just want a value.
These little frustrations can take time to fix, which is why Paste Special is so… special. Paste Special enables you to pick which elements of the copied cell you bring over. After you've copied your cell (Ctr+C) hit Ctrl+Alt+V (or go to the Clipboard section of the Home ribbon, or Edit > Paste Special) to bring up Paste Special and make your selection. Alt+E+S+V is the shortcut to just paste values — probably the most common use of Paste Special.
Add Multiple Rows (2 minutes to learn)
We often need to add new rows between existing rows. The shortcut (Ctrl, shift, +) is pretty handy, especially as you can toggle the + to add multiple rows. Often, just highlighting the number of rows you want to add (say 5) and using right click, insert is quicker when adding in bulk as it will add the number of rows you've highlighted.
Flash Fill (30 minutes to learn)
Excel developed a mind of its own in 2013 with this feature. Flash Fill automatically fills your data when it senses a pattern.
Suppose you have a list of product numbers in the first ten cells of column A, e.g. ‘ABC-00001' to ‘ABC-00010' and you only need the numbers after the ‘-'. You can easily discard the ‘ABC' using Flash Fill. Pre-2013 this was possible, but relied on a combination of functions (FIND, LEFT, &, etc). Now, this is much faster and will impress people.
Establish the pattern by typing ‘00001' into the first blank cell. If Flash Fill is turned on (File Options, Advanced) just start to type the next product number in the cell below and Flash Fill will recognize the pattern and fill down the remaining product numbers for you. Just hit the Enter key to accept. Or, get it going manually by clicking Data > Flash Fill, or Ctrl+E.
Flash Fill is like magic, and can be used in many different scenarios. It's a fantastic time saver when you need to input or change a lot of data quickly and accurately. Flash Fill is a jaw-dropping feature.
INDEX-MATCH (45 minutes to learn)
Aside from VLOOKUP (which looks up the value in one column and returns a corresponding value from another column), INDEX and MATCH are the most widely used and most powerful tools in Excel for performing lookups. Used separately, these functions are invaluable, but it's when you combine them that their true power is unleashed. INDEX and MATCH used in combination help you extract the data you need from a large dataset efficiently and precisely. Mastering these functions will not only make you look like an Excel whiz to your colleagues and manager, but can make a tedious, mundane task quick and simple. Here's how these functions work:
VLOOKUP is a great function, but it has its limitations. It can only look up values from left to right. The lookup value must be on the left in the lookup table. INDEX and MATCH allows you to look up a value anywhere in the lookup table regardless of its position.
Let's say you have a spreadsheet with a list of products. You have columns titled 'Product Number', 'Profit', 'Product Name', and 'Revenue'. On another spreadsheet, you have a list of the product names and you want to look up how much profit each product has generated. In this scenario, we are using the product name (our lookup value) to look up the profit. The product name sits to the right of the profit and so VLOOKUP would not work. This is the perfect scenario for INDEX and MATCH.
The syntax would be:
=INDEX(Profit column,MATCH(Lookup Value,Product Name column,0))
Here's a good way to remember how it works:
=INDEX (Column I want a return value from, MATCH (My Lookup Value, Column I want to Lookup against, Enter '0' )) (Zero gives you an exact match, you can match against less than (-1) or greater than (1) as well.)
At first and even second glance, INDEX and MATCH looks complex. It certainly needs some practice, but it's worth getting your head around as it is more flexible and more powerful than a VLOOKUP. It's one of the most important uses of Excel, period.
SUM (2 minutes to learn)
This is one of the first functions you're likely to learn in Excel — how to sum a row or column. But did you know you can select the cell at the end of a row or column and press Alt + to do this functions in seconds?
What To Know About Excel
CTRL Z / CTRL Y (1 minutes to learn)
This is your Excel get-out-of-jail-free card. If you aren't using Ctrl Z to undo mistakes in Excel, then you should be. What many people don't know is that Ctrl Y does the opposite — redo. The two work in tandem very nicely, and you cycle through iterations of your work until you find the right one.
Remove Duplicates (10 minutes to learn)
This is so simple and quick to use. Remove Duplicates does exactly what you'd expect — it removes the duplicates in any given range of data. Our advice is to remove the values you want to dedupe and place them in another sheet. It's found on the Data tab in the Data Tools section of the Ribbon.
If you just want to highlight duplicates, you can do this using Conditional Formatting. The shortcut to get you there is Alt H L. (Or find it on the Home ribbon under Styles).
Everything About Excel
Freeze Panes (15 minutes to learn)
Ever scroll down a large table of data only to forget which columns are which? Freeze Panes is the answer here. You can freeze just the top row, first column or any number of either. Identify the columns and rows of the area you want to freeze. Then select the cell immediately to the right of those columns and beneath those rows. Go to the View tab and Freeze Panes in the Window section. Alt W F is the shortcut.
F4 (10 minutes to learn)
There are two especially satisfying ways to use F4 in Excel. The first is when creating an Absolute Reference: F4 toggles you through the various options. The second is one that few people know about, but could seriously improve your Excel productivity. F4 repeats your last action, where available. For example, if you've just applied a border to one cell, use F4 to apply it to others.
CTRL + Arrows (5 minutes to learn)
If you've found yourself scrolling through a dataset to reach the bottom of it, stop right now and start using Ctrl + the arrow keys! This simple shortcut takes you straight to the end of the data in a column or row you are using. Combine it with Ctrl, Shift to highlight/select large areas of data in seconds.
Everything About Excel Pdf
Warning: If you have gaps in your data this will just take you down to the first gap. So if you want to get to the bottom fast, choose a column of data which has no gaps.
Everything To Know About Excel
Harness just a few of these ten items, and you can transform your typical work day. Whether you want to help justify data-driven business decisions at a high level, or simply get home to your family earlier, mastering the right Excel functions is a quick and easy way to maximize your productivity.
