excelhelp Archives - Page 2 of 9

Jun122022 by excelhelp13No Comments

Get Month name from Date as Text in Excel.

To get the Month name from Date as text, you can use text function with a custom number format within excel.. Syntax:- (1) =text(date,"mmm") or (2) =text(date,"mmmm") NOTE: "mmm" – to get the first 3 letters of the name of the month."mmmm" – to get the full name of the month An Examples are shown in the image below.

Jun122022 by excelhelp13No Comments

Create diagonal line in cell

Home Menu, tips & Tricks

When you want to show to separate headings within a same cell (one for the row and one for the column), in that case you need to split a cell diagonally. Always remember that cell is a combination of row and column. For Example : Suppose you have data as per image shown below. Now suppose want to insert headings for both column and row in cell A3 for list of name and list of month. STEPS : Select cell A3.Right click on it and Click on format cells...In the format cell dialogue box, go on border tab.Click on diagonal line icon at the bottom right.Click ok. 6. See the result in below image. How to remove diagonal line in excel ? STEPS : Select the cell or cells from which you want to remove the diagonal line.Right click on selection and choose "F...

Jun92022 by excelhelp13No Comments

Hide the data of a specific cell in excel

tips & Tricks

Suppose you have a confidential data in your worksheet. you do not want to get it visible on the worksheet when other user using that sheet. If that the case then you can hide those confidential data by using custom number format. STEPS : Select data which you want to hide.Right click on it.Click on format cells... 4. In the format cell dialogue box, go on "Number" tab 5. Click "Custom" 6. Type ;;; (3 semicolon) in the type box. 7. Click ok. 8. See the Result in below Image. The Data of the Selected Cells are hidden. Unhide the data of a specific cell in excel STEPS : Select the cells whose data you want to Unhide.Right click on it.Click on format cells...In the format cell dialogue box, go on "Number" tab Click "Custom"Choose "General" fr...

Jun92022 by excelhelp13No Comments

Get the Day name from Date in Excel.

tips & Tricks

To get the Day name from Date, you can use text function with a custom number format. SYNTAX: (1) =text(date,"ddd") or (2) =text(date,"dddd") or (3) =text(date,"dd") Note : "ddd" – to get the first 3 letters of the name of the Day."dddd" – to get the full name of the Day"dd" - to get a Day number of the related month. An Examples are in the image below.

Jun92022 by excelhelp13No Comments

Insert multiple rows in one click

tips & Tricks

Usually Insert Row command Insert only one Row at One time. But you can Insert multiple Rows in one click in Excel. Follow the steps below. Usually insert row command is insert only one row at one time. But you can insert multiple rows in one click in Excel. Follow the steps below. STEPS: Select the same number of rows as you want to insert. For example, if you want to insert 4 blank rows, select 4 rows.Right click on it.Click on insert. 4. Blank rows will be inserted above the selected rows. Note- if there is data in the selected rows, it’s ok. Because that data will not get deleted.

Jun92022 by excelhelp13No Comments

Increase font size of Formula bar in excel.

tips & Tricks

Follow the steps below to increase font size of Formula bar. STEPS: Go to File tab.Click on the "Options". That is located at bottom left-side of panel . 3. The one Excel Options dialog box will get appear. 4. Click on General, the go to "when creating a new workbook", in that select "Font Size" and change the value according to your need. For example 30. 5. Click OK 6. Excel gives you a notification "please close and restart Microsoft Excel so that the font changes can take effect." 7. Click OK in it. 8. Close the worksheet and Reopen it. 9. See the result in the image below. The size of the formula bar should be increased. Note- Does not affect worksheet area and cell font size.

Jun92022 by excelhelp13No Comments

Data transpose Dynamically in Excel

tips & Tricks

Transpose function is use to exchange the data from Row to Column and Column to Row. To make your data get update automatically by using transpose function please follow the below mentioned steps. STEPS : Select destination range. (Where you want to transpose data)Type formula =transpose(source data range)Press Ctrl + shift + enter 4. See the result in below image. Data transposed within the selected range. 5. When we edit the source data, the transposed data will changed automatically. See the image below.

Jun22022 by excelhelp13No Comments

Move or Copy Sheet in Excel

Home Menu, Menu Bar

When you need to create a new spreadsheet based on an existing one or move a sheet from one excel file to another, you can move or copy a worksheet in the same workbook or another workbook. 1. Move or Copy a worksheet in the same workbook Open the worksheet you want to move or copy.Home > format > move or copy sheet… (you can also open this option using right click on sheet Tab) The Move or Copy dialog box will then open.Choose where you want to place the copy.Click on Create a copy box. (If you don't Click the create a copy box, Excel will only move the sheet.)Click OK. 2. Move or Copy a worksheet to another workbook Open the worksheet you want to move or copy.Home > format > move or copy sheet… (you can also open this opti...

Jun22022 by excelhelp13No Comments

Protect Worksheet in Excel

Home Menu, Menu Bar

When you share an Excel file with other users, you may want to protect a worksheet to help prevent it from being changed. Using this option, you can lock your Excel sheet with a password and you can make only certain parts of the sheet editable and users will not be able to modify data in any other region in the sheet. Preview in new tab How to Protect Sheet in Excel ? Steps:- Right click on sheet tab > Click Protect Sheet. Or Home> Format > Protect Sheet Check on protect worksheet and content of locked cells box.Enter a password.Check the actions you allow the users of your worksheet to perform.Note:- if you don't check any action, users can only view the Excel file.Click OK. Confirm the password and click OK.Your worksheet is protect...

Jun22022 by excelhelp13No Comments

Strikethrough Effect in Excel

Home Menu, Menu Bar

Strikethrough Effect is Useful for drawing a line in the center above the data / text in the cell to mark it as a completed or to display as depricated or non use as per the needs vary. How to Insert Strikethrough Effect in Excel ? For Example:- excelhelp.in Steps:- Select DataClick Home tab > format > Format cell… or right click on any cell > Format cell… The format cell dialog box will open.Click on font tab > font effectCheck on Strikethrough box.Click OK How to Remove Strikethrough Effect in Excel ? Select the data.Click Home tab > format > Format cell… OR right click on any cell > Format cell… The format cell dialog box will open.Click on font tab > font effectUnCheck on Strikethrough box.Click OK