Excell tips in pdf

First is RAND which generates random numbers between 0 and 1. ALERT: These both functions are volatile so whenever you re-calculate your worksheet or hit enter, they update their values so make sure to use them with caution. In Excel, there is no specific function to count words. You can count characters with LEN but not words. But, you can use the following formula which can help you to count words from a cell.

This formula counts the number of spaces from a cell and adds 1 to it after that which equals the total number of words in a cell. This mysterious function is specifically made to get the difference between two dates. I have figured out that there are 4 different ways to calculate the ratio in Excel but using a simple divide method is the easiest one.

This formula divides the larger number with the smaller one so that you can take the smaller number as a base 1. To calculate the square root, cube root, or any root of a number the best way is to use the exponent formula. In the exponent formula, you can specify the Nth number for which you want to calculate the root. To get a total number of days in a month dynamically you can use the following formula:. To simply get the last date of a month you can use the following dynamic formula.

If you love to use a line chart, then you are awesome but it would be more awesome if you use a smooth line in the chart. This will give a smart look to your chart. Copy Chart Format in Excel. This charting tip is simple but still quite useful. But the better way is to hide them instead of deleting them. Here are the steps:. If you are dealing with the large numbers in your chart, you can change the units for axis values.

I often use Excel charts with rounded corners and if you like to use round corners too, here are the simple steps. You can use the following options for that empty cell. How to Create a Pictograph in Excel. You can create a chart template to use it anytime in the future and the steps are as follow. So if your favorite chart is a line chart, then the shortcut is useless for you.

Here are the steps to fix this:. When you hide a cell from the data range of a chart, it will hide that data point from the chart as well. To fix this, just follow these steps.

If you use comments in your reports then you can print them as well. At the end of all printed pages, you can get a list of all the comments. Sometimes we struggle to print entire data on a single page. Instead of using the page number in the header and footer, you can also use a custom header and footer.

Imagine you have fewer data to print on a page. In this case, you can align it at the center of the page while printing. But what if you need to print that range frequently, in that case, you can specify the printing area and print it without selecting it every time.

You can replace all the error values while printing with a specific value three other values to use as a replacement. You can specify that number and the rest of the pages will follow that sequence. Sometimes we need to track some important cells in a workbook and for this, the best way is to use the watch window. In the watch window, you add those important cells and then get some specific information about them in one place without navigating to each cell. Here are the steps to use it:.

Flash fill is one of my favorite options to use in Excel. Let me give you an example. Here are the steps to use it: You have dates in the range A1: A10 and now, you want to get the month from the dates in the B column. Once you do this it will extract the month from the rest of the dates, just like below.

In Excel, using a live image of a table can help you resize it according to space, and to create a live image there are two different ways in which you can use it. One is camera tools and the second is the paste special option. Here are the steps to use the camera tool and for paste special use the below steps. A few of the Excel users know that there is a default data entry form is there which we can use. And the best part is there is no need to write a single line of code for this. We all have some favorite options or some options which we use frequently.

To access all those options in one place you create a tab and add them to it. Follow these steps:. In simple words, Goal Seek is a problem-solving tool. It helps you find the input value by proving the value you want in the result. This is an option where you can make Excel speak the text you have entered into a cell or a range of cells.

TRIM can help you to remove extra spaces from a text string. Just refer to the cell from where you want to remove the spaces and it will return the trimmed value in the result. In Excel, to remove these duplicate values is quite simple.

Once you click OK, Excel will remove all the rows from the selected data where values are duplicates and show a message with the number of values removed and unique values left. I know five different ways to merge text from a range but out of those Fill Justify is my favorite. Let say you have words in the range A1: A5 and you want to concatenate all of them in a single cell.

You can do this simply by the find and replace option. So, you have text in multiple cells, and you want to combine all the text into one cell. No, this time not with fill justify. All you need to do is to add a delimiter if any , and TRUE if you want to ignore empty cells, and in the end, refer to the range.

But if you convert this table to something like the one below you can use it easily anywhere. But if you convert this table into something like the one below you can use it easily anywhere.

So how to do this? The easiest way to deal with these error values is to select them and delete them and these are the simple steps.

The normal way is to cut and paste them one by one. But we also have an out-of-the-box way. In Excel, you can sort columns just like you sort rows and by using the same methods you can arrange them in a custom order. Click here to learn this full tip. Sometimes you have dates that are stored as text and you can use them in a calculation and further analysis.

The easiest way to convert a negative number into a positive is by using the. Before I started to use format painter for applying cell formatting, I was using paste special with the shortcut key. I always found it quicker than using a shortcut key to change the name of a worksheet. All you have to do is just double-click on the sheet tab and enter a new name. Let me tell you why this method is faster than using a shortcut.

Suppose if want to rename more than one worksheet using a shortcut key. Before you change the name of a worksheet, you need to activate it. But if you use the mouse it will automatically activate that worksheet and edit the name with only two clicks.

I am sure shortcut addicts always use a shortcut key to drag formulas and values in downward cells. But using a fill handle is more impressive than using a shortcut key. This method only works if you have values in the corresponding column and it works only in the vertical direction. Just make a double click on the active tab in your ribbon and it will collapse the ribbon. And if you want to expand it back just double click on it again.

You often use shapes in our worksheets to present some messages and you have to insert some text into those shapes. Besides the typical method, you can use a double click to edit a shape and insert the text into it. Whenever you have to adjust column width you can double click on the right edge of the column header. It auto sets the column width according to the column data.

This trick can be useful if you are working with a large dataset. By using a double click, you can go to the last cell in the range which has data. All you have to do is just make a double-click on the border of the graph to open the formatting option. As you already know Excel stores data in pivot cache before creating a pivot table. You can extract data from a pivot table by double-clicking on data values.

As soon as you do this Excel will insert a new worksheet with the data which has been used in the pivot table. There is a right-click drop-down menu in Excel which few users know about.

To use this menu all you need to do is select a cell or a range of the cell and then right-click and while holding it, drop the selection to somewhere else. Normally while working on Excel I create more than 15 Excel files every day. And, if I save each of these files to my desktop it looks nasty.

Here are the steps to disable the start-up screen in Microsoft Office. Create an Excel data table to compare multiple results Suppose you want to view multiple possibilities within an Excel calculation. The answer is to create a data table. Put these few techniques to work for almost magical results. How to easily print Excel sheets in black and white Printing a colorized Excel spreadsheet in black and white is easy, whether you do so manually or using a short VBA procedure.

How to use Excel styles efficiently Learn what styles are and how they can help you format your sheets more effectively and efficiently. How to use Excel's border options by creating a simple floor plan Applying borders to your sheets will make them more readable and easier to use. Learn how to use borders efficiently by creating a simple floor plan. Excel tip: Three cell formats that will make header text fit Header text often takes up too much space.

Use these three formats to put headers on a diet when working in Excel. Excel Ideas: An intelligent data visualisation tool Excel Ideas helps you extract the key information from a spreadsheet, but there's currently a limited range of insights available and some data formatting may be required. How to hide Excel data and alert readers It's common to hide Excel data. If you want viewers to see that data, you can add a friendly alert. How to work with different views in Microsoft Excel You can change views in Excel depending on what you're doing and what you want to see.

Learn how in this step-by-step tutorial. Entering leading zeroes in Excel By default, Microsoft Excel won't display or store leading zeros. Read this Excel tip to learn three handy ways to get around the problem. How to suppress 0 values in an Excel chart There isn't a one-size-fits-all solution for removing 0 values from an Excel chart. Here's a look at a few methods. Use a custom format in Excel to display easier to read millions Use a custom format in Microsoft Excel to reduce the number of digits in a large number without losing its scale.

Two ways to build dynamic charts in Excel Users will appreciate a chart that updates right before their eyes. In Microsoft Excel and Excel , it's as easy as creating a table. In earlier versions, you'll need the formula method.

How to use Excel's find feature to highlight or delete matching values Excel's Find feature offers more than just finding values if you know the right steps. Learn how to put it to use in your spreadsheets.

How to split a column using an IF function in Excel If you need to conditionally split values into multiple columns in Microsoft Excel, consider using the IF function.

How to combine Excel's VLOOKUP function with a combo box for enhanced searching A combo box's autocomplete feature linked to a lookup function creates a flexible search tool.

How to subtotal transactions by conditional date components in Excel If you need a monthly or yearly balance for revenue transactions in Microsoft Excel, you're in luck because both are easy! Here's how to find the totals you need. How to do more advanced averaging in Excel Averaging values in an Excel sheet is easy.

But perhaps you want to ignore zeros or include multiple sheets. Knowing how each averaging function works is the key to choosing the right one. How to average with and without the highest and lowest values in Excel Averaging in Microsoft Excel is easy, until you start excluding specific values. Here are three ways to average a data set when giving special consideration to the highest and lowest values.

How to calculate bonuses and commissions in Excel Everyone likes a bonus, but sometimes calculating one can be a bit complicated--at first. Here's how to calculate the amount in Microsoft Excel that you or your staff will get paid. How to find the minimum and maximum values within a specified set of years in Excel There's a lot going on in these formulaic conditional rules in Microsoft Excel that highlight the smallest and largest values within a period of years.

How to copy expressions without changing cell references in Excel Microsoft Excel updates cell references when you copy an expression. Here are a couple of workarounds for those rare occasions when you don't want to change the cell references. How to expose expressions that return a defined error value in Microsoft Excel You can't distribute an Excel sheet that displays ugly error values when something goes wrong!

You can easily create numbered lists in your document. Most versions of Word even allow you to modify the distance Do you need to frequently add en spaces and em spaces to your documents? You can add special tools to Word that make Discover how to extend the capabilities of Office Word, Excel, PowerPoint, Outlook, and Access with VBA programming, using it for writing macros, automating Office applications, and creating custom applications.

It is inevitable that if you spend time creating a chart you will want to print that chart on your printer. Here's how When printing a worksheet, you may want to rotate the output on the page to fit a certain orientation.

Excel doesn't Want to make sure that when you worksheet is printed that everything in the workbook is really printed? You can Enter your address and click "Subscribe.

Your e-mail address is not shared with anyone, ever. Maximum image size is 6Mpixels. Images larger than px wide or px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted. Mahmoud Try changing from ThisWorkbook. Path to ActiveSheet. Hi all I have the below macro in my worksheet to save as PDF, but users are complaining that compile error is coming!

Select Selection. Protect "" Range "E8". Select End Sub. Hi, I'm stuck with following issue: I am having a number up to report diagrams to print a pdf based on an Excel data table where the charts get updated for each row, one by one. Count Worksheets "Auswertung". ListRows i.

Range "rngAllValues" Worksheets "Report". Above Mentioned macro works very fine but it saves file in my documents but what if i want to save pdf file in current working folder Please advise.