The Top 10 Excel Features
O365 Microsoft Word could not create the work file. Check the temp environment variable and Preview Pane word and excel not show. Before i use office 2016 it show normally no problem. Microsoft Excel provides a grid interface to organize nearly any type of information. The power of Excel lies in it's flexibility to define the layout and structure of the information you want to manage.
Every Excel user has a favourite tip. So we decided to bring some method, data, and science to bear in this hotly debated topic, and to come up with the Top 10 Excel Features.
We consulted the research of 30 of the world's leading Excel experts as well as our own internal Excel experts. We then grouped up and consolidated the emerging features, and applied a scoring methodology and index to produce a definitive ordered list of the 100 most useful Excel features, hacks, tips and tricks.
These are the top 10 Excel features as determined by our ranking.
Skills are ranked by usefulness and given a score out of 100. Each skill also has a difficulty rating (out of 5) based on the complexity and sophistication of the feature. Finally, we provide the average time in minutes it takes the average learner to go from no knowledge to proficient.
1. Conditional Formatting
Utility: 100 | Difficulty: 3 | Learn in 180 mins
Making sense of our data-rich, noisy world is hard but vital. That's why the first of our Top 10 Excel Features is so important. Used well, Conditional Formatting brings out the patterns of the universe, as captured by your spreadsheet. That's why Excel experts and Excel users alike vote this the #1 most important feature. This can be sophisticated. But even the most straightforward colour changes can be hugely beneficial. Suppose you have volumes sold by sales staff each month. Just three clicks can reveal the top 10% performing salespeople and tee up a meaningful business conversation.
2. PivotTables
Utility: 95 | Difficulty: 3 | Learn in 240 mins
At four hours to get to proficiency, you may be put off learning PivotTables, but don't be. Use them to sort, count, total or average data stored in one large spreadsheet and display them in a new table, cut however you want. That's the critical thing here. Undertale xbox one. If you want to look only at sales figures for specific countries, product lines, or marketing channels, it's trivial. Warning: make sure your data is clean first!
3. Paste Special
Utility: 88 | Difficulty: 3 | Learn in 10 mins
Grabbing (i.e. Copying) some data from one cell and pasting it into another cell is one of the most common activities in Excel. But there's a lot you might copy (formatting, value, formula, comments, etc.) and sometimes you won't want to copy all of it. The most common example of this is where you want to lose the formatting – the place this data is going is your own spreadsheet with your own styling.
4. Add Multiple Rows
Utility: 87 | Difficulty: 0 | Learn in 10 mins
Probably one of the most frequently carried out activities in spreadsheeting. Ctrl-Shift + is the shortcut, but actually, it takes longer, so Right Click is what we recommend. If you want to add more than one, select as many rows or columns as you'd like to add and then Right Click and add.
5. Absolute References
Utility: 85 | Difficulty: 2 | Learn in 15 mins
Indispensable! The dollar in front of the letter fixes the column, the dollar sign in front of number fixes the row F4 toggles through the four possible combinations.
6. Print Optimisation
Utility: 84 | Difficulty: 3 | Learn in 120 mins
Everyone has problems printing from Excel. But just imagine if what you printed were always just what you intended to print. It IS actually possible. There are a few components to this though: print preview, fit to one page, adjusting margins, print selection, printing headers, portrait vs landscape and spreadsheet design. Invest the time to get comfortable with it. You'll be carrying out this task many, many times in your working life.
7. Extend formula across/down
Utility: 84 | Difficulty: 1 | Learn in 5 mins
The beauty of Excel is its easy scalability. Get the formula right once and Excel will churn out the right calculation a million times. The + crosshair is handy. Double clicking it will take it all the way down if you have continuous data. Sometimes a copy and paste (either regular paste or paste formulas) will be faster for you.
8. Flash Fill
Utility: 84 | Difficulty: 2 | Learn in 30 mins
Excel developed a mind of its own in 2013. Say you have two columns of names and you need to construct email addresses from them all. Just do it for the first row and Excel will work out what you mean and do it for the rest. Pre-2013 this was possible but relied on a combination of functions (FIND, LEFT, &, etc). This is much faster and WILL impress people.
9. INDEX-MATCH
Utility: 82 | Difficulty: 4 | Learn in 45 mins
This is one of the most powerful combinations of Excel functions. You can use it to look up a value in a big table of data and return a corresponding value in that table. Let's say your company has 10,000 employees and there's a spreadsheet with all of them in it with lots of information about them like salary, start date, line manager, etc. But you have a team of 20 and you're only really interested in them. INDEX-MATCH will look up the value of your team members (these need to be unique like email or employee number) in that table and return the desired information for your team. This is worth getting your head around this as it is more flexible and therefore more powerful than VLOOKUPs.
10. Filters
Utility: 81 | Difficulty: 2 | Learn in 60 mins
Explore data in a table quickly. Filtering effectively hides data that is not of interest. Usually, there's a value e.g. ‘Blue cars' that you're looking for and Filters will bring up those and hide the rest. But in more modern versions of Excel, you can now also filter on number values (e.g. is greater than, top 10%, etc), and cell color. Filtering becomes more powerful when you need to filter more than one column in combination e.g. both colors and vehicles to find your blue car.
This article was originally published on Business Insider.
Getting data in and out of the Common Data Service for Apps is a fundamental task when building apps and flows, we're happy to say the Import, Export and Open in Excel features are now available for the latest update of the Common Data Service for Apps. These features have been released in addition to the PowerQuery functionality already available in environments today. Working with data can take may forms, hence why we have multiple methods for importing, exporting and manipulating data within CDS for Apps. This blog will focus on the importing and exporting data from Excel and CSV files, and interacting with data using the Excel Add-in.
Before we do, if you'd more information on the existing tools:
- PowerQuery is used to get data and transform it from a number of cloud services, and create entities as part of a single step while importing data. Find more information here.
- Data Integrator is used setup reoccurring data integrations between systems, typically managed by an admin to integrate data from other systems of records intoout of the Common Data Service for Apps.
Excel Add-in
The PowerApps add-in for Microsoft Excel allows you to open an entity in an Excel worksheet to view and edit your data. The Excel add-in allows you to refresh your data, create new records, delete and edit existing records all from within Excel. You can read more about the Excel add-in here.
To get started, go to web.powerapps.com and select the environment you'd like to use (If you don't already have an environment, follow the steps here. You can open the Excel Add-in by first opening your list of entities (Data > Entities) and selecting an entity from the list, from there you'll see the Open in Excel appear on the command bar.
Clicking this will generate a new Excel file and prompt you to download it, you can download the file onto your local drive, or OneDrive. Once you've downloaded and opened the file you'll be prompted to enable add-ins, don't worry at this point if the file contains no data – it needs to connect to your environment to refresh the data first.
Once the workbook has loaded, you're now connected to your CDS for Apps environment and ready to start making updates. You can find a detailed guide here. Here are some quick tips to keep in mind:
- The Excel add-in connects to CDS using your credentials, the same permissions that would apply if you were using an app or flow, would apply here. If you don't have permissions to access an entity, you'll need to get access from your admin before using the add-in.
- You need to click 'Publish' to have your changes published back to CDS, if you don't click publish, they will not be reflected in other apps.
- Lookups are presented in the Excel workbook as two columns – the Display name, which is the friendly field you typically use to enter the name or primary identifier and the GUID, which is the system generated unique ID. The Excel add-in helps you update your data by presenting you a list of lookup values whenever you select a lookup field (either the Display or the GUID) but keep in mind – that as the Display isn't always unique, you should avoid copy and pasting into this field as we won't be able to match it against the record you're trying to enter. If you need to copy and paste multiple lookup values, you should use the GUID field.
- The workbook will refresh by default when you open it – so make sure you publish your changes regularly, and before closing the workbook.
For more information on the Excel Add-in, you can find a detailed guide here.
Importing and Exporting Excel and CSV files
We are also reintroducing the ability to bulk-import and export data from Excel or CSV files by using the Get Data from Excel file and Export Data features for updated Common Data Service (CDS) for Apps environments. With this feature, you can move data into single or multiple entities from Excel or CSV files. Alternatively, you can export data to a CSV file from single or multiple entities. Import and Export from Excel is accessible from the command bar by going to Get Data > Get Data from Excel, or Export > Data.
Microsoft Excel Environment And Its Parts
The import and export from Excel is a great option for importing and exporting data when:
- You're working with a file on your local desktop, not on OneDrive for Business or other cloud location.
- You need to import or export multiple files at the same time.
- Your entities contain relationships to other entities and you want to use GUID or your primary keys to set the lookup (foreign key) on import.
- You need to export your data to Excel or CSV files and don't require a live connection back to your data.
Microsoft Excel Environment
You can find a detailed guide of how to import and export data here.
Microsoft Environment Impact
Helpful resources
As always, if you have any feedback please reach out on our forums.
Thanks,
Clay.
