Showing posts with label Excel Tips. Show all posts
Showing posts with label Excel Tips. Show all posts

Divide Excel Worksheet into Pages!!


Excel Page Layout View


If you're using Excel 2010/ 2013, you can divide your worksheet into individual pages. This exciting feature was unthinkable even on Excel 2003. At first, Microsoft introduced this Page Layout feature in Excel 2007. And it's available in Excel 2010 and 2013 too. 


Almost all of you are familiar with the Print Layout view of Microsoft Word. Excel Page Layout view does the same job here. If you enable Page Layout view in a worksheet, you can easily understand your print area with header and footer. And it also makes your printing task easier


Excel 2013 Page Layout View


Applies to:
  • Excel 2007
  • Excel 2010
  • Excel 2013


Advantages of Page Layout


  • You can view how your printed document will look
  • Check where your page begins and ends
  • View headers and footers of the page
  • Print the worksheet page by page

How to Enable Page Layout View

You can enable page layout view as below:
  1. Open any worksheet or create a new one
  2. Go to the View Tab > Workbook Views > Page Layout 

Page Layout Option in View Menu


You can also use the keyboard shortcuts:

Press Alt Key > Then type WP 


If you would like to back to the Normal View then:

Press Alt Key > Then type WL 


You can also enable Page Layout view from Status Bar. I think it's the most handy option. Follow the image below:


Excel 2013 Page Layout Icon


The selected icon in the middle is Page Layout icon. The left one is for Normal View. And the right one is for Page Break Preview. 






Stay with Marks PC Solution to get more interesting IT topics!



How to Print Headings on Every Page in Excel?


Repeat Column Headings on Every Page


Sometimes you may need to print same column headers on every page in Microsoft Excel. And to do so, you don't need to manually copy the headers on every page. You can simply set a command from page setup option to print column headers on every page automatically! 

In my previous post, I discussed about how to lock or freeze column headers. By freezing rows or columns, you can just show headers at the time of scrolling. But they will be printed on the first page only.  In this tutorial, you will learn - how to print same column headers on every page automatically .  .  .


Applies to:
  • MS Excel 2000/ XP
  • MS Excel 2007/ 2010/ 2013
Note: This tutorial has been prepared using Excel 2013. So, few options may differ slightly.


Let's start - 
  1. Open a previously saved worksheet or create a new one in MS Excel.
  2. Go to the Page Setup option from Page Layout Tab (Office XP/ 2000 users should find it under Edit Menu).
  3. Go to the Sheet tab from Page Setup.
  4. Look at the Print titles option: Rows to repeat and Columns to repeat.
  5. To repeat the headers at the top, click on the first one. Or click on the second one to repeat the columns.
  6. After clicking the box, select the area of your sheet which needs to be repeated. Follow the image below.
  7. Hit OK to close the Page Setup. Done! Now check by printing or look at the print preview. 

Repeat Headers in Every Page



Here I showed the process for rows only. You can also choose columns to repeat by the same way. And I've also uploaded a same sheet for you. Download the sheet from the link below:






Open the above sheet. You will find a worksheet named Rows to Repeat. I've set top 3 rows to be repeated on every page. You can directly check it by printing this two-page worksheet. Or simply check it in Print Preview. 


Hopefully, today's topic will be helpful for your study or office work . . . :)





Stay with Marks PC Solution to get more interesting IT topics!


How to Lock Rows/ Columns in Excel?





When you are preparing a large statement or table in Excel, the column headings at the top usually disappear when you scroll down. And this is really troublesome when you need to input data under several headings. Couple of months ago, I faced the same problem. And just 2 days ago, one of my relatives questioned me how to create static/ fixed column header in Microsoft Excel. 


Then I explored the topic and found the solution from Excel help and Microsoft Support Page. So, in this post, you are getting another important excel tutorial - How to Lock/ Fix/ Freeze Rows or Columns in excel while scrolling throughout the worksheet.


Lock/ Fix/ Freeze Rows or Columns

In Excel, the term is known as Freeze Panes. With this option, you can freeze a row or column that you want visible even when scrolling down or right. 

Suppose, you preparing a salary sheet that contains hundreds of rows. And you can view only 25/30 rows in a view. When you scroll down, the column headings will disappear. If you want the first row (that contains headings) visible, you have to use the Freeze Panes option. 


If you're using Office 2000/ XP/ 2003, you will get the freeze panes option under Window menu. 

  1. Simply open a worksheet
  2. Choose the row you wanna freeze
  3. Then go to the Window menu
  4. Hit on the Freeze Panes option - Done! 
If you want to unlock the row, follow the steps again, and this time you will get unfreeze option. 


For Office 2007/ 2010/ 2013

Here you will get the freeze panes option under View tab in Window section. And you can also freeze both top row and left column! 



Excel 2010 Freeze Panes under View Menu


  1. Open a previously saved worksheet or create a new one.
  2. Input your necessary data.
  3. Go to the View Tab.
  4. Find the Window section at the right and locate Freeze Panes
  5. Hit on the down arrow, you will get 3 options - Freeze Panes, Freeze First Column, Freeze Tip Row.
  6. Freeze Panes - Used for locking multiple rows. And the next two are used either to lock  only the first column or first row.
  7. If you want to unlock the locked row or column, you have to unfreeze it by following the same steps. 

Note: Suppose you wanna freeze first 3 rows. Then you have to click on the 4th row. Now go to the Freeze Panes option in view menu and use Freeze Panes. 





I've also uploaded a sample sheet which you can download from the above link. Download and open the above sample sheet. It contains the example of freezed panes. 

In the first sheet, I've freezed a single row. In the second sheet, I've freezed multiple rows. There you will see, first three rows are freezed. To do it, you have to put your cursor in the 4th row, then you have to choose Freeze Panes


Hope this will be helpful for you. In my next post, I'm gonna discuss about - how to print the headings in every page


Reference: Freeze or Lock Rows and Columns



Stay with Marks PC Solution to get more interesting IT topics!


Creating Column, Bar & Pie Chart in MS Excel (With Sheet)


Creating Column, Bar and Pie Charts in MS Excel


Hello Dudes! Hopefully you're alright and also hope you're enjoying my posts. After a long time, I've come with MS Excel Tips. Couple of Months ago, I shared an Excel Sheet with you that includes - Salary Sheet, Result Sheet, Electric Bill etc. Today I've come with another sheet which will help you to learn how to create Column, Bar and Pie Chart in MS Excel. 


Actually I was planning to share this tips with you for a long time. But I couldn't understand how to start and what to write. Finally, I've decided to share a sheet with you as example. And I will also try to clarify how to create charts in MS Excel. 


Applies to:
  • Office 2007
  • Office 2010
  • Office 2013
But I've used Office 2010. So there might be some changes in other versions. 




For your convenience, you can download the above file and have a look before we start. It's just 22 KB! Remember, it is an .xlsx file and can only be opened with Office 2007 or above. Three charts are in three sheets. You will get the names at the bottom of the worksheet. 

Okay let's begin. I am using Office 2010. 


Column Chart:


Column Chart is a widely used chart which is normally used to show Profit, Growth Rate etc. It is shown as vertical lines. In my example sheet, I showed the pageviews of my site for last five months. To create a column chart, follow the steps below:
  1. Run MS Excel.
  2. Input your data (Maybe you can enter particular in one column and values in other columns). In my example, I showed only a single column value. You can enter multiple columns. Maybe you can show the pageviews of three years while I showed only one year.
  3. Now select your input data (in my example, I selected only data - June to 120000) to avoid the column header to be appeared in the chart. 
  4. Go to the Insert Tab > Find the Chart Section > Choose Column (I've used the first one).
  5. Now you will see a Column Chart in your book. 

Bar Chart:

This is almost similar to Column Chart but gives you a different flavour. It presents data horizontally. Creating a Bar Chart is almost similar to that of Column. After inputting the data, you have to go to the Charts and then choose Bar Chart. 


Pie Chart:

The best example of the use of Pie Chart is the representation of Budget. Without Pie Chart, representation of budget can't be imagined. It is notmally used when something is divided into parts. For example, in a budget, we can see what percentage of money will be spent in a particular sector. 

Such as for Education - 20%, for Defense - 30%, for Industry - 15%, for Power - 20% etc. Only Pie Chart can show this properly. 

To create a Pie Chart (take help of my example sheet), you have to enter the category in one column, and then enter the values in another column. Then select the input data, go to the charts, choose Pie Chart. That's it. 


Customization of Charts:

Creating a chart is not very difficult. But arranging it properly could be a boring task if you don't know how to do it. I'm gonna discuss about customizing the charts in brief:
  • After creating any chart, if you click on it, you will get three new tabs - Design, Layout and Format.
  • From Design Tab, you can change your chart design - Chart Style and Layout. 
  • In Layout Tab, you will find Chart Title, Legend Position, Data Label etc.
  • Format Tab will allow you to perform all kinds of formatting tasks including text color, line color, line style, font style, shape effects etc. 

Of course you can use your mouse to choose a particular part of the chart. Then you can change that part. To select a segment of your chart, click on it and then click again (not double click). If you wanna move any part of your chart, hit on it and move it when you get the moving pointer. 


Stay with Marks PC Solution to get more interesting IT topics!


How to Type More than 15 Digits in MS Excel?




If you are a regular user of Microsoft Excel then you might be in danger while typing more than 15 digits in a cell. If you type 1111222233334444 then it will be 1111222233334440. Or if you type 25127680876675435 then it will be 25127680876675400. Simply the digits after 15th place will be changed to zeros! Doesn't it seem peculiar? 


Today I will explain when & why this problem occurs and how you can solve it easily. 


When the Problem Occurs?

The problem occurs when you try to type more than 15 digits in a cell like below: 

####-####-####-####
1234324545679876

But when you press Enter, the output will be 1234324545679870. Excel changes the last digit to a 0. And why someone needs to type more than 15 digits in a cell? It can't be a currency figure. 

Usually when you type a Credit Card/ Debit Card/ BO Account Number you have to type 16 digits. And the calculation of excel doesn't allow you to type more than 15 digits. That's why it converts the last digits (after 15th place) into zeros.  


Reason

You may ask - why Excel restricts users to type 15 digits in a cell? I'm also searching for a satisfactory answer. I found the following reason from Microsoft Support

Excel follows the IEEE 754 specification on how to store and calculate floating-point numbers. Excel therefore stores only 15 significant digits in a number, and changes digits after the fifteenth place to zeroes. 

I don't know what does it mean! :) Anyway, I can show you several ways to get rid of this problem . . . 


Solution

There are several ways through which you can avoid this problem. First I'd like to show you an easy way:

Method 1: Putting Inverted (') Before the Number

When you type more than 15 digits in a cell, just place an inverted comma ( ' ) before that number. And that mark will not be displayed on the sheet after pressing Enter! Though it will be displayed in the formula bar. Look at the image below: 


I don't know why the inverted mark disappears automatically. But I can explain why the 16th digit doesn't change to 0. Because when you type ' before a number then Excel doesn't consider this value as Number. It maybe consider the cell as text. That's why it remains unchanged. 


Method 2: Format the Cell as Text

Maybe the 2nd method is more useful. Before typing more than 16 digits in a cell, you have to format those cells as text. 

You need to select the cells first. Office 2003 users will find the Format Cell options from the Format Menu > Cells > Format Cells. And Office 2007 and 2010 users will get this as below: 


Format Cells Menu in Excel 2010


Format cell window is same in all version of MS Excel. After selecting the cells, you will just need to choose the Text option and then hit OK. That's it. 

Now you will be able to type more than thousand characters in a single cell! 


Applies to: 

All versions of Microsoft Excel. 


Stay with Marks PC Solution to get more interesting IT topics!