ONLINE DRIVERS MEDIA: excel

Showing posts with label excel. Show all posts

Friday, April 8, 2016

Type Multiple Lines in a Cell in Excel

How to Type Multiple Lines in a Single Cell in Excel

Excel users often face a common problem. Normally you cant type two or three words in a cell in Microsoft Excel. You have to merge cells to type something. But if you have no option to merge cell horizontally then you may fall in trouble. Believe it or not, last week I had the same problem while working in a worksheet!

Today I am sharing the solution with you. Look at the image below -

By default, you cant type like the image above in MS Excel. Have you noticed - I havent merge the rows or colums. And there are 3 lines in Column A and 4 lines in Column B. Try to type multiple lines in a row or cell. Simply you cant. You have to use the Wrap Text option to type multiple lines in a cell.

For Excel 2003

Sorry to say, I dont have Microsoft Office 2003. So I cant show you the path. Probably you have to follow this way-

Format Menu > Font > Format Cells > Alignment > Text Control > Wrap Tex (Check the box) > OK.

Im not sure about this command. But the main point is - find the format cells window and then check the wrap text from alignment tab.

You can also press Shift + Ctrl + F to open Fomat Cells dialogue box.

For Excel 2007 and 2010

In Excel 2007 or 10, the process is almost same. But as you have to use the ribbon interface, you have to follow this way -

Go to the Home Tab > Click on the Marked area of Alignment > Now you will get Format Cells window (Shift + Ctrl + F) > Choose Alignment tab > Check the Wrap text option > Hit OK.

Shortcut Method!

Maybe its very time consuming for you. Im gonna share a secret shortcut!

Type your text in cell, when the area ends, press Alt+Enter. You will get a new line in cell! See the image below:

First Ive typed Marks PC, then I pressed Alt+Enter and typed Solution. But remember, first you have adjust the column size. Adjust the column size and then type your text. Once you press Alt+Enter, new lines will be creating automaticlly when the cell area ends.

There is another shortcut for you! Just look at the image below:

Have you noticed the market button? Its in the Alignment section, just below the Review Tab. This button is a shortcut to wrap text. Type your text and hit on this button to create multiple lines in a row/ cell.

You can adjust the column size after typing the text. Your lines will be adjusted automatically.

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

How to Print Headings on Every Page in Excel

Sometimes you may need to print same column headers on every page in Microsoft Excel. And to do so, you dont 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.

Lets start -

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

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

Download Sample Sheet - Rows to Repeat

Open the above sheet. You will find a worksheet named Rows to Repeat. Ive 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, todays topic will be helpful for your study or office work . . . :)

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

Creating daily activity logs through MS Excel

Excel is the spreadsheet application in the Microsoft Office productivity suite. You can use Excel to generate daily activity (task) logs. This document explains how to:

Create your base log, which is a simple Excel spreadsheet that serves as a template for your daily activity logs.
Create and use daily activity logs, each of which starts as a copy of your base log. For each activity or task that you log, you need enter only a category, a brief description and an ending time. The spreadsheet automatically calculates starting time, activity interval, and total daily time.
Create your PDF daily activity log.
Cover your assets (CYA).

Note: Although this document applies specifically to creating daily activity-log spreadsheets through MS Excel, it applies generally to creating activity logs through other spreadsheet applications, such as LibreOffice Calc and Google Drive Spreadsheet.

Creating your base log

To create your base log, you need to format it and then add its formulas.

Formatting your base log - Your base log requires six columns (A through F) and four rows (1 through 4). To format your base log, do the following:

Open a new MS Excel file, rename its worksheet as DailyLog, and then save the spreadsheet as BaseLog.xls.
Select cells A1 through C1, select HOME (in the toolbar) to display its ribbon, and then click the Merge & Center icon (at the right in the Alignment frame).
Adjust the width of column D, making it wider so you can type activity descriptions.
Merge cells E1 and F1.
Select cells A1 through F2 and then format them as Ariel, 12 points and bold.
In rows 1 through 4, type text as shown in Figure 1 below.
Select cells A3 through B4, select HOME, click the arrow at the right of Number, select Time, select 1:30 PM, and then click OK to format cells A3 through B4 for 12-hour time. Note: If you prefer 24-hour time, select 13:30.
Select cells E3 through F4, select HOME, click the arrow at the right of Number, select Time, select 13:30, and then click OK to format cells E3 through F4 for hours and minutes (no AM or PM).

Figure 1 - Formatting your base-log spreadsheet.

Adding base-log formulas - To add formulas to your base log, do the following:

Format you base log according to the procedure above.
Select merged cells E1 and F1, type =today() into the function (fx) bar, and then press Enter.
Select cell A3, type your typical first-activity start time, such as 8:00 am.
Select cell B3, type your typical first-activity end time, such as 8:15 am.
Select cell A4, type =b3 into the function bar, and then press Enter.
Select cell E3, type =b3-a3 into the function bar, and then press Enter.
Select cell F3, type =e3 into the function bar, and then press Enter.
Select cell E3, copy it and paste it into cell E4.
Select cell F4, type =sum($e$3:e4) into the function bar, and then press Enter. Your base log should now display as shown in Figure 2 below.
Save your BaseLog.xls, and make at least one backup copy.

Figure 2 - Adding formulas to your base log.

Creating and using your daily logs

To create and use your daily activity logs, you need to create a new log at the start of each day. For an example daily activity log, see Figure 3 below. To create each daily log, do the following:

Open your BaseLog.xls Excel spreadsheet file, and then save it with a file name that includes the current date and your name, such as 2012-10-11_Your_Name_Log.xls.
Modify cells A3 through D3 to log the your first daily activity.
Describe your next activity in cells C4 and D4, and then, when done, record its end time in cell B4.
Copy row 4, paste it into row 5, and then modify cells B5 through D5 to log your next activity. Note: Modify cells only in columns B through D; spreadsheet formulas update other cells automatically.
For each subsequent activity, copy your last row, paste it into the next, and then modify its cells in columns B through D.
At the end of the day, make at least one backup copy of your daily activity log.

Figure 3 - Example daily activity log.

Creating your PDF daily activity log

To create your PDF daily activity log, do the following:

Open your Excel daily activity log, such as 2012-10-11_Your_Name_Log.xls.
Click the Office Button(at the upper left), to display its menu.
Select Save As, click PDF or XPS, and select the PDF file type.
Click Publish to generate your PDF daily activity log, such as
2012-10-11_Your_Name_Log.pdf.

Covering your assets (CYA)
Your daily activity logs can help you practice prudent CYA. For example, at the end of each day, you can:

Create a PDF daily activity log according to the section above.
Create a PDF status report according to Creating a cumulative status report through MS Word.
Send an email to all your managers, and attach both PDFs.
Make personal backup copies of both PDFs, and file them in private storage.

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! Doesnt 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 cant 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 doesnt allow you to type more than 15 digits. Thats 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? Im 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 dont 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 Id 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 dont know why the inverted mark disappears automatically. But I can explain why the 16th digit doesnt change to 0. Because when you type before a number then Excel doesnt consider this value as Number. It maybe consider the cell as text. Thats 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 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. Thats 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!

Divide Excel Worksheet into Pages!!

If youre 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 its 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.

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:

Open any worksheet or create a new one
Go to the View Tab > Workbook Views > Page Layout

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 its the most handy option. Follow the image below:

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 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 youre using Office 2000/ XP/ 2003, you will get the freeze panes option under Window menu.

Simply open a worksheet
Choose the row you wanna freeze
Then go to the Window menu
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!

Open a previously saved worksheet or create a new one.
Input your necessary data.
Go to the View Tab.
Find the Window section at the right and locate Freeze Panes
Hit on the down arrow, you will get 3 options - Freeze Panes, Freeze First Column, Freeze Tip Row.
Freeze Panes - Used for locking multiple rows. And the next two are used either to lock only the first column or first row.
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.

Download Sample Sheet

Ive 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, Ive freezed a single row. In the second sheet, Ive 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, Im 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 youre alright and also hope youre enjoying my posts. After a long time, Ive 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 Ive 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 couldnt understand how to start and what to write. Finally, Ive 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 Ive used Office 2010. So there might be some changes in other versions.

Download Column, Bar & Pie Chart Sheet

For your convenience, you can download the above file and have a look before we start. Its 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 lets 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:

Run MS Excel.
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.
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.
Go to the Insert Tab > Find the Chart Section > Choose Column (Ive used the first one).
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 cant 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. Thats it.

Customization of Charts:

Creating a chart is not very difficult. But arranging it properly could be a boring task if you dont know how to do it. Im 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!

MS Excel Training Basics Salary Sheet Result Sheet Electric Bill

Hello Guys!

Today Ive come with something special for you! This is about Microsoft Excel Training. You will get an Excel File with some popular excel sheets.

Previously I discussed about MS Word:

How to Create Shortcuts for Symbols in Microsoft Word?
Marks PC Solution: How to Show Text Boundaries in MS Word?
How to Change Measurement Unit of Office 2003, 07 and 10?
Marks PC Solution: Keyboard Shortcuts for MS Word (All Versions) etc.

But for the first time in this blog, Im writing about MS Excel. The file contains the following materials:

Some Basics about MS Excel
Salary Sheet
Electric Bill
Result Sheet
And some discussions

Hope you will be find it useful. All the sheets are included in a single file. It just 28 KB. After downloading the file you should be able to move different sheet from the bottom. For your convenience each sheet has been named properly. But it supports only:

Office 2007
Office 2010
Office 2013

If youre using 2003 or earlier version then first open this file in 2007 or 10. Then you can save the file for using in 2003.

Download Link

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

How to Rotate Text in Excel

When youre running out of space to write column headings in MS Excel, you can rotate your texts. In my last post, I discussed how to wrap text to create multiple lines in a cell. But if you dont have sufficient option to create multiple lines, then you can rorate your text by some degrees. And definitely it will save your column space.

Maybe most of you are familiar with text rotation. But Im sharing this tips for the beginners. You will get the text rotation option at the same place where the wrap text is located. Both of them are located in the Alignment Tab in Format Cells dialogue box.

Shortcut Method

If youre using Excel 2007/ 2010, you can easily rotate text from ribbon. Go to the Home tab and then move to the Alignment section. Look at the image below:

First you have to select the cell which you wanna rotate. Then go to the text rotation icon and choose your desired rotation style.

If youd like to explore more options then press Shift+Ctrl+F > Go to the Alignment tab > Look at the Orientation option at the right of the window > Here you can rotate your text by degree.

Text Alignment is really a useful tool in Excel. With this tool, you can reposition your text as your wish. In the ribbon, you can indent your text through text alignment section. You can position the text in the top, bottom, middle or left, right and center.

You can also merge cells from text alignment section. And you just need to practice by yourself. Type text or numbers randomly. And try the each option by yourself. Within few days you will be an expert in formatting text in Excel!

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

How to Show the File Location in MS Word and Excel

In MS Word and Excel you can show the location of your file (File Path). Usually the file path is placed at the footer of the document. And when it is placed at the document footer, it automatically appears at the each page of that document.

Why to Use the File Path (Location of the File):

Well this is really important for those people who want to keep the source of the document printed with it. If the file path is printed with the document then it is quite easier for us to trace the path next time when you search for it on the hard disk. It looks like the image below:

Thus it is easier for you to locate a saved document.

Why You Shouldnt Use the File Path:

Definitely it is revelation of information. If the file is a highly classified document and if the file path is used there, then others (who do not have access to that file) might be accessed that file through the path printed with the document! For example: it may be the question of the Varsity Admission Test or Job Test.

How to Show the File Path in a Document:

Well this is not a difficult task but really tough to find if you are not familiar with this option previously. And I will discuss about how to show the file path as well as how to hide. I am an user of Office 2010. But dont like to avoid the Office 2003 users. :)

Show/ Hide File Path in Office 2003:

Ms Word 2003: There are two ways available for you to do this task. You can follow any of the two methods. But at first you have to make sure that the file is previously saved. If you apply this to an unsaved document then it will show only Document 1 instead of showing the file path.

Method 1: Open a Document > Go to the View Menu > Choose Header and Footer > Go to the Footer > Insert Auto Text > Hit on Filename and Path. Done!

Take help of the image below:

Insert File Path from Header and Footer Option

Method 2: Somewhat difficult. Open a Document > View Menu > Header and Footer > Go to Footer > Insert Menu > Field > Choose Document Information from Categories > Select FileName from Field Names section > Select Add path to filename from the right most column > Hit Ok. Finish.

Look at the image below:

MS Excel 2003:

There is only one method and this is very easy. Open a previously created MS Excel Book. Simply go to the View Menu. Click on the Header and Footer option. Now select Edit Footer. Here you you can insert the file path.

Show/ Hide File Path in Office 2007 and 2010:

Here the procedure is somewhat different from Office 2003. Because Office 2007 and 10 has the ribbon instead of menu.

MS Word 2007 and 2010:

Dont know why the Auto Text doesnt show the file path. But you can insert the file path from field.

First you have to open a saved document. Then go to the footer (Just double click on the footer margin to activate footer). Now go to the Insert Menu > Quick Parts from the right side > Hit on the down arrow and choose Field > Select Document Information from Categories > Choose FileName > Mark Add Path to FileName. Done!

Insert File Path in Office 2010 from Field

MS Excel 2007 and 2010:

Open a saved excel book. Go to the Insert Menu > Hit on the Header & Footer from the right side of the ribbon > A new menu will appear named Design > Now move to the Document Footer > Select File Path > Done.

Look at the images below:

How to Remove the File Path?

You may notice that I have only talked about how to insert file path. But nothing was discussed about how to remove it! If you have already inserted the file path and cant remove it then simply go to the footer. Press Ctrl + A and hit the delete button to remove everything from footer. Or just select the file path from the footer and delete it. Thats it.

ONLINE DRIVERS MEDIA

Friday, April 8, 2016

Type Multiple Lines in a Cell in Excel

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

Wednesday, April 6, 2016

How to Print Headings on Every Page in Excel

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

Thursday, March 24, 2016

Creating daily activity logs through MS Excel

Sunday, March 20, 2016

How to Type More than 15 Digits in MS Excel

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

Tuesday, March 15, 2016

Divide Excel Worksheet into Pages!!

Advantages of Page Layout

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

Tuesday, February 23, 2016

How to Lock Rows Columns in Excel

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

Friday, February 12, 2016

Creating Column Bar Pie Chart in MS Excel With Sheet

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

Tuesday, January 26, 2016

MS Excel Training Basics Salary Sheet Result Sheet Electric Bill

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

Saturday, January 23, 2016

How to Rotate Text in Excel

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

Friday, January 22, 2016

How to Show the File Location in MS Word and Excel

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