excelhelp13, Author at Excel-Help

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

Subtotal All Function in Excel

Data, Menu Bar, Statistical

SUBTOTAL function is use to get a subtotal in a list or from database. SUBTOTAL function can either include or exclude values in manually hidden rows. By default, SUBTOTAL excludes values in rows hidden by a filter. The SUBTOTAL function automatically ignores other SUBTOTAL formulas that exist in references to prevent double-counting. Syntax:-=SUBTOTAL (function_num, ref1, [ref2], ...) Arguments function_num - A number that specifies which function to use in calculating subtotals within a list. See table below for full list.ref1 - A named range or reference to subtotal.ref2 - [optional] A named range or reference to subtotal. There are 11 functions available, each with two options, as seen in the table below. When function_num is between 1-11, SUBTOTAL includes cells that have...

Jun22022 by excelhelp13No Comments

VLOOKUP with SUBTOTAL

FORMULAS, Lookup & Reference

Please follow the step shown below with images. This can be used when you want to check or analyze the data with different criteria and luckily excel has two great function to make this possible VLOOKUP 2. SUBTOTAL. Let's begin. We have Stock details of LCD, Keyboard, Mouse, Printer etc. From which we need certain types of information separately. Which we will get using VLOOKUP with SUBTOTAL. Follow the steps below. Create a subtotal Functions table in your worksheet. To create a list of subtotal functions, select cell C20 or according to your choice and adjustment you can change cell until it reference correctly. Then Go to DATA tab >> Data Validation. Select list in the dialog box that opens. Select H5:H11 in the source. Click ok. 4. The list of...

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 opt...

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 protec...

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 ...

Mar282021 by excelhelp13No Comments

SPLIT CONTENTS IN DIFFERENT CELL IN EXCEL

Data, Menu Bar

STEPS : Select the cell or column that contains the text you want to split.Click on Data TabClick Text to Columns.In the Convert Text to Columns Wizard, select Delimitedclick Next. Select the Delimiters for your data. For example Comma, Space etc. You can see a preview of your data in the Data preview window. here we choose spaceClick Next. Choose the Column data format or use what Excel choose for you. Here we choose General.Select the Destination, which is where you want the split data to appear on your worksheet.Click finish

Mar282021 by excelhelp13No Comments

Hide or Remove Gridlines in Excel

Menu Bar, Page Layout

What is Gridlines ? The horizontal and vertical gray lines that appear between the cells on the worksheet are called Gridlines. If you don’t need gridlines in your work, you can Hide them by following the steps below. Method-1 Click on Page Layout tab Go to the Gridlines section in sheet options group. Uncheck the view box. Method-2 Click on view tabGo to the show group.Uncheck the Gridlines. Method-3 Click on File tabClick excel Options > Advanced > display options for this worksheetUncheck the show Gridlines

Mar242021 by excelhelp13No Comments

Remove Duplicates in excel

Data

Steps: 1. Select data or Click any single cell inside the data set. 2. Click on the Data tab 3. Click Remove Duplicates in the Data Tools group. 4. Remove duplicates dialog box appears. 5. Click check boxes as per your requirement and click OK. (in this example, remove rows with the Name. So, Check Name and click OK.) 6. Result- Excel removes all rows with the same Name except for the first instances found.