People often ask about the best principles to follow to make the better excel spreadsheets. Hope you might also be interested to know them. Here we go…
Showing posts with label Excel. Show all posts
Showing posts with label Excel. Show all posts
[PDF Download] Advanced Microsoft Excel 2013 Tutorial
Microsoft Excel is program designed to efficiently manage spreadsheets and analyze
data. It contains both basic and advanced features that anyone can learn. Once some
basic features are known, learning the advanced tools becomes easy. This lesson is
composed of some advanced Excel features.
. It assumes basic prior knowledge of Excel, and it is expected that the objectives from AT Step’s Excel Essentials are known. This lesson will talk about the advanced customization and formatting features that allow for easier data manipulation and organization.
Objectives
1) Learn how to Customize the Interface
2) Advanced Formatting: Custom Lists, Cell Groups, and Transposing Tables
3) Learn how to Reference Across Sheets
4) Advanced Formulas and Using Data Ranges
5) Using Data Validation
data. It contains both basic and advanced features that anyone can learn. Once some
basic features are known, learning the advanced tools becomes easy. This lesson is
composed of some advanced Excel features.
. It assumes basic prior knowledge of Excel, and it is expected that the objectives from AT Step’s Excel Essentials are known. This lesson will talk about the advanced customization and formatting features that allow for easier data manipulation and organization.
Objectives
1) Learn how to Customize the Interface
2) Advanced Formatting: Custom Lists, Cell Groups, and Transposing Tables
3) Learn how to Reference Across Sheets
4) Advanced Formulas and Using Data Ranges
5) Using Data Validation
Alternative Link : Download From Here
No-one is going you to tell about these "5 Powerful Excel Features"
Microsoft Excel is the most useful and easy tool for business analysts. It has large number of useful formulas, features and bundles of interactive charts. But, most of us are not known of all of them and there are some more features which are powerful and easy to use to make our work simpler. You might not have noticed some of the useful Excel 2010 & 2013 features like Sparklines, Slicers, Conditional Formatting and other formulas which add value to your work. In this article, I will take you through them and will give you an idea on what are those and how to use them.
Most Useful Excel Features
Among many Excel features, there are some hidden features which are easy to use and you many not know all of them. Without any further delay, we will look at 5 such Excel features.
Sparklines
Sparklines were first introduced in Excel 2010 and are used to represent visualizations for the trend across the data in a row. It fits in a single Excel cell and saves the space on the worksheet. This is a cool feature and is very easy to use. Calculating the trend for row data and placing the visualization in the single excel is really a great feature to use.
In order to create your own Sparklines, select the range of data. Click insert on the ribbon and select the type of Sparklines (Line, Column or Win/Loss). Next, enter the range of the target where you want to show the Sparklines.For more information on how to create neat charts & graphs in excel click here
Conditional Formatting
Conditional Formatting is a well known feature of Excel. It is used to visually present the data based on the conditions met. It is also useful to create heat maps. This would be helpful to find the interesting patterns by exploring the data effectively.
To create the heat map, select the data and head over to the ribbon. Under Home, clickConditional Formatting and then click Color Scales. Now, pick the color scale. You can even set the color scale by editing the formatting rule.
SMALL and LARGE Functions
We all know about MAX and MIN functions. They give you the maximum and minimum values of the selected data respectively. But, in order to find the 1st, 2nd, 3rd or nth largest or smallest value of the selected range if data, we can make use of LARGE and SMALL functions respectively.
In this example, in order to find the top two products for each month, we made use of MATCH and INDEX functions along with LARGE and SMALL functions. For more information on basic to advanced Excel formulas, visit this tutorial
Remove Duplicates
Do not blame me for mentioning this feature in this list. It is very important to get rid of redundant data from the available huge amount of data. It is one of the best ways for cleaning and organizing the data and so thought of having it in this list of powerful Excel features. Removing Duplicates feature was introduced from Excel 2007 and is helpful to remove duplicates which is the most important problem which we face.
To remove duplicates, select the data and head over to the ribbon. Under Data, click theRemove Duplicates button and what you see the data without duplicates. For more information on how to Find and Remove Duplicates, visit this tutorial.
Slicers
Slicers act as visual filters. It helps you to visualize the subset of data as a connected chart or as a raw data. For example, if you want to show the trend of sales of various products, then you can create the interactive sales trend chart using Slicers. Based on the product you select, respective chart is shown. Slicers were first introduced in Excel 2010 and enhanced a lot in Excel 2013.
In Excel 2013, if you want to add Slicer to your charts, select the data range and click on insert > Slicer. Now, select the part of the data you want to use as a filter. In the image above, Product column is used as a filter.
How many of you have used these powerful and useful Excel features? If you want to add more features to the list, please let us know through comments.
Extract text from a cell in Excel
Sometimes it is useful (or necessary) to extract part of a cell into another cell in Excel. For example, you may have a cell that contains a combination of text and numbers, or a cell that contains two numbers separated by a delimiter such as a comma.
5 Excel Tips and Tricks to Boost your Productivity
Taking the time to learn some Excel tips and tricks will likely help you boost your productivity and streamline your spreadsheets.
We previously shared some Excel insights. Here are five more tips to consider:
1. Use Number Formatting Shortcuts
For circumstances when you need to format a large amount of data, Excel offers time-saving shortcuts for many common formatting functions. Experiment with these handy ones:
- Format numbers to include two decimal places: Ctrl+Shift+1
- Format as time: Ctrl+Shift+2
- Format as date: Ctrl+Shift+3
- Format as currency: Ctrl+Shift+4
- Format as percentage: Ctrl+Shift+5
- Format in scientific/exponential form: Ctrl+Shift+6
2. Use Sparklines to Display Data
Sparklines are a built-in feature of Excel 2010, Excel 2011 for Mac and Excel 2013 that allow you to display small charts inside cells. These can be line charts, bar charts or simple win/loss charts. To create a Sparkline chart, select the range of numbers you’d like to include, click the “Insert” menu, then choose one of the chart options. Select a location range, which must be located along a single row or column in the same worksheet as your data range. Sparklines can help you easily display trends in your data in a compact format.
3. Manipulate Data with Pivot Tables
When you have a large, detailed data set, pivot tables allow you to easily manipulate your data. These tables are interactive and can help you analyze data, detect patterns and make comparisons. Creating a pivot table is as easy as using the built-in PivotTable and PivotChart Wizard, located in the “Data” drop-down menu. The wizard helps you choose the data to include in your PivotChart and format that information in a meaningful manner.
4. Move Between Formulas and Results
To efficiently switch between the cell data and formula, use the Ctrl+tilde (~) keystroke. This allows you to rapidly check formulas when working in a large spreadsheet.
5. Hide Zero Values
Hiding zero values can be helpful within large data sets by allowing you to see data more clearly. To hide zero values, you simply need to change the options in your Excel setup. Navigate to this function by clicking the “File” drop-down menu and choose “Options.” Then choose “Advanced” from the left-hand menu and uncheck the box for “Show a zero in cells that have zero value.” (Mac users: Go to the “Excel” drop-down menu and choose “Preferences,” then uncheck “Show zero values.”)
These are just a few of the helpful Excel features that can decrease the time you spend on spreadsheets and increase the amount of work you get done. What Excel tips and tricks do you use to boost productivity? Let us know in the comments section.
Top 10+ Free Excel Games To Download
Here you can download some of best games that can be played in Excel. Just download it and start playing in your excel :)
Adrenaline Challenge:This is a motor cycle game with predefined objectives. Complete the objectives as fast as possible and move to the next stage.
Angry Birds: I can bet that most of us would have played angry birds before, but this is a handy flash version of the game. This is a must try for all the angry birds lovers.
Bang Bang -2: Bang Bang-2 is a first person shooting game. Your objective is to kill as many people in the stipulated time.
Batman: Play the heroic Batman game to save the Gotham city from bad people. Defeat the bad people and move to the next levels.
BMX-Tricks: Ride your cycle and perform different stunts on the track to earn extra points in stipulated time. You can ride over grills, on tables etc. to earn extra points.
Bowling: This is a cool game with 3D graphics. Aim the bowl using the mouse to hit the pins. A must try game for bowling lovers.
Chopper Challenge: Fly the chopper as far as possible and avoid the obstacles in the path. Make a high score and challenge your friends.
Defend Your Castle: Defend Your Castle was one of the most rated games of 2012. Here your goal is to protect your castle from the opponent team.
Drunk Driving: In this game your task is to handle an uncontrollable car. Avoid the obstacles like other cars and buses to clear stages.
Famous Eyes: This game consists of images of eyes of some of the famous personalities, identify them and earn points.
Frog leap: This is a little puzzle game. The game challenges you to interchange the positions of 6 frogs. Clear the level and challenge your friends.
2D Knockout: 2D knockout is a boxing game. You have to play against the boxers of various countries. The objective of this game is to defeat your opponent.
Download it here.
3D Tic Tac Toe:3D Tic Tac Toe is a must try game for all the tic tac toe lovers. The objective of the game is to get 4 X’s or 0’s in a row, whosoever gets it first wins the game.
You can download this game here.
Adrenaline Challenge:This is a motor cycle game with predefined objectives. Complete the objectives as fast as possible and move to the next stage.
Get it here.
Air Fighting: This is a traditional Air Plane fighting game. The objective of this game is to destroy all the opponent air planes.
Click this link to download.
Angry Birds: I can bet that most of us would have played angry birds before, but this is a handy flash version of the game. This is a must try for all the angry birds lovers.
You can download this game here.
Angry birds Rio: This is another angry bird’s game with bigger and better graphics. I am quite sure that you will fell in love with this one.
Download it here.
Apple Shooter: Apple shooter is a very nice game designed by Wolf games. Here your objective is to shoot the apple without killing the man beneath it.
Get it here.
Ball Challenge: This game has a set of blue and red balls bouncing inside a box. You need to keep the blue balls inside one compartment and the red ones inside another compartment.
Click this link to download.
Bang Bang -2: Bang Bang-2 is a first person shooting game. Your objective is to kill as many people in the stipulated time.
Excel game here.
Baseball Pinch Hitter -2: This game has awesome graphics and great gameplay. The game has certain levels and you have to meet some targets to clear those level.
You can download this game here.
Batman: Play the heroic Batman game to save the Gotham city from bad people. Defeat the bad people and move to the next levels.
Download it here.
Bloxorz: The aim of this game is to roll the block such that it falls in the square hole inside the board. The game has 33 stages and different challenges that you will love to tackle.
Get it here.
BMX-Tricks: Ride your cycle and perform different stunts on the track to earn extra points in stipulated time. You can ride over grills, on tables etc. to earn extra points.
Click this link to download.
BowMan: This is a third person shooting game where you have to kill your opponent using arrows. You can shoot the arrow at different angles and power to hit the opponent.
You can download this game here.
Bowling: This is a cool game with 3D graphics. Aim the bowl using the mouse to hit the pins. A must try game for bowling lovers.
Download it here.
Chaser: This is a nice time pass game. The game is very similar to the traditional snake’s game with the only difference that you and computer are playing on the same board.
Get it here.
Chopper Challenge: Fly the chopper as far as possible and avoid the obstacles in the path. Make a high score and challenge your friends.
Click this link to download.
Crab Ball: This a little funny volleyball like game between two crabs. Simply download the game and enjoy playing it.
Excel game here.
Defend Your Castle: Defend Your Castle was one of the most rated games of 2012. Here your goal is to protect your castle from the opponent team.
Download the game here.
Down Hill Stunts: This is a motor bike game, your aim is to ride your bike on the track and collect the stars on the way and that too without crashing.
Download it here.
Drunk Driving: In this game your task is to handle an uncontrollable car. Avoid the obstacles like other cars and buses to clear stages.
You can download this game here.
Easy Chess: This a flash version of the chess game. A must try game for a die-hard chess lover.
Download it here.
Famous Eyes: This game consists of images of eyes of some of the famous personalities, identify them and earn points.
Get it here.
Fishy: Fishy is small time pass game, where you have to feed your fish on the other small fishes and also save your fish from the other fishes that are bigger than it.
Click this link to download.
Frog leap: This is a little puzzle game. The game challenges you to interchange the positions of 6 frogs. Clear the level and challenge your friends.
Excel game here.
Golf: The main objective of this game is to put the ball inside the hole in minimum number of strokes. The game also offers a multiplayer mode.
Download the game here.
Subscribe to:
Posts (Atom)