Skip to main content

Update All Sheets at same time in a Excel Workbook


Say you have to urgently submit a file with many sheets.

For Example put a Header or write a same  data for all sheets.

I will show you an amazingly powerful tool called Group mode.

Right click on the January worksheet tab and choose Select All Sheets from the context menu.

Say that you have 12 worksheets that are mostly identical. You need to add totals to all 12 worksheets. To enter Group mode, right-click on any worksheet tab and choose Select All Sheets.

The name of the workbook in the title bar now indicates that you are in Group mode.

The title bar at the top of the Excel window shows the workbook name followed by the word Group enclosed in square brackets. [Group] is a very subtle indicator that the workbook is in group mode.

Anything you do to the January worksheet will now happen to all the sheets in the workbook.

Why is this dangerous? If you get distracted and forget that you are in Group mode, you might start entering January data and overwriting data on the 11 other worksheets!

When you are done adding totals, don’t forget to right-click a sheet tab and choose Ungroup Sheets.

Comments

Popular posts from this blog

Diploma In Data Analytics with Excel and Power BI

I will Be teaching the below mentioned course in association with Sanyukta Trust Sanyukta Trust is proud to introduce a special course designed to transform you into Data Analytics Expert! In this course, you will benefit by learning how to use MS Excel and Power Bi (Business Intelligence) to read, clean, transform, visualize and analyze the data.  Join our AIBTE (All India Board of Technical Education) Government recognised Online course of Diploma in Data Analytics with Microsoft Excel and Power Business Intelligence (Bi) Session: 16 sessions plus assignment submissions  Course Highlights: - Introduction to Data Analytics & MS Office Tools - In depth knowledge of MS Excel & Power Bi - Clean and Transform data with Power Query - Data Modelling with Dax/Power Pivot  - 3D maps  - Interactive Charts and Data Visualization with Slicers - Interactive Dashboards Days: Twice a Week - Saturday - 5pm to 6.30pm - Sunday - 11am to 12.30pm ...

20 Time Intelligence Dax Measures

20 Time Intelligence DAX measures in Power BI with examples: Year-to-Date Sales: css Copy code YTD Sales = TOTALYTD( [Total Sales] , Calendar [Date] ) Month-to-Date Sales: css Copy code MTD Sales = TOTALMTD( [Total Sales] , Calendar [Date] ) Quarter-to-Date Sales: css Copy code QTD Sales = TOTALQTD( [Total Sales] , Calendar [Date] ) Previous Year Sales: mathematica Copy code Previous Year Sales = CALCULATE ( [ Total Sales ] , SAMEPERIODLASTYEAR ( Calendar [ Date ] ) ) Year-over-Year Growth: css Copy code YoY Growth = DIVIDE( [Total Sales] - [Previous Year Sales] , [Previous Year Sales] ) Rolling 3-Month Average Sales: sql Copy code 3 M Rolling Avg Sales = AVERAGEX(DATESINPERIOD(Calendar[ Date ], MAX (Calendar[ Date ]), -3 , MONTH ), [Total Sales]) Cumulative Sales: scss Copy code Cumulative Sales = SUMX (FILTER(ALL(Calendar), Calendar [Date] <= MAX (Calendar[Date])), [Total Sales] ) Running Total Sales: scss Copy code Running Total Sales = SUMX (FILTER(ALL(Calendar), Calen...

Goal Seek feature in Excel

Have you ever found yourself plugging in successively higher and lower values into an input cell, hoping to arrive at a certain answer? A tool that is built in to Excel does exactly this set of steps. Select the cell with the Payment formula. On the Data tab, in the Data Tools group, look for the What-If Analysis dropdown and choose Goal Seek…. The figure below shows how you can try to set the payment in B5 to $425 by changing cell B1 Goal Seek finds the correct answer within a second. Note that the formula in B5 stays intact. The only thing that changes is the input value typed in B1. Also, with Goal Seek, you are free to experiment with changing other input cells. You can still get the $425 loan payment and the $25,995 car if your banker will offer you a 71.3379-month loan!